EF Core 6.0.13生成TSQL引用不存在的TrackId列引发SqlException

AI 小北

2026-7-26

EF Core 6.0.13 引用已删除的TrackId列导致SQL异常

问题描述

我此前曾在模型中添加过TrackId列用于关联Track表，后移除该列且正常开发测试2个月无异常。近期增删其他列后，EF Core 6.0.13生成的TSQL语句中出现对不存在的TrackId列的引用，引发SqlException: Invalid column name 'TrackId'错误。

环境与模型关系

数据库为Azure SQL Database。Track包含TrackCourses仅作为创建模板；创建ScheduledTrack时复用Track信息生成实例，并为每个TrackCourse创建ScheduledClass实例。Track与ScheduledTrack为独立对象，创建完成后无关联。TrackCourses关联Course，但Course无反向关联。

相关代码

ScheduledClass实体

public class ScheduledClass : AuditableEntity  // (提供ID及创建/修改时间列)
{
    public ScheduledClass()
    {
        Scheduling = new SchedulingInformation();
        Cancellation = new CancellationInformation();
        CoursewareDetails = new CoursewareDetails();
        Enrollments = new HashSet<Enrollment>();
        Sessions = new HashSet<ClassSession>();
    }

    public int CourseId { get; set; }
    public int? ScheduledTrackId { get; set; }
    public SchedulingInformation Scheduling { get; set; }
    public string TimeZone { get; set; }
    public int ClassroomId { get; set; }
    public StudentCapacity StudentCapacity { get; set; }
    public CancellationInformation Cancellation { get; set; }
    public bool ShowOnWeb { get; set; }
    public ClassStatusEnum ClassStatus { get; set; }
    public decimal Retail { get; set; }
    public decimal InternalCost { get; set; }
    public string Version { get; set; }
    public int ScheduledTypeId { get; set; }
    public DeliveryLength DeliveryLength { get; set; }
    public bool IsPrivate { get; set; }
    public string Client { get; set; }
    public int? SeatModalityLookup { get; set; }
    public int? LocationLookup { get; set; }
    public int CoursewareDetailsId { get; set; }

    public virtual Classroom Classroom { get; set; }
    public virtual Course Course { get; set; }
    public virtual CoursewareDetails CoursewareDetails { get; set; }
    public virtual ScheduledTrack ScheduledTrack { get; set; }
    public virtual ICollection<ClassSession> Sessions { get; set; }
    public virtual ICollection<Enrollment> Enrollments { get; set; }
}

ScheduledTrack实体

public class ScheduledTrack : Entity<int>
{
    public ScheduledTrack()
    {
        Classes = new HashSet<ScheduledClass>();
    }

    public DateTime StartDate { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public string Abbrev { get; set; }
    public int? TimePeriodLookup { get; set; }

    public virtual ICollection<ScheduledClass> Classes { get; set; }       
}

Track实体（曾关联ScheduledTrack，现已移除）

public class Track : AuditableEntity
{
    public Track()
    {
        Courses = new HashSet<TrackCourse>();
    }

    public string Name { get; set; }
    public bool IsActive { get; set; }
    public string Version { get; set; }
    public string Abbrev { get; set; }

    public virtual ICollection<TrackCourse> Courses { get; set; }

}

DbContext配置

public partial class CareContext : DbContext
{
    public readonly IHttpContextAccessor _Http;

    public CareContext() { }
    public CareContext(IHttpContextAccessor ctx, DbContextOptions<CareContext> opt) : base(opt)
    {
        _Http = ctx;
    }

    public virtual DbSet<ScheduledClass> ScheduledClasses { get; set; }
    public virtual DbSet<ScheduledTrack> ScheduledTracks { get; set; }
    public virtual DbSet<Track> Tracks { get; set; }

    #endregion
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new ScheduledClassConfigurationProfile());
        modelBuilder.ApplyConfiguration(new ScheduledTrackConfigurationProfile());

        modelBuilder.Entity<Course>()
           .Navigation(d => d.CoursewareDetails)
           .AutoInclude();
        modelBuilder.Entity<ScheduledClass>()
           .Navigation(d => d.CoursewareDetails)
           .AutoInclude();
        modelBuilder.Entity<ScheduledClass>()
           .Navigation(d => d.Classroom)
           .AutoInclude();
        modelBuilder.Entity<Enrollment>()
            .Navigation(d => d.Student)
            .AutoInclude();
        base.OnModelCreating(modelBuilder);
    }       
}

ScheduledClass的Fluent API配置

internal class ScheduledClassConfigurationProfile : IEntityTypeConfiguration<ScheduledClass>
{
    public void Configure(EntityTypeBuilder<ScheduledClass> builder)
    {
        builder
            .ToTable("Classes");

        builder
            .HasKey(x => x.Id);

        builder
            .Property(x => x.Id)
            .IsRequired()
            .HasColumnName("ClassId");

        builder
            .Property(x => x.ClassStatus)
            .HasColumnName("ClassStatusId");

        builder
            .Property(e => e.Client).HasMaxLength(50);

        builder
            .Property(e => e.CreatedBy)
            .IsRequired()
            .HasMaxLength(25);

        builder
            .Property(e => e.InternalCost)
            .HasColumnType("smallmoney");

        builder
            .Property(e => e.ModifiedBy)
            .HasMaxLength(25);

        builder
            .Property(e => e.TimeZone)
            .HasMaxLength(10);

        builder
            .Property(e => e.Version)
            .HasMaxLength(10);

        builder
            .OwnsOne(x => x.Scheduling);

        builder
            .OwnsOne(x => x.DeliveryLength);

        builder
            .OwnsOne(x => x.StudentCapacity);

        builder
            .OwnsOne(x => x.Cancellation);

        builder
            .HasOne(x => x.ScheduledTrack);

        builder
            .HasMany(x => x.Enrollments);

        builder
           .HasOne(x => x.CoursewareDetails)
           .WithOne(x => x.Class)
           .HasForeignKey<ScheduledClass>(x => x.CoursewareDetailsId);

        builder.HasOne(d => d.ScheduledTrack)
            .WithMany(p => p.Classes)
            .HasForeignKey(d => d.ScheduledTrackId)
            .HasConstraintName("FK_Classes_ScheduledTracks");

    }
}

