Entity Framework Core错误使用实体属性名而非数据库列名访问数据库

多对多关系配置 #

在MainContext中配置的多对多关系如下：

modelBuilder.Entity<Project>()
    .HasMany(project => project.Messages)
    .WithMany(message => message.Projects)
    .UsingEntity<ProjectmessageProject>(
        typeBuilder => typeBuilder.HasOne(x => x.Message).WithMany().HasForeignKey(x => x.ProjectmessageId),
        typeBuilder => typeBuilder.HasOne(x => x.Project).WithMany().HasForeignKey(x => x.ProjectIdTest)
    );

测试时将ProjectId重命名为ProjectIdTest，此操作并非问题根源，可随时改回。

生成的SQL问题 #

访问ProjectmessageProject时，EF Core生成的SQL查询如下：

SELECT `p0`.`project_id`, `p0`.`projectmessage_id`, `p0`.`folder_id`, `p0`.`ProjectId`, `p0`.`ProjectMessageId`, `p`.`id`
      FROM `projects` AS `p`
      INNER JOIN `projectmessage_project` AS `p0` ON `p`.`id` = `p0`.`ProjectId`
      WHERE `p`.`id` = @__id_0
      ORDER BY `p`.`id`

核心问题：SQL中错误引用了ProjectId和ProjectMessageId，这两个是不存在的实体属性，数据库中对应的列是project_id和projectmessage_id，且已被正确选中，但EF仍额外添加了不存在的列引用，导致数据库报错。

实体定义 #

中间表实体定义如下：

[Table("projectmessage_project")]
public record ProjectmessageProject : IProjectmessageProject
{
    public Project? Project { get; init; }

    public ProjectMessage? Message { get; init; }

    [NotMapped]
    public UInt64 Id { get; init; }

    [Column("projectmessage_id")]
    public UInt64? ProjectmessageId { get; init; }

    [Column("project_id")]
    public UInt64? ProjectIdTest { get; init; }

    [Column("folder_id")]
    public UInt64? FolderId { get; init; }

}

注意Id已标记[NotMapped]，但EF仍尝试访问该列对应的数据库字段。

抛出的异常 #

控制台抛出的关键异常信息如下：

MySqlConnector.MySqlException (0x80004005): Unknown column 'p0.ProjectId' in 'field list'

完整堆栈跟踪：

Connection id "0HMMVFK574S69", Request id "0HMMVFK574S69:00000005": An unhandled exception was thrown by the application.

MySqlConnector.MySqlException (0x80004005): Unknown column 'p0.ProjectId' in 'field list'

at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 127
at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
         at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
         at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.<PopulateSplitCollection>g__InitializeReader|67_1[TCollection,TElement,TRelatedEntity](RelationalQueryContext queryContext, RelationalCommandCache relationalCommandCache, Boolean detailedErrorsEnabled)
         at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateSplitCollection[TCollection,TElement,TRelatedEntity](Int32 collectionId, RelationalQueryContext queryContext, IExecutionStrategy executionStrategy, Boolean detailedErrorsEnabled, SplitQueryResultCoordinator resultCoordinator, RelationalCommandCache relationalCommandCache, Func`3 childIdentifier, IReadOnlyList`1 identifierValueComparers, Func`5 innerShaper, Action`3 relatedDataLoaders)
         at lambda_method138(Closure , QueryContext , IExecutionStrategy , SplitQueryResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
         at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Watch.BrowserRefresh.BrowserRefreshMiddleware.InvokeAsync(HttpContext context)
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

已尝试的方案 #

• 调整多对多关系配置
• 为Project和Message导航属性添加外键标记
• 在上下文配置复合主键：

  modelBuilder.Entity<ProjectmessageProject>().HasKey(x => new
          {
              x.ProjectIdTest,
              x.ProjectmessageId,
              x.FolderId
          });
  

• 将ProjectIdTest改回ProjectId后，EF Core转而尝试访问数据库中的ProjectId1列，问题依旧。

解决建议 #

1. 显式完整配置中间表映射 #

避免EF Core依赖默认约定推断，明确配置中间表的所有属性与数据库列的映射关系，同时在多对多配置中指定中间表的细节：

// 先单独配置中间表实体
modelBuilder.Entity<ProjectmessageProject>()
    .ToTable("projectmessage_project")
    .HasKey(x => new { x.ProjectIdTest, x.ProjectmessageId, x.FolderId });

modelBuilder.Entity<ProjectmessageProject>()
    .Property(x => x.ProjectmessageId)
    .HasColumnName("projectmessage_id")
    .IsRequired(false); // 根据实际数据库列是否可空调整

modelBuilder.Entity<ProjectmessageProject>()
    .Property(x => x.ProjectIdTest)
    .HasColumnName("project_id")
    .IsRequired(false);

modelBuilder.Entity<ProjectmessageProject>()
    .Property(x => x.FolderId)
    .HasColumnName("folder_id")
    .IsRequired(false);

// 配置导航属性与外键
modelBuilder.Entity<ProjectmessageProject>()
    .HasOne(pm => pm.Project)
    .WithMany()
    .HasForeignKey(pm => pm.ProjectIdTest)
    .OnDelete(DeleteBehavior.Restrict); // 根据业务需求设置删除行为

modelBuilder.Entity<ProjectmessageProject>()
    .HasOne(pm => pm.Message)
    .WithMany()
    .HasForeignKey(pm => pm.ProjectmessageId)
    .OnDelete(DeleteBehavior.Restrict);

// 重新配置多对多关系
modelBuilder.Entity<Project>()
    .HasMany(p => p.Messages)
    .WithMany(m => m.Projects)
    .UsingEntity<ProjectmessageProject>(
        j => j.HasOne<ProjectMessage>(pm => pm.Message)
            .WithMany()
            .HasForeignKey(pm => pm.ProjectmessageId),
        j => j.HasOne<Project>(pm => pm.Project)
            .WithMany()
            .HasForeignKey(pm => pm.ProjectIdTest)
    );

2. 检查接口IProjectmessageProject的定义 #

确保IProjectmessageProject接口中没有定义ProjectId或ProjectMessageId属性，否则EF Core会将这些接口属性识别为实体属性，尝试映射到数据库列。

3. 清理EF模型缓存 #

EF Core可能缓存了旧的模型配置，尝试：

• 删除项目的bin和obj目录
• 清理NuGet缓存
• 重新生成解决方案并重启应用

4. 尝试将record改为class类型 #

虽然EF Core支持record类型，但record的不可变性可能在某些场景下导致映射逻辑异常。将ProjectmessageProject改为class类型测试：

[Table("projectmessage_project")]
public class ProjectmessageProject : IProjectmessageProject
{
    public Project? Project { get; set; }

    public ProjectMessage? Message { get; set; }

    [NotMapped]
    public UInt64 Id { get; set; }

    [Column("projectmessage_id")]
    public UInt64? ProjectmessageId { get; set; }

    [Column("project_id")]
    public UInt64? ProjectIdTest { get; set; }

    [Column("folder_id")]
    public UInt64? FolderId { get; set; }
}

5. 禁用EF Core的默认外键约定 #

如果默认约定干扰了映射，可以禁用特定约定：

modelBuilder.Conventions.Remove<ForeignKeyDiscoveryConvention>();

注意此操作会影响整个模型的外键推断，需确保所有外键都显式配置。

内容的提问来源于stack exchange，提问作者SinusQuell