执行LINQ查询Tenant表时'PartnerId'列不存在异常原因排查

执行简单LINQ查询DbContext.Tenants.ToList()时，抛出Invalid column name 'PartnerId'异常，但Tenant数据库表和对应的实体类中均未定义该字段。

查询代码 #

public IList<Tenant> GetAsync()
{
    return DbContext.Tenants
        .ToList();
}

Tenant表结构 #

CREATE TABLE [dbo].[Tenants](
    [Id] [uniqueidentifier] NOT NULL,
    [FullName] [nvarchar](max) NULL,
    [Name] [nvarchar](max) NULL,
    [IsGlobal] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NULL,
    [CreatedBy] [nvarchar](max) NULL,
    [ModifiedOn] [datetime2](7) NULL,
    [ModifiedBy] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,
    [Enable] [bit] NOT NULL,
    [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_Tenants] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

基础实体类 #

public class BasicEntity<TKey>
{
    public TKey Id { get; set; }
    public DateTime? CreatedOn { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? ModifiedOn { get; set; }
    public string ModifiedBy { get; set; }
    public bool IsDeleted { get; set; }
    public bool Enable { get; set; }
    public string Description { get; set; }
}

Tenant实体类 #

public class Tenant: BasicEntity<Guid>
{
    public string FullName { get; set; }
    public string Name { get; set; }
    public bool IsGlobal { get; set; }
    public List<Environment> Environments { get; set; }
    public List<TenantCompanies> Companies { get; set; }
    public List<TenantMeta> TenantMetas { get; set; }
}

DbContext接口 #

public interface IAdminTenantManagementSystemDbContext
{
    DbSet<User> Users { get; set; }
    DbSet<Tenant> Tenants { get; set; }
    DbSet<Entities.Environment> Environments { get; set; }
    DbSet<Partner> Partners { get; set; }
    DbSet<PartnerTenants> PartnerTenants { get; set; }
    DbSet<TenantCompanies> TenantCompanies { get; set; }
    DbSet<TenantMeta> TenantMetas { get; set; }
}

Partner实体类 #

public class Partner : BasicEntity<Guid>
{
    public string Name { get; set; }
    public List<PartnerTenants> PartnerTenants { get; set; }
    public List<Tenant> Tenants { get; set; }
    public List<Environment> Environments { get; set; }
    public List<TenantCompanies> Companies { get; set; }
}

PartnerTenants实体类 #

public class PartnerTenants
{
    public Guid Id { get; set; }
    public Guid TenantId { get; set; }
    public Guid PartnerId { get; set; }
    public bool Enable { get; set; }
    public Partner Partner { get; set; }
    public Tenant Tenant { get; set; }
}

异常信息 #

Microsoft.Data.SqlClient.SqlException (0x80131904): 列名 'PartnerId' 无效。
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Skoruba.IdentityServer4.Admin.EntityFramework.Repositories.TenantRepository`1.GetAsync() in D:\data\code\tms\Get.Caa\IdentityServer.Admin\src\Skoruba.IdentityServer4.Admin.EntityFramework\Repositories\TenantRepository.cs:line 32
   at Skoruba.IdentityServer4.Admin.BusinessLogic.Services.TenantService.GetTenantsAsync() in D:\data\code\tms\Get.Caa\IdentityServer.Admin\src\Skoruba.IdentityServer4.Admin.BusinessLogic\Services\TenantService.cs:line 71
   at Skoruba.IdentityServer4.Admin.Api.Controllers.TenantController.Get() in D:\data\code\tms\Get.Caa\IdentityServer.Admin\src\Skoruba.IdentityServer4.Admin.Api\Controllers\TenantController.cs:line 42
   at lambda_method42(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- 之前位置的堆栈跟踪结束 ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeNextResourceFilter()
--- 之前位置的堆栈跟踪结束 ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- 之前位置的堆栈跟踪结束 ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.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)

原因分析 #

EF Core的默认约定映射逻辑导致问题：

• Partner实体中定义了List<Tenant> Tenants导航集合，EF Core会默认将其识别为一对多关联关系，自动尝试在Tenant表中寻找PartnerId作为外键字段，但实际数据库和实体都没有这个字段。
• 虽然已存在PartnerTenants中间表用于维护多对多关系，但EF Core未自动识别出这是多对多的中间表，反而采用了一对多的默认映射规则，最终生成错误的SQL查询。

解决方案 #

方案1：显式配置多对多关系（推荐） #

在DbContext的OnModelCreating方法中，明确指定Tenant和Partner通过PartnerTenants中间表建立多对多关系：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // 配置Tenant与Partner的多对多关联
    modelBuilder.Entity<Tenant>()
        .HasMany(t => t.Partners) // 需要在Tenant实体中添加ICollection<Partner> Partners属性
        .WithMany(p => p.Tenants)
        .UsingEntity<PartnerTenants>(
            join => join.HasOne(pt => pt.Partner).WithMany().HasForeignKey(pt => pt.PartnerId),
            join => join.HasOne(pt => pt.Tenant).WithMany().HasForeignKey(pt => pt.TenantId),
            join =>
            {
                join.HasKey(pt => pt.Id);
                // 可添加其他中间表配置，比如索引
            });
}

同时在Tenant实体中补充对应的导航属性：

public class Tenant: BasicEntity<Guid>
{
    // 原有属性...
    public string FullName { get; set; }
    public string Name { get; set; }
    public bool IsGlobal { get; set; }
    public List<Environment> Environments { get; set; }
    public List<TenantCompanies> Companies { get; set; }
    public List<TenantMeta> TenantMetas { get; set; }
    // 添加多对多导航属性
    public ICollection<Partner> Partners { get; set; }
}

方案2：忽略错误的导航属性映射 #

如果不需要Tenant和Partner之间直接的导航关联，可在DbContext中忽略Partner实体的Tenants集合：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // 忽略Partner中的Tenants导航属性，避免EF自动生成外键
    modelBuilder.Entity<Partner>()
        .Ignore(p => p.Tenants);
}

方案3：排查全局配置 #

• 检查是否存在全局查询过滤器错误引用了PartnerId字段
• 确认Fluent API中是否有错误配置给Tenant实体添加了PartnerId外键

内容的提问来源于stack exchange，提问作者Syed Rafey Husain