EF Core 8多对多关联Count查询生成错误SQL问题求助

问题背景 #

使用SQL Server 2019 + EF Core 8，实体与数据库表的映射关系如下：

• 实体TenantOrganization → 数据库表[Tenant].[Organizations]
• 实体SharedIndustry → 数据库表[Shared].[Industries]
• 中间实体TenantOrganizationIndustry → 数据库表[Tenant].[OrganizationIndustries]，用于建立两者的多对多关联

需要通过Industry的ID统计其关联的OrganizationIndustries数量，判断是否允许删除该Industry，但EF Core生成的SQL存在表名错误：将[Tenant].[Organizations]错误命名为[TenantOrganization]，既忽略架构，又将表名单数化。

生成的错误SQL #

SELECT TOP(1) (
    SELECT COUNT(*)
    FROM [Tenant].[OrganizationIndustries] AS [o]
    INNER JOIN [TenantOrganization] AS [t] ON [o].[OrganizationId] = [t].[Id]-- WRONG table name
    WHERE [i].[Id] = [o].[IndustryId])
FROM [Shared].[Industries] AS [i]
WHERE [i].[Id] = @__command_Id_0

期望的正确SQL #

SELECT TOP(1)   (
                    SELECT  COUNT(*)
                    FROM    [Tenant].[OrganizationIndustries] AS [TOI]
                            INNER JOIN [Tenant].[Organizations] AS [TO] ON ([TOI].[OrganizationId] = [TO].[Id])-- CORRECT table name
                    WHERE   ([SI].[Id] = [TOI].[IndustryId])
                )
FROM            [Shared].[Industries] AS [SI]
WHERE           ([SI].[Id] = @__command_Id_0);

当前使用的LINQ查询 #

_shared.Industries.Where(i => i.Id == command.Id).Select(i => i.Organizations.Count).FirstAsync(cancellationToken)

DbContext中的导航配置 #

builder.Entity<TenantOrganization>().HasMany(
    to => to.Industries).WithMany(
    si => si.Organizations).UsingEntity(
        "OrganizationIndustries",
        l => l.HasOne(typeof(SharedIndustry)).WithMany().HasForeignKey(nameof(TenantOrganizationIndustry.IndustryId)).HasPrincipalKey(nameof(SharedIndustry.Id)),
        r => r.HasOne(typeof(TenantOrganization)).WithMany().HasForeignKey(nameof(TenantOrganizationIndustry.OrganizationId)).HasPrincipalKey(nameof(TenantOrganization.Id)),
        j => j.ToTable("OrganizationIndustries", "Tenant"));

EF Core实体模型 #

public sealed class SharedIndustry {
    public SharedIndustryId Id { get; set; }
    public ICollection<TenantOrganization> Organizations { get; } = [];
}

public sealed class TenantOrganization {
    public TenantOrganizationId Id { get; set; }
    public ICollection<SharedIndustry> Industries { get; } = [];
}

public sealed class TenantOrganizationIndustry {
    public SharedIndustryId IndustryId { get; set; }
    public TenantOrganizationId OrganizationId { get; set; }
}

疑问：是否必须为TenantOrganizationIndustry创建DbSet才能解决问题？同时期望生成最优SQL直接查询中间表：

SELECT  COUNT(*)
FROM    [Tenant].[OrganizationIndustries]
WHERE   ([IndustryId] = @__command_Id_0);

解决方案 #

1. 显式配置实体与数据库表的映射 #

EF Core默认会将实体名作为表名，因此需要手动指定TenantOrganization和SharedIndustry对应的数据库表名及架构，这是解决表名错误的核心：

// 配置TenantOrganization对应Tenant架构下的Organizations表
builder.Entity<TenantOrganization>()
    .ToTable("Organizations", "Tenant");

// 配置SharedIndustry对应Shared架构下的Industries表
builder.Entity<SharedIndustry>()
    .ToTable("Industries", "Shared");

2. 优化多对多关联配置 #

使用泛型版本的UsingEntity替代typeof，让EF更清晰识别中间实体类型，避免映射歧义：

builder.Entity<TenantOrganization>()
    .HasMany(to => to.Industries)
    .WithMany(si => si.Organizations)
    .UsingEntity<TenantOrganizationIndustry>(
        left => left.HasOne<SharedIndustry>().WithMany().HasForeignKey(oi => oi.IndustryId),
        right => right.HasOne<TenantOrganization>().WithMany().HasForeignKey(oi => oi.OrganizationId),
        join => join.ToTable("OrganizationIndustries", "Tenant")
    );

3. 实现最优查询（无需显式定义DbSet） #

即使没有为TenantOrganizationIndustry定义DbSet，也可以通过DbContext.Set<T>()直接访问该实体类型，生成期望的最优SQL：

var count = await _context.Set<TenantOrganizationIndustry>()
    .CountAsync(oi => oi.IndustryId == command.Id, cancellationToken);

验证效果 #

完成上述配置后，原LINQ查询将生成正确的SQL，关联[Tenant].[Organizations]表并正确带上架构；同时通过Set<TenantOrganizationIndustry>的方式可以直接查询中间表的关联数量，性能更优。

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