使用Group By和Let的LINQ查询

debugcn 发表于 Dev

ASN

我有下表

Name    Consumed
A   Flavour 1
B   Flavour 2
C   Flavour 1
C   Flavour 3
A   Flavour 3
B   Flavour 1
C   Flavour 3
A   Flavour 2
A   Flavour 1
C   Flavour 1
A   Flavour 1
B   Flavour 2

在第一列中，我有这些人的名字，在第二列中，是他们所喜欢的风味。我想生成下表，以风味为列，并找到每个人喜欢的风味的总和。我知道使用SQL的解决方案，但是我正在寻找使用LINQ VB.Net的解决方案。

Name  Flavour 1  Flavour 2  Flavour 3
A     3          1          1
B     1          2          0
C     2          0          2

SQL Query以达到上述结果，

SELECT Name, 
SUM(CASE WHEN Consumed='Flavour 1' THEN 1 ELSE 0) AS Flavour 1,
SUM(CASE WHEN Consumed='Flavour 2' THEN 1 ELSE 0) AS Flavour 2,
SUM(CASE WHEN Consumed='Flavour 3' THEN 1 ELSE 0) AS Flavour 3
GROUP BY Name

下面是LINQ查询，

Dim query = (From row In dtMain.AsEnumerable
             Let Flavour_1 = IIf(row.Field(Of String)("Consumed") = "1", 1, 0)
             Let Flavour_2 = IIf(row.Field(Of String)("Consumed") = "2", 1, 0)
             Let Flavour_3 = IIf(row.Field(Of String)("Consumed") = "3", 1, 0)
             Group row By Code = row.Field(Of String)("Name")
             Into freq = Group Select .Code = Code, .Count = freq.Count()).ToList

我的困难在于如何使用SUM函数汇总临时变量Flavour_1，Flavour_2和Flavour_3并按名称分组。

懒惰

给定以下数据：

Dim table = {
    New With { .Name = "A", .Consumed = "Flavor 1" }, 
    New With { .Name = "B", .Consumed = "Flavor 2" }, 
    New With { .Name = "C", .Consumed = "Flavor 1" }, 
    New With { .Name = "C", .Consumed = "Flavor 3" }, 
    New With { .Name = "A", .Consumed = "Flavor 3" }, 
    New With { .Name = "B", .Consumed = "Flavor 1" }, 
    New With { .Name = "C", .Consumed = "Flavor 3" }, 
    New With { .Name = "A", .Consumed = "Flavor 2" }, 
    New With { .Name = "A", .Consumed = "Flavor 1" }, 
    New With { .Name = "C", .Consumed = "Flavor 1" }, 
    New With { .Name = "A", .Consumed = "Flavor 1" }, 
    New With { .Name = "B", .Consumed = "Flavor 2" }
}

您要查询的查询是：

Dim result = From e In table 
             Group e By Name = e.Name Into g = group
             Select New With 
             { 
                .Name = Name, 
                .Flavour1 = g.Count(Function(x) x.Consumed = "Flavor 1"), 
                .Flavour2 = g.Count(Function(x) x.Consumed = "Flavor 2"), 
                .Flavour3 = g.Count(Function(x) x.Consumed = "Flavor 3") 
             }

在此处输入图片说明