使用LINQ查询实现商品原产国的店铺数、最低价统计及排序

给定两个数据序列：

• goodList：Good类型的商品信息集合，每个元素包含商品SKU（Id）、分类（Category）、**原产国（Country）**字段
• storePriceList：StorePrice类型的店铺商品价格集合，每个元素包含商品SKU（GoodId）、店铺名称（Shop）、**价格（Price）**字段

需要完成以下统计任务：

1. 针对每个原产国，统计售卖该国产商品的独立店铺数量（店铺需去重）
2. 计算该国产商品在所有店铺中的最低售价
3. 统计结果封装为CountryStat类型（包含Country、MinPrice、StoresNumber三个字段）
4. 若某国无任何商品在店铺售卖，则StoresNumber和MinPrice均设为0
5. 最终结果按原产国名称排序

当前已有初步思路：考虑将storePriceList按GoodId分组后选取对应商品的最低价格，但不清楚后续步骤。

示例数据 #

// 商品信息列表
var goodList = new[]
{
    new Good{Id = 1, Country = "Ukraine", Category = "Food"},
    new Good{Id = 2, Country = "Ukraine", Category = "Food"},
    new Good{Id = 3, Country = "Ukraine", Category = "Food"},
    new Good{Id = 4, Country = "Ukraine", Category = "Food"},
    new Good{Id = 5, Country = "Germany", Category = "Food"},
    new Good{Id = 6, Country = "Germany", Category = "Food"},
    new Good{Id = 7, Country = "Germany", Category = "Food"},
    new Good{Id = 8, Country = "Germany", Category = "Food"},
    new Good{Id = 9, Country = "Greece", Category = "Food"},
    new Good{Id = 10, Country = "Greece", Category = "Food"},
    new Good{Id = 11, Country = "Greece", Category = "Food"},
    new Good{Id = 12, Country = "Italy", Category = "Food"},
    new Good{Id = 13, Country = "Italy", Category = "Food"},
    new Good{Id = 14, Country = "Italy", Category = "Food"},
    new Good{Id = 15, Country = "Slovenia", Category = "Food"}
};

// 店铺价格列表
var storePriceList = new[]
{
    new StorePrice{GoodId = 1, Price = 1.25M, Shop = "shop1"},
    new StorePrice{GoodId = 3, Price = 2.25M, Shop = "shop1"},
    new StorePrice{GoodId = 5, Price = 4.25M, Shop = "shop1"},
    new StorePrice{GoodId = 7, Price = 9.25M, Shop = "shop1"},
    new StorePrice{GoodId = 9, Price = 11.25M, Shop = "shop1"},
    new StorePrice{GoodId = 11, Price = 12.25M, Shop = "shop1"},
    new StorePrice{GoodId = 13, Price = 13.25M, Shop = "shop1"},
    new StorePrice{GoodId = 14, Price = 14.25M, Shop = "shop1"},
    new StorePrice{GoodId = 5, Price = 11.25M, Shop = "shop2"},
    new StorePrice{GoodId = 4, Price = 16.25M, Shop = "shop2"},
    new StorePrice{GoodId = 3, Price = 18.25M, Shop = "shop2"},
    new StorePrice{GoodId = 2, Price = 11.25M, Shop = "shop2"},
    new StorePrice{GoodId = 1, Price = 1.50M, Shop = "shop2"},
    new StorePrice{GoodId = 3, Price = 4.25M, Shop = "shop3"},
    new StorePrice{GoodId = 7, Price = 3.25M, Shop = "shop3"},
    new StorePrice{GoodId = 10, Price = 13.25M, Shop = "shop3"},
    new StorePrice{GoodId = 14, Price = 14.25M, Shop = "shop3"},
    new StorePrice{GoodId = 3, Price = 11.25M, Shop = "shop4"},
    new StorePrice{GoodId = 2, Price = 14.25M, Shop = "shop4"},
    new StorePrice{GoodId = 12, Price = 2.25M, Shop = "shop4"},
    new StorePrice{GoodId = 6, Price = 5.25M, Shop = "shop4"},
    new StorePrice{GoodId = 8, Price = 6.25M, Shop = "shop4"},
    new StorePrice{GoodId = 10, Price = 11.25M, Shop = "shop4"},
    new StorePrice{GoodId = 4, Price = 15.25M, Shop = "shop5"},
    new StorePrice{GoodId = 7, Price = 18.25M, Shop = "shop5"},
    new StorePrice{GoodId = 8, Price = 13.25M, Shop = "shop5"},
    new StorePrice{GoodId = 12, Price = 14.25M, Shop = "shop5"},
    new StorePrice{GoodId = 1, Price = 3.25M, Shop = "shop6"},
    new StorePrice{GoodId = 3, Price = 2.25M, Shop = "shop6"},
    new StorePrice{GoodId = 1, Price = 1.20M, Shop = "shop7"}
};

