ASP.NET Core中Lambda查询关联Inventory与Article表的问题

问题背景 #

现有两张表Inventory和Article，同时定义了零件列表类SteelSheet，需要查询仅包含steelSheet列表中零件的Inventory和Article数据，同时获取Article.Price和Inventory.Stock字段。

类定义 #

SteelSheet 类 #

public class SteelSheet
{
    public string Part { get; set; }
    public float Quantity { get; set; }
}

Inventory 实体类 #

public class Inventory
{
    public int ID { get; set; }
    public int Stock { get; set; }
    public int ArticleID { get; set; }
    public Article Article { get; set; }
}

Article 实体类 #

public class Article
{
    public int ID { get; set; }
    public string Part { get; set; }
    public float Price { get; set; }
    public ICollection<Inventory> Inventory { get; set; }
}

已准备的零件列表数据 #

var steelSheet = new List<SteelSheet>();
steelSheet.Add(new SteelSheet() { Part = "PART0001", Quantity = 8 });
steelSheet.Add(new SteelSheet() { Part = "PART0002", Quantity = 3 });

注：原代码中Quantity赋值为字符串存在类型不匹配问题，已修正为数值类型

解决方案 #

通过筛选Article.Part在steelSheet列表的零件集合中实现需求，可选择投影到匿名类型或自定义类：

方式1：投影到匿名类型 #

// 先提取零件号集合，优化查询性能
var partNumbers = steelSheet.Select(s => s.Part).ToList();

var sheetTest = _context.Inventories
    .Include(x => x.Article)
    .Where(x => partNumbers.Contains(x.Article.Part))
    .Select(x => new 
    {
        Part = x.Article.Part,
        Stock = x.Stock,
        Price = x.Article.Price,
        Quantity = steelSheet.FirstOrDefault(s => s.Part == x.Article.Part)?.Quantity
    })
    .ToList();

方式2：投影到自定义扩展类 #

先定义包含所需字段的扩展类：

public class SteelSheetDetail : SteelSheet
{
    public int Stock { get; set; }
    public float Price { get; set; }
}

再执行查询：

var partNumbers = steelSheet.Select(s => s.Part).ToList();

var sheetTest = _context.Inventories
    .Include(x => x.Article)
    .Where(x => partNumbers.Contains(x.Article.Part))
    .Select(x => new SteelSheetDetail
    {
        Part = x.Article.Part,
        Quantity = steelSheet.FirstOrDefault(s => s.Part == x.Article.Part)?.Quantity ?? 0,
        Stock = x.Stock,
        Price = x.Article.Price
    })
    .ToList();

额外说明 #

• 若单个零件对应多条Inventory记录，查询会返回多条数据，可根据业务需求添加GroupBy进行聚合处理；
• 提前提取partNumbers集合可避免EF Core将整个steelSheet对象转换为SQL条件，提升查询效率。

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