2

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

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

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(*)}

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

Bastien Vandamme
  • 17,659
  • 30
  • 118
  • 200

2 Answers2

5

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()
    }
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
2

You will have to use anonymous type like this:-

Group j By Key = New With { Key .Var1 = j.var1,
                            Key .Var2 = j.var2,
                            Key .Var3 = j.var3 } Into Group
Select New With { .var1 = Key.Var1,
                  .var2 = Key.Var2,
                  .var3 = Key.Var3,
                  .quantity = Group.Sum(Function(x) x.qty),
                  .count = Group.Count()
                }
Rahul Singh
  • 21,585
  • 6
  • 41
  • 56