EF Core 6.0.7数据库优先模式下SQL丢失表前缀致列不存在错误

问题背景 #

使用Microsoft.EntityFrameworkCore.SqlServer 6.0.7版本，采用数据库优先模式对接现有数据库，通过脚手架生成实体类与上下文后，多数实体可正常查询，但TimeOffRequest实体查询报错。

错误信息 #

Microsoft.Data.SqlClient.SqlException : Invalid column name 'fldTimeOffRequestID'.

Invalid column name 'fldCorpCode'.

Invalid column name 'fldEmpID'.

Invalid column name 'fldEmployeeComment'.

Invalid column name 'fldEntryDate'.

Invalid column name 'fldJobLoc'.

Invalid column name 'fldManagerComment'.

Invalid column name 'fldManagersLogonID'.

Invalid column name 'fldMgrModifiedBy'.

Invalid column name 'fldMgrModifiedDate'.

Invalid column name 'fldPJobCode'.

Invalid column name 'fldPeoplesoftID'.

Invalid column name 'fldRequestDateEnd'.

Invalid column name 'fldRequestDateStart'.

Invalid column name 'fldRestaurantNumber'.

Invalid column name 'fldSAM'.

Invalid column name 'fldSeriesID'.

Invalid column name 'fldStatus'.

Invalid column name 'fldTempScheduleChange'.

Invalid column name 'fldUserUpdateDate'.

Invalid column name 'fldfName'.

Invalid column name 'fldlName'.

Invalid column name 'fldEntryDate'.

相关代码 #

实体类TimeOffRequest #

// tbl_TimeOffRequests        
public partial class TimeOffRequest
{
        public int TimeOffRequestID { get; set; } // fldTimeOffRequestID (Primary key)
        public string CorpCode { get; set; } // fldCorpCode (length: 10)
        public int RestaurantNumber { get; set; } // fldRestaurantNumber
        public int EmpID { get; set; } // fldEmpID
        public string SAM { get; set; } // fldSAM (length: 25)
        public System.DateTime RequestDateStart { get; set; } // fldRequestDateStart
        public System.DateTime RequestDateEnd { get; set; } // fldRequestDateEnd
        public byte Status { get; set; } // fldStatus
        public System.DateTime EntryDate { get; set; } // fldEntryDate
        public System.DateTime UserUpdateDate { get; set; } // fldUserUpdateDate
        public string ManagerComment { get; set; } // fldManagerComment (length: 255)
        public string EmployeeComment { get; set; } // fldEmployeeComment (length: 255)
        public string ManagersLogonID { get; set; } // fldManagersLogonID (length: 30)
        public System.DateTime? MgrModifiedDate { get; set; } // fldMgrModifiedDate
        public string MgrModifiedBy { get; set; } // fldMgrModifiedBy (length: 55)
        public string fName { get; set; } // fldfName (length: 30)
        public string lName { get; set; } // fldlName (length: 25)
        public string PJobCode { get; set; } // fldPJobCode (length: 4)
        public byte? JobLoc { get; set; } // fldJobLoc
        public int? PeoplesoftID { get; set; } // fldPeoplesoftID
        public System.Guid? SeriesID { get; set; } // fldSeriesID
        public bool TempScheduleChange { get; set; } = false; // fldTempScheduleChange
}

上下文配置LmsContext #

public partial class LmsContext : DbContext
{
        public LmsContext(DbContextOptions<LmsContext> options)
            : base(options)
        {
        }

