如何在EF Core中实现多对多关联查询并按Active字段筛选记录

你可以通过两种方式实现该需求，分别适配是否配置了实体导航属性的场景：

前提准备 #

首先确保你的DbContext中已经注册了所有对应表的DbSet：

// 你的DbContext类中添加以下属性
public DbSet<ProductCategory> ProductCategories { get; set; }
public DbSet<Product> Products { get; set; }
public DbSet<ProductComplementCategory> ProductComplementCategories { get; set; }
public DbSet<ComplementCategory> ComplementCategories { get; set; }
public DbSet<ComplementCategoryComplement> ComplementCategoryComplements { get; set; }
public DbSet<Complement> Complements { get; set; }

方案1：手动Join（和原生SQL逻辑完全对齐，无需依赖导航属性） #

这种写法和你给出的SQL逻辑一一对应，不需要提前配置实体间的导航关系，适配度最高：

var queryResult = await _dbContext.ProductCategories
    // 关联Product表
    .Join(_dbContext.Products,
        pc => pc.Uid,
        p => p.ProductCategoryId,
        (pc, p) => new { pc, p })
    // 关联ProductComplementCategory表
    .Join(_dbContext.ProductComplementCategories,
        join1 => join1.p.Uid,
        pcc => pcc.ProductID,
        (join1, pcc) => new { join1.pc, join1.p, pcc })
    // 关联ComplementCategory表
    .Join(_dbContext.ComplementCategories,
        join2 => join2.pcc.ComplementCategoryID,
        cc => cc.Uid,
        (join2, cc) => new { join2.pc, join2.p, cc })
    // 关联ComplementCategoryComplements表
    .Join(_dbContext.ComplementCategoryComplements,
        join3 => join3.cc.Uid,
        ccc => ccc.ComplementCategoryID,
        (join3, ccc) => new { join3.pc, join3.p, join3.cc, ccc })
    // 关联Complement表
    .Join(_dbContext.Complements,
        join4 => join4.ccc.ComplementID,
        c => c.Uid,
        (join4, c) => new { join4.pc, join4.p, join4.cc, c })
    // 筛选四个表Active=1的条件
    .Where(result => result.pc.Active == 1 
                    && result.p.Active == 1 
                    && result.cc.Active == 1 
                    && result.c.Active == 1)
    // 最终返回所需字段，和SQL逻辑一致返回四个表的全部字段
    .Select(result => new 
    {
        ProductCategory = result.pc,
        Product = result.p,
        ComplementCategory = result.cc,
        Complement = result.c
    })
    .ToListAsync();

如果你的Active字段是布尔类型，把筛选条件里的==1改为==true即可。

方案2：导航属性查询（EF Core推荐写法，需提前配置关联关系） #

如果你的实体已经配置了对应导航属性（比如Product实体中有ProductCategory导航属性、Product实体中有ProductComplementCategories集合导航属性等），可以用更简洁的写法：

var queryResult = await _dbContext.ProductCategories
    .Include(pc => pc.Products)
        .ThenInclude(p => p.ProductComplementCategories)
            .ThenInclude(pcc => pcc.ComplementCategory)
                .ThenInclude(cc => cc.ComplementCategoryComplements)
                    .ThenInclude(ccc => ccc.Complement)
    .Where(pc => pc.Active == 1
                && pc.Products.Any(p => p.Active == 1)
                && pc.Products.SelectMany(p => p.ProductComplementCategories)
                    .Select(pcc => pcc.ComplementCategory)
                    .Any(cc => cc.Active == 1)
                && pc.Products.SelectMany(p => p.ProductComplementCategories)
                    .SelectMany(pcc => pcc.ComplementCategory.ComplementCategoryComplements)
                    .Select(ccc => ccc.Complement)
                    .Any(c => c.Active == 1))
    .Select(pc => new 
    {
        ProductCategory = pc,
        Products = pc.Products.Where(p => p.Active == 1),
        ComplementCategories = pc.Products
            .SelectMany(p => p.ProductComplementCategories)
            .Select(pcc => pcc.ComplementCategory)
            .Where(cc => cc.Active ==1),
        Complements = pc.Products
            .SelectMany(p => p.ProductComplementCategories)
            .SelectMany(pcc => pcc.ComplementCategory.ComplementCategoryComplements)
            .Select(ccc => ccc.Complement)
            .Where(c => c.Active ==1)
    })
    .ToListAsync();

两种写法最终生成的SQL和你给出的原生SQL逻辑一致，可根据实际项目配置选择使用。

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