Optimize query for two many-to-many relationships using the same link table

Question

I have three entities User, Tenant and Team. There is a many-to-many relationship between User and Tenant, and User and Team. I have a single table UserTenant which covers both of these relations. The logic is that each user is assigned to a team per tenant. The team is a mandatory field. Here are the models.

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; }
}

I have established many-to-many relationships for User<->Tenant and User<->Team using the following configuration which works fine.

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>();
    }
}

However, when I run a query to include both Tenant and Team the SQL generated includes two joins to the relation table UserTenant to get information about Tenants and Teams. Is there a way to make EF generate a query that uses a single join with user_tenant table to get both Tenant and Team?

Current linq query and the generated SQL:

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

var sqlString = query.ToQueryString();

The thing to note in the following query (generated by EF core) is the two left joins to user_tenants table. First join gets Tenants for a user and the second join gets Teams for the same user.

SELECT a.id, a.access_failed_count, a.concurrency_stamp, a.email, a.email_confirmed, a.lockout_enabled, a.lockout_end, a.name, a.normalized_email, a.normalized_user_name, a.password_hash, a.phone_number, a.phone_number_confirmed, a.security_stamp, a.two_factor_enabled, a.user_name, t1.id, t1.archived_on, t1.created_on, t1.is_archived, t1.modified_on, t1.team_id, t1.tenant_id, t1.user_id, t1.id0, t1.created_on0, t1.modified_on0, t1.name, t1.system_generated, t1.tenant_id0, t2.id, t2.archived_on, t2.created_on, t2.is_archived, t2.modified_on, t2.team_id, t2.tenant_id, t2.user_id, t2.id0, t2.account_setting_id, t2.address_line1, t2.address_line2, t2.city, t2.country, t2.created_on0, t2.modified_on0, t2.name, t2.owner_id, t2.postal_code
FROM asp_net_users AS a
LEFT JOIN (
    SELECT u.id, u.archived_on, u.created_on, u.is_archived, u.modified_on, u.team_id, u.tenant_id, u.user_id, t0.id AS id0, t0.created_on AS created_on0, t0.modified_on AS modified_on0, t0.name, t0.system_generated, t0.tenant_id AS tenant_id0
    FROM user_tenants AS u
    INNER JOIN (
        SELECT t.id, t.created_on, t.modified_on, t.name, t.system_generated, t.tenant_id
        FROM teams AS t
        WHERE t.tenant_id = @__ef_filter___tenantId_2
    ) AS t0 ON u.team_id = t0.id
    WHERE @__ef_filter__p_0 OR u.tenant_id = @__ef_filter___tenantId_1
) AS t1 ON a.id = t1.user_id
LEFT JOIN (
    SELECT u0.id, u0.archived_on, u0.created_on, u0.is_archived, u0.modified_on, u0.team_id, u0.tenant_id, u0.user_id, t3.id AS id0, t3.account_setting_id, t3.address_line1, t3.address_line2, t3.city, t3.country, t3.created_on AS created_on0, t3.modified_on AS modified_on0, t3.name, t3.owner_id, t3.postal_code
    FROM user_tenants AS u0
    INNER JOIN tenants AS t3 ON u0.tenant_id = t3.id
    WHERE @__ef_filter__p_0 OR u0.tenant_id = @__ef_filter___tenantId_1
) AS t2 ON a.id = t2.user_id
ORDER BY a.id, t1.id, t1.id0, t2.id

Answer 1

After experimenting more with Entity Framework Core 6 I learned how Include works and made me realize what my original query was doing. Include essentially will generate a join with the corresponding entity table (through its linking table if needed). So in my case since I had two Include to the navigation properties, it produced two joins.

So .Include(x => x.Tenants) will create a join block with UserTenants and then a join with Tenants..

Then .Include(x => x.Teams will create another join without considering that we already are joining with UserTenants.

Based on this knowledge I tried the following which solved my issue and now I only get one join with UserTenants.

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

var sqlString = query.ToQueryString();

This produces the following SQL query

SELECT a.id, a.access_failed_count, a.concurrency_stamp, a.email, a.email_confirmed, a.lockout_enabled, a.lockout_end, a.name, a.normalized_email, a.normalized_user_name, a.password_hash, a.phone_number, a.phone_number_confirmed, a.security_stamp, a.two_factor_enabled, a.user_name, t2.id, t2.archived_on, t2.created_on, t2.is_archived, t2.modified_on, t2.team_id, t2.tenant_id, t2.user_id, t2.id0, t2.created_on0, t2.modified_on0, t2.name, t2.system_generated, t2.tenant_id0, t2.id1, t2.account_setting_id, t2.address_line1, t2.address_line2, t2.city, t2.country, t2.created_on1, t2.modified_on1, t2.name0, t2.owner_id, t2.postal_code
FROM asp_net_users AS a
LEFT JOIN (
    SELECT u.id, u.archived_on, u.created_on, u.is_archived, u.modified_on, u.team_id, u.tenant_id, u.user_id, t0.id AS id0, t0.created_on AS created_on0, t0.modified_on AS modified_on0, t0.name, t0.system_generated, t0.tenant_id AS tenant_id0, t1.id AS id1, t1.account_setting_id, t1.address_line1, t1.address_line2, t1.city, t1.country, t1.created_on AS created_on1, t1.modified_on AS modified_on1, t1.name AS name0, t1.owner_id, t1.postal_code
    FROM user_tenants AS u
    INNER JOIN (
        SELECT t.id, t.created_on, t.modified_on, t.name, t.system_generated, t.tenant_id
        FROM teams AS t
        WHERE t.tenant_id = @__ef_filter___tenantId_2
    ) AS t0 ON u.team_id = t0.id
    INNER JOIN tenants AS t1 ON t0.tenant_id = t1.id
    WHERE @__ef_filter__p_0 OR u.tenant_id = @__ef_filter___tenantId_1
) AS t2 ON a.id = t2.user_id
ORDER BY a.id, t2.id, t2.id0