EF Core 6中带条件的Include为何生成Left Join？如何强制用Inner Join？

问题描述 #

在使用EF Core 6编写查询时，通过Include关联子集合并添加过滤条件，生成的SQL却使用了LEFT JOIN而非预期的INNER JOIN；即使后续通过Where(userGroup => userGroup.UserToUserGroups.Any())过滤了存在关联数据的分组，仍想了解该行为的原因，以及是否有配置选项可以更改这一行为。

代码实现 #

public async Task<IEnumerable<UserGroup>> GetUserGroupWithUser()
{
    var UserIds = new List<int>{ 1};

    var userGroups = await context.UserGroups.Include(userGroup => 
     userGroup.UserToUserGroups.Where(ugtu => ugtu.IsActive && 
     UserIds.Contains(ugtu.UserId)))
     .ThenInclude(ugtu => ugtu.User)
     .Where(userGroup => userGroup.UserToUserGroups.Any())
     //Removed some projection
     .ToListAsync();

     return userGroups;
}

生成的SQL查询 #

SELECT [u].[Id], [u].[IsActive], [u].[Name], [t].[Id], [t].[IsActive], [t].[UserGroupId], [t].[UserId], [t].[Id0], [t].[IsActive0], [t].[Name]
FROM [UserGroups] AS [u]
LEFT JOIN (
    SELECT [u1].[Id], [u1].[IsActive], [u1].[UserGroupId], [u1].[UserId], [u2].[Id] AS [Id0], [u2].[IsActive] AS [IsActive0], [u2].[Name]
    FROM [UserToUserGroups] AS [u1]
    INNER JOIN [Users] AS [u2] ON [u1].[UserId] = [u2].[Id]
    WHERE ([u1].[IsActive] = CAST(1 AS bit)) AND ([u1].[UserId] = 1)
) AS [t] ON [u].[Id] = [t].[UserGroupId]
WHERE EXISTS (
    SELECT 1
    FROM [UserToUserGroups] AS [u0]
    WHERE [u].[Id] = [u0].[UserGroupId])
ORDER BY [u].[Id], [t].[Id]

实体类 #

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<UserToUserGroup> UserToUserGroups { get; set;}
}

 public class UserToUserGroup
 {
    public int Id { get; set; }
    public int UserId { get; set; }
    public int UserGroupId { get; set; }
    public bool IsActive { get; set; }
    public virtual UserGroup UserGroup { get; set; }
    public virtual User User { get; set; }
 }

public class UserGroup
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<UserToUserGroup> UserToUserGroups { get; set; }
}

配置代码 #

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable("Users");
        builder.HasKey(user => user.Id);
        builder.Property(user => user.Id)
            .IsRequired()
            .ValueGeneratedOnAdd();
    }
}

public class UserGroupConfiguration : IEntityTypeConfiguration<UserGroup>
{
    public void Configure(EntityTypeBuilder<UserGroup> builder)
    {
        builder.ToTable("UserGroups");
        builder.HasKey(userGroup => userGroup.Id);
        builder.Property(userGroup => userGroup.Id)
            .IsRequired()
            .ValueGeneratedOnAdd();
    }
}

public class UserToUserGroupConfiguration : IEntityTypeConfiguration<UserToUserGroup>
{
    public void Configure(EntityTypeBuilder<UserToUserGroup> builder)
    {
        builder.ToTable("UserToUserGroups");
        builder.HasKey(ugtu => ugtu.Id);
        builder.Property(ugtu => ugtu.Id)
            .IsRequired()
            .ValueGeneratedOnAdd();

        builder.HasIndex(ugtu => new
        {
            ugtu.UserId,
            ugtu.UserGroupId
        }).HasFilter("IsActive = 'true'").IsUnique();

        builder
            .HasOne(utug => utug.User)
            .WithMany(user => user.UserToUserGroups)
            .HasForeignKey(ugtu => ugtu.UserId)
            .OnDelete(DeleteBehavior.Cascade);
        builder.HasOne(utug => utug.UserGroup)
            .WithMany(user => user.UserToUserGroups)
            .HasForeignKey(ugtu => ugtu.UserGroupId)
            .OnDelete(DeleteBehavior.Cascade);
        
    }
}

已尝试操作 #

• 在实体配置中使用了IsRequired()方法

解答 #

原因分析 #

EF Core 6中，对Include添加过滤条件（即Include(...Where(...))）时生成LEFT JOIN是设计行为，核心原因有两点：

1. 保留主实体完整性：Include的语义是「加载主实体的关联数据」，而非「过滤主实体」。即使过滤后的子集合为空，EF Core仍会返回主实体，LEFT JOIN恰好能满足这一语义。
2. 操作语义分离：后续添加的Where(userGroup => userGroup.UserToUserGroups.Any())是独立的主实体过滤逻辑，EF Core会将其转换为EXISTS子查询，而非修改Include生成的JOIN类型——这两个操作的职责不同，前者筛选主实体，后者筛选要加载的关联数据。

修改行为的方法 #

EF Core目前没有直接的配置选项可以将带过滤的Include从LEFT JOIN改为INNER JOIN，但可以通过以下两种方式实现类似效果：

方案1：使用LINQ投影替代Include

通过投影直接筛选关联数据，EF Core会自动生成INNER JOIN：

public async Task<IEnumerable<UserGroup>> GetUserGroupWithUser()
{
    var UserIds = new List<int>{ 1};

    var userGroups = await context.UserGroups
        .Where(ug => ug.UserToUserGroups.Any())
        .Select(ug => new UserGroup
        {
            Id = ug.Id,
            Name = ug.Name,
            IsActive = ug.IsActive,
            UserToUserGroups = ug.UserToUserGroups
                .Where(ugtu => ugtu.IsActive && UserIds.Contains(ugtu.UserId))
                .Select(ugtu => new UserToUserGroup
                {
                    Id = ugtu.Id,
                    IsActive = ugtu.IsActive,
                    User = ugtu.User,
                    UserId = ugtu.UserId,
                    UserGroupId = ugtu.UserGroupId
                })
                .ToList()
        })
        .ToListAsync();

    return userGroups;
}

方案2：手动编写显式JOIN查询

直接使用Join方法完全控制JOIN类型，精准实现业务逻辑：

public async Task<IEnumerable<UserGroup>> GetUserGroupWithUser()
{
    var UserIds = new List<int>{ 1};

    var query = from ug in context.UserGroups
                join ugtu in context.UserToUserGroups
                    on ug.Id equals ugtu.UserGroupId
                join u in context.Users
                    on ugtu.UserId equals u.Id
                where ugtu.IsActive && UserIds.Contains(ugtu.UserId)
                group new { ugtu, u } by ug into grouped
                select new UserGroup
                {
                    Id = grouped.Key.Id,
                    Name = grouped.Key.Name,
                    IsActive = grouped.Key.IsActive,
                    UserToUserGroups = grouped.Select(g => new UserToUserGroup
                    {
                        Id = g.ugtu.Id,
                        IsActive = g.ugtu.IsActive,
                        UserId = g.ugtu.UserId,
                        UserGroupId = g.ugtu.UserGroupId,
                        User = g.u
                    }).ToList()
                };

    return await query.ToListAsync();
}

注意事项 #

• 使用投影时，若需要EF Core跟踪实体以便后续修改保存，需确保投影的实体被上下文正确跟踪（可按需使用AsNoTracking关闭跟踪）。
• 显式JOIN查询虽然灵活性高，但代码复杂度会有所提升，需根据业务场景选择合适的方案。

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