How to groupBy in linq to sql?

Question

Hi

I have data like this

id = 1<pk>
SDate = 12/12/2009
EDate = 12/12/2009
binding = a_1
userid = 14

id = 2<pk>
SDate = 12/12/2009
EDate = 12/12/2009
binding = a_1
userid = 14

I want to group my data by binding. I am not sure how to do this though. Do I have to make a new select to do this?

so far I have this

 Db.Table.Where(u => u.UserId == userId && u.Binding == binding)
                .GroupBy(u => u.Binding)

So I want to return all the columns. Do I have to go

.select(group = new Table {....});

Answer 1

Remember that Linq's GroupBy isn't like SQL's GroupBy. Linq's GroupBy returns a .Key (which is what your group by condition is), and then an IEnumerable<> of whatever it is you're grouping. So if you wanted a count of all of the rows for each binding alongside the binding itself, it would be:

 var bindingsAndCounts = 
     Db.Table.Where(u => u.UserId == userId && u.Binding == binding)
                .GroupBy(u => u.Binding)
                .Select(g => new {g.Key, BindingCount = g.Count()});

This is a lot more powerful of a construct than with SQL because you can do just about anything with "g" in the expression.

If you want to enumerate through each group you could omit the select:

foreach (var group in whateveryourgroupingExpressionWas) {
   Console.WriteLine(group.Key);
   foreach (var row in group) {
      Console.WriteLine("ID: " + row.Id + "Name " + row.Name ...);
   }
}