EF Core 6中如何使用Where子句过滤关联子表

目标SQL #

需要生成的SQL语句如下：

SELECT * 
FROM Shops
JOIN Products P ON Shops.Id = P.ShopId
WHERE Shops.Id = 1 AND P.IsDeleted = 0;

实体模型定义 #

Shop 模型 #

public class Shop
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;

    public IList<PaymentMethod> PaymentMethods { get; set; } = default!;
    public ICollection<ApplicationUserShop> ApplicationUserShops { get; set; } = default!;
    public ICollection<Product> Products { get; set; } = default!;
}

Product 模型 #

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public decimal Price { get; set; }
    public int ShopId { get; set; }
    public Shop Shop { get; set; } = default!;
    public bool IsDeleted { get; set; } = false;
}

初始代码的问题 #

最初尝试在Include中添加过滤条件的写法无法生成目标SQL的过滤逻辑：

public async Task<Shop?> GetShopWithProductsByIdAsync(int id)
{
    return await _context.Shops
                         .Include(s => s.Products.Where(p => !p.IsDeleted))
                         .FirstOrDefaultAsync(s => s.Id == id);
}

原因是EF Core中Include内的过滤会在加载所有关联数据后进行内存过滤，不会将过滤条件整合到JOIN的WHERE子句中，因此无法过滤已删除的Product记录。

正确实现代码 #

通过显式Join+GroupBy的方式，可以生成符合要求的SQL：

public async Task<Shop?> GetShopWithProductsByIdAsync(int id)
{
    return await _context.Shops
        .Join(_context.Products.Where(p => !p.IsDeleted),
            s => s.Id,
            p => p.ShopId,
            (s, p) => new {s, p})
        .GroupBy(x => x.s)
        .Select(x => new Shop
        {
            Id = x.Key.Id,
            Name = x.Key.Name,
            Products = x.Select(y => y.p).ToList()
        })
        .FirstOrDefaultAsync(x => x.Id == id);
}

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