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.

11 Comments

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

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

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

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

  • Very clear thank you.

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

  • 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..
    {}

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

  • Thanks mate! Really helped here :-)

  • 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?

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

Comments have been disabled for this content.