Monday, 28 April 2008

LINQ Group By Syntax for Grouping on Multiple Columns (another peculiarity of syntax in LINQ)


It is an oversight by the folks at Microsoft that LINQ does not have a built-in group by operator that supports multiple group by columns. For example, I found it unusual that LINQ does not support multiple grouping statements like so:



from f in FundUserRoles
group f by f.RoleId, f.UserName into myGroup
where myGroup.Count() > 0
select new {myGroup.Key, ProductCount = myGroup.Count()}




If you try to run this query, you will get a syntax error on the second line. You cannot just add items to the group by clause like this. Instead, to get this group by working, you must use anonymous types and do the following:

from f in FundUserRoles
group f by new {f.RoleId, f.UserName}
into myGroup
where myGroup.Count() > 0
select new { myGroup.Key.RoleId, myGroup.Key.UserName, FundCount = myGroup.Count()}


This query now works in LINQPad.


41 comments:

Abram said...

Thanks man! I've been stuck on that for hours. You're a lifesaver!

CodeMonkey said...

Exactly what I needed. Thanks!

Ryan said...

Hey, thanks a lot man! This is what I have been looking for for days!

I was on another road, I tired to use the nested group as I learned this from 101linq samples. Then I was stuck with how to display this nested dataset.

Been trying find a way to display this nested (nasty) dataset for days! and I am still stuck here.

Ryan

Fakher Halim said...

Thanks for the workaround, it is cool!
But I think the main reason why Microsoft just cannot do it as comma separated list is because of the fact that every LINQ expression is eventually transformed into lambda functions/ argument before execution. Therefore, even what appears as a list of arguments has to be merged as a single anonymous type (e.g. the way we do with select all the time new {a, b, c, d}), as the LINQ lambda functions are typically not designed to be expecting a variable number of arguments, but just one object of any (named, or anonymous) type.

Anonymous said...

Really great thanks!!

Adam Tuliper said...

Good post.
This gets tricky when you are trying to group on items without property names, such as arrays...string[], etc. not sure yet if you can even do that. I know the basic syntax
new {someArray[3],someArray[4]}
in a groupby stmt won't work but you can group on a single one of those items just ok without the new {} stmt.
I'd be curious if anyone has gotten groupby to work with multiple unnamed items like this. There was a trick to do an anonymous method by assigning it to a variable and then using those variables to create it ok and thus avoid a compiler error, but I dont think it would work in this case.. hmmmmmmmmmmmmmmm

Anonymous said...

Great! Thank you very much!!
Marcone

Anonymous said...

You sir, are a star!

I have searched through books, complicated grouping queries and godknows what esle trying to find a simple answer.

Well done!
works a treat.

Jay Lindsay said...

Beautiful little girl!

How would you write this query using extension methods?

Jay

Eder said...

Oh Yes! This is the solution for a big trouble here!
Thanks

james said...

Sweet ... thx

Andy said...

Note: vb.net requires the "Key" keyword specified for the anonymous type

http://stackoverflow.com/questions/626935/why-group-by-key-of-anonymous-objects-does-not-behave-the-way-expected

Anonymous said...

This was a life saver. Thanks a bunch!!!

Thiago Marotta said...

Thank you for this post!
I've been stuck on that for long time... It's exactly what I needed!

Anonymous said...

Blame it on the C# team for messing this one up. The VB team did it right where you can just comma delimit your group by. Also, why do they require you to have a select on every query? VB doesn't, it just projects everything unless you specify, and in cases where you just want to do a .Count() or .Any() there is no need to select anything. That was dumb

Raj said...

thanks alot :-)

JeffR said...

Very nice David - thanks.

I still have a question for anyone who can answer this, though.

This query works in LINQPad, but I can't seem to get it to work in C# because the generics list I am putting it into is a list of a certain type and I can't do the conversion from the anonymous type:

IQueryable[Address] query = from u in myList group u by new { u.Country, u.City } into g select new { g.Key.Country, g.Key.City };

I get an error under the 'select' word - it says:

