在多个字段SUM和COUNT上使用GROUP BY的Linq查询 [英] Linq query with GROUP BY on multiple fields, SUM and COUNT

问题描述

我正在尝试使用LINQ SQL查询数据库.

I'm trying to use a LINQ SQL to query my database.

假设我有一些看起来像这样的数据:

Let's say I have some data that looks like this:

var1 var2 var3 qty
1    a    1a   50
1    a    1a   25
2    b    2b   10
2    b    2b   15
2    b    2b   10
3    a    3a   25

我知道如何用SQL格式化查询.会是这样的:

I know how to format my query in SQL. It would be something like this:

SELECT var1, var2, var3, count(*) AS count, sum(qty) As quantity
    FROM MyTable
    GROUP BY var1, var2, var3

在这种情况下，输出将是这样的:

In this case the output would be something like this:

var1 var2 var3 Count Qty
1    a    1a   2     75
2    b    2b   3     35
3    a    3a   1     25

我如何在vb.net中使用LINQ做同样的事情

How can I do the same thing with LINQ in vb.net

Dim groups = From j In MyTable
             Group By j.var1, j.var2, j.var3 Into g
             Select new {var1 = g.var1, 
                         var2 = g.var2, 
                         var3 = g.var3, 
                         quantity = sum(g.qty),
                         count = count(*)}

那不是很正确，但是我认为这已经很接近了.我不了解VB.NET中该分组依据的语法.

That's not quite right, but I think it's close. I don't understand the syntax of the group by in VB.NET.

推荐答案

您要按匿名类型分组，这是VB.NET中的语法(请注意Key):

You want to group by an anonymous type, this is the syntax in VB.NET (note the Key):

Dim groups = 
    From j In MyTable
    Group By x = New With {Key .var1 = j.var1, Key .var2 = j.var2, Key .var3 = j.var3} Into g = Group
    Select New With {
        .var1 = x.var1,
        .var2 = x.var2,
        .var3 = x.var3,
        .quantity = g.Sum(Function(r) r.qty),
        .count = g.Count()
    }

这篇关于在多个字段SUM和COUNT上使用GROUP BY的Linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！