LINQ – Group by multiple columns, with join

September 22, 2011

In order to get data to drive a new report I was developing this morning, I needed a Group By query. I have done this before, but really could not remember the syntax, hence this post, for my furture reference, and hopefully helpful to others!

var result = (from tbl1 in YourDayaContext.Messages
                           join tbl2 in YourDayaContext.Table2 on tbl1.id equals tbl2.id
                           join tbl3 in YourDayaContext.Table3 on tbl2.id equals tbl3.id
                           group tbl1 by
                           new
                           {
                               tbl1.tbl1_Col1,
                               tbl1.tbl1_Col2,
                               tbl2.tbl2_Col1,
                               tbl3.tbl3_Col1,
                           } into g
                           where g.Count() > 1
                           select new
                           {
                               g.Key.tbl1_Col1,
                               g.Key.tbl1_Col2,
                               g.Key.tbl2_Col1,
                               g.Key.tbl3_Col1,
                               Quantity = g.Sum(tbl1 => tbl1.tbl1_Col1)
                           });

Probably worth mentioning at this stage that I changed the vae/table/column names in the code above from a working version to protect my database schema security – I have not compiled/tested the above.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: