多对多关系下获取所有产品名称（含无分类产品）的SQL及EF Linq实现

问题背景 #

已通过EF Code First配置Product与CategoryProduct的多对多关系，现有SQL查询仅能返回关联了分类的产品名称，需求调整为获取所有产品名称——即使产品未关联任何分类，需要实现对应的EF Linq查询。

模型代码 #

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<CategoryProduct> CategoryProducts { get; set; }
}

public class CategoryProduct
{
    public int CategoryProductId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

internal class EFDbContext : DbContext, IDBProductContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<CategoryProduct> CategoryProducts { get; set ; }

    public EFDbContext()
    {
        Database.SetInitializer<EFDbContext>(new DropCreateDatabaseIfModelChanges<EFDbContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasMany(p => p.CategoryProducts)
            .WithMany(c => c.Products)
            .Map(pc => {
                pc.MapLeftKey("ProductRefId");
                pc.MapRightKey("CategoryProductRefId");
                pc.ToTable("CategoryProductTable");
            });
        base.OnModelCreating(modelBuilder);
    }
}

现有SQL问题 #

当前SQL是内连接逻辑，仅返回存在分类关联的产品：

SELECT p.Name, cp.Name 
FROM CategoryProductTable AS cpt, 
     CategoryProducts AS cp, Products as p
WHERE 
    p.ProductId = cpt.ProductRefId 
    AND cp.CategoryProductId = cpt.CategoryProductRefId

要实现需求，需要改成左外连接，对应Linq有两种常用写法：

解决方案 #

写法1：查询语法（左外连接） #

using (var context = new EFDbContext())
{
    var result = from p in context.Products
                 join cpt in context.CategoryProducts
                     on p.ProductId equals cpt.ProductRefId into productCategories
                 from pc in productCategories.DefaultIfEmpty()
                 select new 
                 {
                     ProductName = p.Name,
                     CategoryName = pc?.Name // 未关联分类时为null
                 };
}

写法2：方法语法（GroupJoin + SelectMany） #

using (var context = new EFDbContext())
{
    var result = context.Products
        .GroupJoin(context.CategoryProducts,
            p => p.ProductId,
            cp => cp.ProductRefId,
            (p, cps) => new { Product = p, Categories = cps })
        .SelectMany(x => x.Categories.DefaultIfEmpty(),
            (x, cp) => new 
            {
                ProductName = x.Product.Name,
                CategoryName = cp?.Name
            });
}

说明 #

两种写法都会生成左外连接的SQL，确保所有Product都被返回：

• 若产品关联了多个分类，会返回多条对应记录（和原SQL的行为一致，只是新增了无分类的产品记录）
• 未关联分类的产品，CategoryName字段会是null

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