如何使用C# LINQ按ID关联三个集合并生成指定结果？

问题描述 #

我有三个包含共同键shopId的集合：

var shops = new[] { new { shopId = "1", shopName = "shop1" }, new { shopId = "2", shopName = "shop2" } , new { shopId = "3", shopName = "shop3" }};
var sales = new[] { new { shopId = "1", sales = "1000", place = 2 }, new { shopId = "2", sales = "0", place = 1 } , new { shopId = "3", sales = "100", place = 3 }};
var unpostedChecks = new[] { new { shopId = "1", checkQnt = 2 }};

需要通过LINQ将它们关联，得到如下结果：

var result = new[] { 
    new { shopId = "1", shopName = "shop1", sales = "1000", place = 2, checkQnt = 2 }, 
    new { shopId = "2", shopName = "shop2", place = 1 , sales = 0, checkQnt = 0},
    new { shopId = "3", shopName = "shop3", sales = "100", place = 3 , checkQnt = 0}
};

实现代码 #

以shops作为主集合，通过内连接关联sales（每个店铺都有对应销售数据），通过左外连接关联unpostedChecks（部分店铺无对应支票数据），处理空值即可得到目标结果：

var result = shops
    // 内连接shops和sales，获取店铺基础信息+销售数据
    .Join(sales,
          shop => shop.shopId,
          sale => sale.shopId,
          (shop, sale) => new 
          { 
              shop.shopId, 
              shop.shopName, 
              // 若需统一为int类型，可改为 int.Parse(sale.sales)
              SaleValue = sale.sales, 
              sale.place 
          })
    // 左外连接unpostedChecks，处理无支票数据的空值场景
    .GroupJoin(unpostedChecks,
               combined => combined.shopId,
               check => check.shopId,
               (combined, checks) => new 
               { 
                   combined.shopId,
                   combined.shopName,
                   sales = combined.SaleValue,
                   combined.place,
                   // 无对应支票数据时，checkQnt设为0
                   checkQnt = checks.FirstOrDefault()?.checkQnt ?? 0 
               });

关键说明 #

• 内连接Join：由于sales集合中每个店铺都有对应数据，直接关联即可完整获取销售信息。
• 左外连接GroupJoin：确保即使店铺没有对应的unpostedChecks数据，也能保留在结果集中，通过FirstOrDefault()?.checkQnt ?? 0将空值替换为0，匹配需求中的默认值。
• 类型统一：原集合中sales为字符串类型，若需要和示例结果中shop2的0（int类型）统一，可将代码中的sale.sales改为int.Parse(sale.sales)。

内容的提问来源于stack exchange，提问作者Артем Бахирев