        public virtual DbSet<TimeOffRequest> TimeOffRequests { get; set; } = null!;        

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {    
            modelBuilder.Entity<TimeOffRequest>(entity =>
            {
                entity.HasKey(e => e.TimeOffRequestID);

                entity.ToTable("tbl_TimeOffRequests");

                entity.HasIndex(e => e.RestaurantNumber, "DRI_IDX_TimeOffRequests_3")
                    .HasFillFactor(80);

                entity.HasIndex(e => new { e.RestaurantNumber, e.CorpCode, e.RequestDateStart, e.TimeOffRequestID, e.Status, e.PeoplesoftID }, "DRI_IDX_TimeOffRequests_Multi")
                    .HasFillFactor(80);

                entity.HasIndex(e => new { e.RestaurantNumber, e.RequestDateStart, e.RequestDateEnd, e.PeoplesoftID, e.TimeOffRequestID, e.Status }, "DRI_IDX_TimeOffRequests_Multi2");

                entity.HasIndex(e => e.PeoplesoftID, "IDX_DRI_tbl_TimeOffRequests_PeoplesoftID")
                    .HasFillFactor(80);

                entity.HasIndex(e => new { e.Status, e.RequestDateStart, e.JobLoc, e.RestaurantNumber }, "IX_tbl_TimeOffRequests_1")
                    .HasFillFactor(90);

                entity.HasIndex(e => new { e.RestaurantNumber, e.EmpID, e.Status }, "IX_tbl_TimeOffRequests_2")
                    .HasFillFactor(90);

                entity.HasIndex(e => new { e.RestaurantNumber, e.RequestDateStart, e.JobLoc }, "IX_tbl_TimeOffRequests_3")
                    .HasFillFactor(90);

                entity.HasIndex(e => new { e.CorpCode, e.RestaurantNumber, e.RequestDateStart }, "IX_tbl_TimeOffRequests_4")
                    .HasFillFactor(90);

                entity.HasIndex(e => new { e.Status, e.RequestDateStart, e.RestaurantNumber, e.JobLoc }, "IX_tbl_TimeOffRequests_5")
                    .HasFillFactor(90);

                entity.HasIndex(e => new { e.CorpCode, e.PJobCode }, "IX_tbl_TimeOffRequests_6")
                    .HasFillFactor(80);

                entity.Property(e => e.TimeOffRequestID).HasColumnName("fldTimeOffRequestID");

                entity.Property(e => e.CorpCode)
                    .HasMaxLength(10)
                    .IsUnicode(false)
                    .HasColumnName("fldCorpCode");

                entity.Property(e => e.EmpID).HasColumnName("fldEmpID");

                entity.Property(e => e.EmployeeComment)
                    .HasMaxLength(255)
                    .IsUnicode(false)
                    .HasColumnName("fldEmployeeComment");

                entity.Property(e => e.EntryDate)
                    .HasColumnType("smalldatetime")
                    .HasColumnName("fldEntryDate");

                entity.Property(e => e.JobLoc).HasColumnName("fldJobLoc");

                entity.Property(e => e.ManagerComment)
                    .HasMaxLength(255)
                    .IsUnicode(false)
                    .HasColumnName("fldManagerComment");

                entity.Property(e => e.ManagersLogonID)
                    .HasMaxLength(30)
                    .IsUnicode(false)
                    .HasColumnName("fldManagersLogonID");

                entity.Property(e => e.MgrModifiedBy)
                    .HasMaxLength(55)
                    .IsUnicode(false)
                    .HasColumnName("fldMgrModifiedBy");

                entity.Property(e => e.MgrModifiedDate)
                    .HasColumnType("smalldatetime")
                    .HasColumnName("fldMgrModifiedDate");

                entity.Property(e => e.PeoplesoftID).HasColumnName("fldPeoplesoftID");

                entity.Property(e => e.PJobCode)
                    .HasMaxLength(4)
                    .IsUnicode(false)
                    .HasColumnName("fldPJobCode");

                entity.Property(e => e.RequestDateEnd)
                    .HasColumnType("smalldatetime")
                    .HasColumnName("fldRequestDateEnd");

                entity.Property(e => e.RequestDateStart)
                    .HasColumnType("smalldatetime")
                    .HasColumnName("fldRequestDateStart");

                entity.Property(e => e.RestaurantNumber).HasColumnName("fldRestaurantNumber");

                entity.Property(e => e.SAM)
                    .HasMaxLength(25)
                    .IsUnicode(false)
                    .HasColumnName("fldSAM");

                entity.Property(e => e.SeriesID).HasColumnName("fldSeriesID");

                entity.Property(e => e.Status).HasColumnName("fldStatus");

                entity.Property(e => e.TempScheduleChange).HasColumnName("fldTempScheduleChange");

                entity.Property(e => e.UserUpdateDate)
                    .HasColumnType("smalldatetime")
                    .HasColumnName("fldUserUpdateDate");

                entity.Property(e => e.fName)
                    .HasMaxLength(30)
                    .IsUnicode(false)
                    .HasColumnName("fldfName");

                entity.Property(e => e.lName)
                    .HasMaxLength(25)
                    .IsUnicode(false)
                    .HasColumnName("fldlName");
            });
            
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

数据库表DDL #

CREATE TABLE [dbo].[tbl_TimeOffRequests](
    [fldTimeOffRequestID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [fldCorpCode] [varchar](10) NOT NULL,
    [fldRestaurantNumber] [int] NOT NULL,
    [fldEmpID] [int] NOT NULL,
    [fldSAM] [varchar](25) NULL,
    [fldRequestDateStart] [smalldatetime] NOT NULL,
    [fldRequestDateEnd] [smalldatetime] NOT NULL,
    [fldStatus] [tinyint] NOT NULL,
    [fldEntryDate] [smalldatetime] NOT NULL,
    [fldUserUpdateDate] [smalldatetime] NOT NULL,
    [fldManagerComment] [varchar](255) NULL,
    [fldEmployeeComment] [varchar](255) NULL,
    [fldManagersLogonID] [varchar](30) NULL,
    [fldMgrModifiedDate] [smalldatetime] NULL,
    [fldMgrModifiedBy] [varchar](55) NULL,
    [fldfName] [varchar](30) NULL,
    [fldlName] [varchar](25) NULL,
    [fldPJobCode] [varchar](4) NULL,
    [fldJobLoc] [tinyint] NULL,
    [fldPeoplesoftID] [int] NULL,
    [fldSeriesID] [uniqueidentifier] NULL,
    [fldTempScheduleChange] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_TimeOffRequests] PRIMARY KEY CLUSTERED 
(
    [fldTimeOffRequestID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_TimeOffRequests] ADD  CONSTRAINT [DF_tbl_TimeOffRequests_fldTempScheduleChange]  DEFAULT ((0)) FOR [fldTempScheduleChange]
GO

EF Core生成的SQL #

SELECT TOP(1) [t].[fldTimeOffRequestID], [t].[fldCorpCode], [t].[fldEmpID], [t].[fldEmployeeComment], [t].[fldEntryDate], [t].[fldJobLoc], [t].[fldManagerComment], [t].[fldManagersLogonID], [t].[fldMgrModifiedBy], [t].[fldMgrModifiedDate], [t].[fldPJobCode], [t].[fldPeoplesoftID], [t].[fldRequestDateEnd], [t].[fldRequestDateStart], [t].[fldRestaurantNumber], [t].[fldSAM], [t].[fldSeriesID], [t].[fldStatus], [t].[fldTempScheduleChange], [t].[fldUserUpdateDate], [t].[fldfName], [t].[fldlName]
FROM [TimeOffRequest] AS [t]
ORDER BY [t].[fldEntryDate] DESC

问题分析 #

从生成的SQL可以明确看到，EF Core错误地将数据库表名tbl_TimeOffRequests替换为TimeOffRequest，移除了tbl_前缀。由于数据库中不存在TimeOffRequest表，SQL引擎判定所有列都无效，从而抛出错误。

解决方法 #

1. 检查OnModelCreatingPartial实现 #

查看OnModelCreatingPartial方法的代码，如果存在修改TimeOffRequest表名的逻辑，直接删除或调整，避免覆盖OnModelCreating中的表名配置。

2. 显式指定Schema #

如果表属于dbo等特定Schema，在ToTable中明确指定，确保表名映射准确：

entity.ToTable("tbl_TimeOffRequests", "dbo");

3. 禁用或自定义表名约定 #

如果脚手架生成时默认启用了移除前缀的约定，在OnModelCreating中全局调整：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 移除默认的表名前缀移除约定
    modelBuilder.Model.RemoveAnnotation("Relational:TableNamePrefix", "tbl_");
    
    // 确保实体绑定正确表名
    modelBuilder.Entity<TimeOffRequest>().ToTable("tbl_TimeOffRequests");
    
    // 其他实体配置...
}

4. 重新生成脚手架并保留原表名 #

执行Scaffold命令时添加-UseDatabaseNames参数，直接使用数据库原表名和列名，避免自动修改命名：

Scaffold-DbContext "你的数据库连接字符串" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables tbl_TimeOffRequests -UseDatabaseNames

验证 #

修改配置后重新执行查询，查看EF Core生成的SQL是否使用tbl_TimeOffRequests作为表名。表名正确的情况下，列不存在的错误会自动消失。

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