优化EF Core多对多查询：单表关联获取Tenant与Team

问题背景 #

拥有User、Tenant、Team三个实体，User与Tenant、User与Team均为多对多关系，共用UserTenant关联表，业务逻辑为每个用户按租户分配对应团队（Team为必填字段）。

实体模型代码 #

public class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }

    public virtual ICollection<Tenant> Tenants {get; set;}
    public virtual ICollection<Team> Teams { get; set; }
    public virtual ICollection<UserTenant> UserTenants { get; set; }
}

public class Tenant
{
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<UserTenant> UserTenants { get; set; }
}

public class Team
{
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<UserTenant> UserTenants { get; set; }
}

public class UserTenant
{
    public int UserId { get; set; }
    public int TenantId { get; set; }
    public int TeamId { get; set; }

    [ForeignKey(nameof(UserId))]
    public virtual User User { get; set; }

    [ForeignKey(nameof(TenantId))]
    public virtual Tenant Tenant { get; set; }

    [ForeignKey(nameof(TeamId))]
    public virtual Team Team { get; set; }
}

现有EF配置 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Team>().HasQueryFilter(m => m.TenantId == _tenantId);
    modelBuilder.Entity<UserTenant>().HasQueryFilter(m => _tenantId == Guid.Empty || m.TenantId == _tenantId);
}

public class UserEntityConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasMany(u => u.Tenants)
            .WithMany()
            .UsingEntity<UserTenant>();

        builder.HasMany(u => u.Teams)
            .WithMany()
            .UsingEntity<UserTenant>();
    }
}

当前问题 #

执行同时包含Tenant和Team的查询时，EF生成的SQL会两次关联UserTenant表，一次获取租户信息，一次获取团队信息，造成查询冗余。

当前LINQ查询：

var query = _dbContext.Users
            .Include(x => x.Tenants)
            .Include(x => x.Teams);

var sqlString = query.ToQueryString();

生成的SQL存在两次对user_tenants表的左连接，核心片段如下：

SELECT a.id, a.name, a.email, t1.id, t1.name, t2.id, t2.name
FROM asp_net_users AS a
LEFT JOIN (
    SELECT u.user_id, t.id, t.name
    FROM user_tenants AS u
    INNER JOIN teams AS t ON u.team_id = t.id
    WHERE @__ef_filter___tenantId_1 = Guid.Empty OR u.tenant_id = @__ef_filter___tenantId_1
) AS t1 ON a.id = t1.user_id
LEFT JOIN (
    SELECT u0.user_id, t3.id, t3.name
    FROM user_tenants AS u0
    INNER JOIN tenants AS t3 ON u0.tenant_id = t3.id
    WHERE @__ef_filter___tenantId_2 = Guid.Empty OR u0.tenant_id = @__ef_filter___tenantId_2
) AS t2 ON a.id = t2.user_id
ORDER BY a.id

需求：让EF生成仅单次关联UserTenant表即可获取Tenant和Team数据的查询。

解决方案 #

问题根源 #

将UserTenant同时用作User-Tenant、User-Team两个多对多关系的关联表，EF Core会将这视为两个独立的多对多关联，因此查询时会分别连接两次关联表。

1. 调整实体导航属性 #

修改User实体，移除独立的Tenants和Teams集合，直接通过UserTenants导航属性关联Tenant和Team——因为UserTenant本身已包含三者的关联关系：

public class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }

    // 移除原有的Tenants和Teams集合，保留UserTenants即可
    public virtual ICollection<UserTenant> UserTenants { get; set; }
}

2. 调整EF配置 #

不再配置User-Tenant和User-Team的多对多关系，转而明确配置UserTenant作为实体的一对一关联，并设置复合主键：

public class UserTenantEntityConfiguration : IEntityTypeConfiguration<UserTenant>
{
    public void Configure(EntityTypeBuilder<UserTenant> builder)
    {
        // 设置复合主键，确保关联关系的唯一性
        builder.HasKey(ut => new { ut.UserId, ut.TenantId, ut.TeamId });

        // 配置与User的一对多关联
        builder.HasOne(ut => ut.User)
            .WithMany(u => u.UserTenants)
            .HasForeignKey(ut => ut.UserId);

        // 配置与Tenant的一对多关联
        builder.HasOne(ut => ut.Tenant)
            .WithMany(t => t.UserTenants)
            .HasForeignKey(ut => ut.TenantId);

        // 配置与Team的一对多关联
        builder.HasOne(ut => ut.Team)
            .WithMany(t => t.UserTenants)
            .HasForeignKey(ut => ut.TeamId);
    }
}

在OnModelCreating中注册该配置，并移除原有的多对多配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Team>().HasQueryFilter(m => m.TenantId == _tenantId);
    modelBuilder.Entity<UserTenant>().HasQueryFilter(m => _tenantId == Guid.Empty || m.TenantId == _tenantId);
    
    // 注册UserTenant的实体配置
    modelBuilder.ApplyConfiguration(new UserTenantEntityConfiguration());
}

3. 修改查询方式 #

通过嵌套Include一次性获取UserTenant及其关联的Tenant和Team，EF只会关联一次user_tenants表：

var query = _dbContext.Users
    .Include(u => u.UserTenants)
        .ThenInclude(ut => ut.Tenant)
    .Include(u => u.UserTenants)
        .ThenInclude(ut => ut.Team);

var sqlString = query.ToQueryString();

优化后效果 #

生成的SQL只会连接一次user_tenants表，同时关联tenants和teams表，避免了重复连接，核心片段如下：

SELECT a.id, a.name, a.email, ut.tenant_id, ut.team_id, t.id, t.name, tm.id, tm.name
FROM asp_net_users AS a
LEFT JOIN user_tenants AS ut ON a.id = ut.user_id
LEFT JOIN tenants AS t ON ut.tenant_id = t.id
LEFT JOIN teams AS tm ON ut.team_id = tm.id
WHERE @__ef_filter___tenantId_1 = Guid.Empty OR ut.tenant_id = @__ef_filter___tenantId_1
ORDER BY a.id

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