Cannot implicitly convert System.Linq.IQueryable[AnonymousType#1] System.Linq.IQuerable[Address].

(note, where you see [ and ], please replace with < and >.. couldn't get them past the posting validation of this page)

Any ideas?

Jeff

JeffR said...

sorry that was

Cannot implicitly convert System.Linq.IQueryable[AnonymousType#1]

to

System.Linq.IQuerable[Address].

batool said...

Thaaaaaaaaaaaaaaaaanks...
You made my day!!!!

David Klein said...

Related post here - how to detect duplicates in a list with LINQ
http://ddkonline.blogspot.com/2010/04/finding-duplicates-in-list-using-linq.html

Thiago Temple said...

Thanks a lot....
It saved the day.

MEHMET said...

Thanks a lot.. best regards :))

Darth Continent said...

Thanks for this, most helpful.

Хусан said...

Thanks a lot...

luke said...

Absolute genius, thank you!

Tola Anjorin said...

You rock!!!

Please how did you configure LinQPad to query sharepoint.

Thanks again. You saved me!

akkbright said...

Thanx for saving my coding hours. In fact, it works! Just a small modification for DataTable:

from row in myDataTable.AsEnumerable()
group row by new { g = row.Field<string>(sGroupColumnName1), h = row.Field<string>(sGroupColumnName2) }
into Group
select new
{
G1= Group.Key.g,
G2= Group.Key.h,
MAXVALUE = Group.Max(row => Decimal.Parse(row[sMaxColumnName].ToString()))
};

Michael said...

That was awesome, you saved me a lot of trouble. thanks!

Michael said...

That was awesome, you saved me a lot of trouble. thanks!

Michael said...

That was awesome, you saved me a lot of trouble. thanks!

Bhaskar said...

Thanks for the post.This is exactly what i need

Angelica Gomez said...

It still doesn't work for me. :(
It has this error on the second line:


Cannot convert lambda expression to type 'System.Collections.Generic.List' because it is not a delegate type

José said...

THANKS a LOT!

José

Mary Brown said...

Fantastic Article on Multi column grouping using LINQ

Mary Brown said...

Fantastic Article on Multi column grouping using LINQ

Dot Net Training in CHennai

Martina Christy said...

It was really a wonderful article and I was really impressed by reading this blog. We are giving all software Course Online Training. The HTML5 Courses in Chennai is one of the reputed Training institute in Chennai. They give professional and real time training for all students.

mathew delport said...

SEO Training in Chennai

Thanks for sharing this information. SEO is one of the digital marketing techniques which is used to increase website traffic and organic search results. If anyone wants to get SEO Course in Chennai visit FITA Academy located at Chennai. Rated as No.1 SEO Training institute in Chennai.

SEO Training in Chennai | SEO Training Institute in Chennai


hari said...

Thanks for sharing such informative article on Load runner Automation testing tool. This load testing tool will provide most precise information about the quality of software.
SharePoint Course in Chennai | SharePoint Course

PLC Training In Chennai said...

PLC Training in Chennai
PLC Training Institute in Chennai
PLC Training Center in Chennai
PLC Training Course in Chennai
PLC Course in Chennai
PLC SCADA Training in Chennai
PLC Training Centre in Chennai
PLC SCADA Training Institute in Chennai
Best PLC Training in Chennai
Best PLC Training Institute in Chennai
SCADA Training in Chennai
DCS Training in Chennai
Automation Training in Chennai
Automation Training Institute in Chennai
Industrial Automation Training in Chennai
HMI Training in Chennai
VFD Training in Chennai
Process Automation Training in Chennai
Final Year Projects in Chennai
IEEE Projects in Chennai
Mechanical Projects in Chennai
Embedded Training in Chennai
VLSI Training in Chennai
Robotics Training in Chennai
Inplant Training in Chennai
AutoCad Training in Chennai
IT Training institutes in Chennai
Web Designing Training in chennai

Iqbal Dlk said...

very nice and informative blog
dot net training in chennai
java training in chennai

1croreprojects said...



Thanks for sharing this valuable information.
java projects in chennai
dotnet projects in chennai
ns2 projects in chennai