c# - 使用 C# 和 LINQ 对值进行分组

Question

我有一个 LINQ 问题。假设我有一个具有以下值的数据库:

===================
Date,product,orders
-------------------
1/2/2003,apple,3
1/2/2003,orange,5
1/3/2003,orange,6
1/4/2003,grape,2
===================

如何使用 LINQ 获得按日期对项目进行分组并将它们放入如下所示的变量中的结果:

==========================
Date, Apple, Orange, Grape
--------------------------
1/2/2003,3,5,0
1/3/2003,0,6,0
1/3/2003,0 0,2
==========================

任何帮助将不胜感激。谢谢!

Answer 1

这里是我做的测试:

var data = new []{
    new { Date = new DateTime(2003, 1, 2), Product = "apple", Orders = (int?) 3 },
    new { Date = new DateTime(2003, 1, 3),Product =  "orange", Orders = (int?) 5 },
    new { Date = new DateTime(2003, 1, 4), Product = "grape", Orders = (int?) 2 },
    new { Date = new DateTime(2003, 1, 4), Product = "grape", Orders = (int?) null }
};

var result = data.GroupBy(x => x.Date)
                 .Select(g => new {
                   Date = g.Key,
                   Apples = g.Where(x => x.Product == "apple").Sum(x => x.Orders),
                   Oranges = g.Where(x => x.Product == "orange").Sum(x => x.Orders),
                   Grapes = g.Where(x => x.Product == "grape").Sum(x => x.Orders)
                 });

测试数据的结果不包含空值，但苹果、橘子和葡萄的结果为 0。