ScheduledTrack的Fluent API配置

internal class ScheduledTrackConfigurationProfile : IEntityTypeConfiguration<ScheduledTrack>
{
    public void Configure(EntityTypeBuilder<ScheduledTrack> builder)
    {
        builder
            .HasKey(x => x.Id);

        builder
            .Property(x => x.Id)
            .HasColumnName("ScheduledTrackId");

        builder.Property(e => e.Abbrev)
            .HasMaxLength(25);

        builder.Property(e => e.Name)
            .IsRequired()
            .HasMaxLength(200);

        builder.Property(e => e.StartDate)
            .HasColumnType("datetime");

        builder.HasMany(x => x.Classes)
            .WithOne(x => x.ScheduledTrack)
            .HasForeignKey(x => x.Id);
    }
}

触发错误的查询代码

public ScheduledTrack LoadMinimal(int id)
{
  var results = _DB.ScheduledTracks
      .First(x => x.Id == id);
  return results;
}

错误堆栈

SqlException: Invalid column name 'TrackId'.
Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, bool isAsync, int timeout, out Task task, bool asyncWrite, bool inRetry, SqlDataReader ds, bool describeParameterEncryptionRequest)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, TaskCompletionSource<object> completion, int timeout, out Task task, out bool usedCache, bool asyncWrite, bool inRetry, string method)
Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+Enumerator.InitializeReader(Enumerator enumerator)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute<TState, TResult>(TState state, Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult<TResult>> verifySucceeded)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+Enumerator.MoveNext()
System.Linq.Enumerable.TryGetSingle<TSource>(IEnumerable<TSource> source, out bool found)
lambda_method741(Closure , QueryContext )
System.Linq.Queryable.First<TSource>(IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
UnifiedCare.Repositories.ScheduledTracks.LoadMinimal(int id)
UnifiedCare.Controllers.ViewComponents.ScheduledTrackNameViewComponent.Invoke(int scheduledTrackName) in ScheduledTrackName.cs

已尝试的排查措施

完全重建数据库（从生产库同步数据并执行转换脚本）；
使用dotnet ef脚手架生成数据库模型，对比后发现脚手架代码未在ScheduledTrack/ScheduledClass中包含TrackId；
查询sysColumns确认仅Tracks和TrackCourses表存在TrackId列，与ScheduledTrack/ScheduledClass无关联；
排除了数据库复制、EDMX文件、EF Migrations的影响，当前采用数据库优先的代码开发模式。

疑问

EF是否存在元数据缓存导致该问题？或模型配置中存在未发现的错误？

问题诊断与解决方案

核心配置错误

ScheduledTrack配置中的外键错误
在ScheduledTrackConfigurationProfile中，关联Classes时错误地将外键指定为x => x.Id（ScheduledClass自身的主键），正确的外键应该是ScheduledTrackId：

// 错误代码
builder.HasMany(x => x.Classes)
    .WithOne(x => x.ScheduledTrack)
    .HasForeignKey(x => x.Id);

// 修正后代码
builder.HasMany(x => x.Classes)
    .WithOne(x => x.ScheduledTrack)
    .HasForeignKey(x => x.ScheduledTrackId);

这个错误会让EF无法正确识别关联关系，可能触发旧的关联元数据残留，导致错误引用TrackId。

ScheduledClass配置中的重复关联定义
在ScheduledClassConfigurationProfile中，重复定义了builder.HasOne(x => x.ScheduledTrack);，这行代码是冗余的，后面已经有完整的关联配置（包含外键和约束名），重复配置会干扰EF的模型构建逻辑。需要删除这行冗余代码。

缓存清理步骤

清理项目的bin和obj目录，重新编译解决方案；
重启应用服务/应用程序池，确保没有残留的内存缓存；
对于Azure部署，确认部署包是最新编译版本，没有旧DLL文件残留。

模型验证

可以添加代码输出EF生成的模型元数据，确认是否还有TrackId的引用：

// 在启动或测试代码中添加
using (var context = new CareContext(...)) // 传入正确的上下文参数
{
    var model = context.Model;
    foreach (var entityType in model.GetEntityTypes())
    {
        Console.WriteLine($"实体: {entityType.Name}");
        foreach (var property in entityType.GetProperties())
        {
            Console.WriteLine($"  属性: {property.Name} (列名: {property.GetColumnName()})");
        }
        foreach (var foreignKey in entityType.GetForeignKeys())
        {
            var fkProps = string.Join(", ", foreignKey.Properties.Select(p => p.Name));
            var pkProps = string.Join(", ", foreignKey.PrincipalKey.Properties.Select(p => p.Name));
            Console.WriteLine($"  外键: {fkProps} -> {foreignKey.PrincipalEntityType.Name}.{pkProps}");
        }
    }
}

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

EF Core 6.0.13生成TSQL引用不存在的TrackId列引发SqlException

问题描述 #

环境与模型关系 #

相关代码 #

ScheduledClass实体 #

ScheduledTrack实体 #

Track实体（曾关联ScheduledTrack，现已移除） #

DbContext配置 #

ScheduledClass的Fluent API配置 #

ScheduledTrack的Fluent API配置 #

触发错误的查询代码 #

错误堆栈 #

已尝试的排查措施 #

疑问 #

问题诊断与解决方案 #