Group by multiple columns in Linq To SQL

Today I was writing a Linq query in which I wanted to applying grouping to query based on more than 1 column and also apply filter on the group more like having clause. After some playing I figured it out. Here is a simple demonstration of grouping by more than 1 column using customer table from NorthWind database.

image

The output of the query is as follows.

CWindowssystem32cmd.exe (2) 

Important point that is worth mentioning in the query is the introduction of the new anonymous type. If you want to group by more than 1 property you have to create an anonymous type and specify the properties in there. Any where clause which is followed after grouping are basically converted to having clause that filter the groups. The query above basically displays groups which have more than 1 contacts for a particular city and contact title.

Published Wednesday, May 7, 2008 1:17 AM by zhirani

Comments

# re: Group by multiple columns in Linq To SQL

Tuesday, March 10, 2009 8:26 AM by Steven

The best explanation of grouping by multiple columns I've seen!!

# re: Group by multiple columns in Linq To SQL

Wednesday, March 11, 2009 9:30 AM by Wesley

This example helped me, exact where I was looking for!

# re: Group by multiple columns in Linq To SQL

Thursday, March 19, 2009 12:05 AM by Huy

Thanks for your example, it helped me to resolve my problem.

# re: Group by multiple columns in Linq To SQL

Tuesday, February 9, 2010 7:12 AM by simonharris

Thank you for posting this. Very clear and to the point explaination.

# re: Group by multiple columns in Linq To SQL

Tuesday, March 9, 2010 1:10 PM by Tom

Very clear thank you.

# re: Group by multiple columns in Linq To SQL

Friday, March 26, 2010 1:33 AM by Nitin

thanks..its very helpful to understand the group by clause

# re: Group by multiple columns in Linq To SQL

Monday, March 29, 2010 9:24 AM by jm

Is there a way to access each item in the groups - you've grouped each row by City and ContactTitle, how do you access the rows in each group?

# re: Group by multiple columns in Linq To SQL

Friday, April 16, 2010 6:22 AM by mian ghous

var result = from outer in

                            (from customerobj in context.customer

                             join regobj in context.register

                             on customerobj.customer_no equals regobj.customer_no

                             join invnobj in context.inventory

                             on regobj.pos_sku equals invnobj.pos_sku

                             join sub_cat in context.sub_category

                             on invnobj.category equals sub_cat.category_id

                             where customerobj.customer_no == as_customerno

                             select new

                             {

                                 altSKU = invnobj.alt_sku,

                                 subcatdescription = sub_cat.description,

                                 compute_0003description = invnobj.product_description == null ? " " : invnobj.product_description + " " + (invnobj.release_no.HasValue ? invnobj.release_no.Value.ToString() : "") + " " +

                                                            (invnobj.description == null ? "" : invnobj.description),

                                 regreceitno = regobj.receipt_no,

                                 qtycompute_0005 = regobj.quantity,

                                 tmpproddescription = invnobj.product_description,

                                 tmpinvdescription = invnobj.description,

                                 tmpinvreleaseno = invnobj.release_no,

                                 tmpsubrelaseno = invnobj.sub_release_no,

                             })

                        group outer by new

                        {

                            grpaltsku = outer.altSKU,

                            grpsubcatdesp = outer.subcatdescription,

                            grpprodescition = outer.tmpproddescription,

                            grpinvdescription = outer.tmpinvdescription,

                            grpinvrelno = outer.tmpinvreleaseno,

                            grpsubrelnob = outer.tmpsubrelaseno,

                            grprectno = outer.regreceitno,

                            grp003grpdesc = outer.compute_0003description,

                        } into g

                        orderby g.Key.grpprodescition ascending, g.Key.grpinvdescription ascending

                        , g.Key.grpinvrelno ascending, g.Key.grpsubrelnob ascending

select new whatever..

{}

# re: Group by multiple columns in Linq To SQL

Wednesday, August 4, 2010 8:13 AM by Maciej

Quick and clear explanation. Best I have found. Thanks!

# re: Group by multiple columns in Linq To SQL

Tuesday, September 21, 2010 5:03 AM by konqi

Thanks mate! Really helped here :-)

# re: Group by multiple columns in Linq To SQL

Thursday, November 25, 2010 10:11 AM by Jimmy

I was doing just about the same in my project but the difference was that I didn't want to return all columns from the database since one is a binary blob representing the binary data from a "physical" file.

Since the table contains many 100k of elements and I only was interested in two different columns I wanted to do something like the following in SQL.

Select Column1, Column2, Count(*)

From Table1

Group by Column1, Column2

You would think that this would be easy to do with NHbibernate but not so. After some playing around I finally gave in and is now using CreateQuery instead which worked from the start.

Any tips on how to do this?

# re: Group by multiple columns in Linq To SQL

Tuesday, March 8, 2011 6:51 AM by AS-CII

Consider using g.Any() instead of g.Count() > 1