// 实体类定义
public class Good
{
    public int Id { get; set; }
    public string Country { get; set; }
    public string Category { get; set; }
}

public class StorePrice
{
    public int GoodId { get; set; }
    public decimal Price { get; set; }
    public string Shop { get; set; }
}

public class CountryStat
{
    public string Country { get; set; }
    public decimal MinPrice { get; set; }
    public int StoresNumber { get; set; }
}

预期结果 #

var expected = new[]
{
    new CountryStat{Country = "Germany", MinPrice = 3.25M, StoresNumber = 5},
    new CountryStat{Country = "Greece", MinPrice = 11.25M, StoresNumber = 3},
    new CountryStat{Country = "Italy", MinPrice = 2.25M, StoresNumber = 4},
    new CountryStat{Country = "Slovenia", MinPrice = 0.0M, StoresNumber = 0},
    new CountryStat{Country = "Ukraine", MinPrice = 1.20M, StoresNumber = 7},
};

1. 关联数据：通过GoodId将storePriceList与goodList关联，得到每条价格记录对应的商品原产国
2. 分组统计：
   • 按原产国对关联后的数据分组
   • 对每个国家分组，统计去重的店铺数量，同时筛选该国家所有商品的最低价格
3. 补全无售卖国家：从goodList中提取所有唯一原产国，若某个国家未出现在分组结果中，则补充StoresNumber=0、MinPrice=0的记录
4. 排序输出：按原产国名称对结果排序

使用LINQ可以高效完成上述逻辑：

// 1. 关联店铺价格与商品信息，得到带国家的价格记录
var priceWithCountry = storePriceList
    .Join(goodList, 
          sp => sp.GoodId, 
          g => g.Id, 
          (sp, g) => new { sp.Shop, sp.Price, g.Country });

// 2. 按国家分组，统计店铺数和最低价
var countryStatsWithSales = priceWithCountry
    .GroupBy(pwc => pwc.Country)
    .Select(g => new CountryStat
    {
        Country = g.Key,
        StoresNumber = g.Select(pwc => pwc.Shop).Distinct().Count(),
        MinPrice = g.Min(pwc => pwc.Price)
    });

// 3. 提取所有唯一国家，补全无售卖的国家记录
var allCountries = goodList.Select(g => g.Country).Distinct();
var finalStats = allCountries
    .Select(c => countryStatsWithSales.FirstOrDefault(stat => stat.Country == c) 
                 ?? new CountryStat { Country = c, MinPrice = 0, StoresNumber = 0 })
    .OrderBy(stat => stat.Country)
    .ToArray();

运行上述代码后，finalStats的结果与预期完全一致：

• Germany：店铺数5，最低价3.25M
• Greece：店铺数3，最低价11.25M
• Italy：店铺数4，最低价2.25M
• Slovenia：店铺数0，最低价0.0M
• Ukraine：店铺数7，最低价1.20M

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