.NET Core Quartz任务访问SQL Server表报错：对象名'Reports.PreviousReportResult'无效

问题场景 #

在.NET Core项目中使用Quartz执行后台任务，任务执行时访问PreviousReportResult表报错，其他表访问正常。SSMS中直接查询该表无问题，但代码中无论用EF Core还是直接SQL都提示表不存在。

Quartz任务代码 #

public class ReportsChangesJob : IJob
{
    private readonly IReportsService _reportsService;

    public ReportsChangesJob(IReportsService reportsService)
    {
        _reportsService = reportsService;
    }

    public async Task Execute(IJobExecutionContext context)
    {
        await _reportsService.CheckReportsChanges(context.CancellationToken);
    }
}

表访问代码 #

通过仓储访问表的代码：

var change = await _repository.Get<PreviousReportResult>(e => e.ReportId == report.Id);

直接SQL访问的代码：

string sql = "SELECT TOP(1) [Id], [ItemsCount], [Json], [ReportId] FROM [Reports.PreviousReportResult] WHERE [ReportId] = @reportId";
var result = await Context.PreviousReportResults.FromSqlRaw(sql, new SqlParameter("@reportId", reportId)).ToListAsync();
return result.FirstOrDefault();

错误日志 #

Quartz.Core.ErrorLogger[0]
Job DEFAULT.ReportsChangesJob threw an exception.
Quartz.SchedulerException: Job threw an unhandled exception.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Reports.PreviousReportResult'.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__188_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)...

实体配置与迁移代码 #

实体配置：

public class PreviousReportResultConfiguration : IEntityTypeConfiguration<PreviousReportResult>
{
    public void Configure(EntityTypeBuilder<PreviousReportResult> builder)
    {
        builder.ToTable("Reports.PreviousReportResult");

        builder.HasOne(e => e.Report)
            .WithOne()
            .HasForeignKey<PreviousReportResult>(e => e.ReportId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

迁移代码：

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<bool>(
        name: "NotificationOnChange",
        table: "Reports.Reports",
        type: "bit",
        nullable: false,
        defaultValue: false);

    migrationBuilder.CreateTable(
        name: "Reports.PreviousReportResult",
        columns: table => new
        {
            Id = table.Column<int>(type: "int", nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            ItemsCount = table.Column<int>(type: "int", nullable: false),
            Json = table.Column<string>(type: "nvarchar(max)", nullable: true),
            ReportId = table.Column<int>(type: "int", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Reports.PreviousReportResult", x => x.Id);
            table.ForeignKey(
                name: "FK_Reports.PreviousReportResult_Reports.Reports_ReportId",
                column: x => x.ReportId,
                principalTable: "Reports.Reports",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        });

    migrationBuilder.CreateIndex(
        name: "IX_Reports.PreviousReportResult_ReportId",
        table: "Reports.PreviousReportResult",
        column: "ReportId",
        unique: true);
}

说明：已将配置添加到DbContext，创建了对应的DbSet。

排查思路与解决办法 #

1. 修正架构与表名的定义方式 #

SQL Server中架构和表名的正确分隔格式是[架构名].[表名]，你当前的写法将Reports.PreviousReportResult当作了单个表名，而非Reports架构下的PreviousReportResult表。

实体配置修改：

将实体配置中的表名写法改为EF Core标准的架构+表名格式：

builder.ToTable("PreviousReportResult", "Reports");

第一个参数是表名，第二个参数是架构名。

2. 修正迁移代码中的表定义 #

迁移代码需明确区分架构和表名，修改如下：

migrationBuilder.CreateTable(
    name: "PreviousReportResult",
    schema: "Reports",
    columns: table => new
    {
        Id = table.Column<int>(type: "int", nullable: false)
            .Annotation("SqlServer:Identity", "1, 1"),
        ItemsCount = table.Column<int>(type: "int", nullable: false),
        Json = table.Column<string>(type: "nvarchar(max)", nullable: true),
        ReportId = table.Column<int>(type: "int", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_PreviousReportResult", x => x.Id);
        table.ForeignKey(
            name: "FK_PreviousReportResult_Reports_ReportId",
            column: x => x.ReportId,
            principalSchema: "Reports",
            principalTable: "Reports",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_PreviousReportResult_ReportId",
    schema: "Reports",
    table: "PreviousReportResult",
    column: "ReportId",
    unique: true);

3. 修正直接SQL语句中的表名 #

将SQL中的表名改为[Reports].[PreviousReportResult]：

string sql = "SELECT TOP(1) [Id], [ItemsCount], [Json], [ReportId] FROM [Reports].[PreviousReportResult] WHERE [ReportId] = @reportId";

4. 验证连接字符串与数据库权限 #

• 确认DbContext的连接字符串指向正确的数据库实例和库名，避免连接到无该表的数据库。
• 检查应用程序使用的数据库登录用户是否拥有Reports架构的访问权限，避免因权限不足导致表无法识别。

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