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.


42 comments:

Unknown 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

Unknown 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

rajravat said...

thanks alot :-)

Unknown 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

Unknown 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.

Unknown 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!

Bhaskara said...

Thanks for the post.This is exactly what i need

Unknown 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

Anonymous 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.

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

Unknown said...

Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
Selenium training in Chennai

Selenium training in Bangalore

tamizh said...

I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described. I hope to read more and more interesting articles from your blog. All the best.
Selenium training in Chennai
Selenium training in Bangalore
Selenium training in Pune
Selenium Online training

nash b said...

Nice...
t system placement paper

advantages of package in java

2xwy cable full form

react native developer resume sample

log(a2/bc) + log(b2/ac(c2/ab) is

error: cannot find module '../lib/utils/unsupported.js'

django.core.exceptions.improperlyconfigured: error loading mysqldb module.

infix to postfix python

what is your biggest achievement yahoo answers

toughest pattern programs in c



nash b said...

Good...
ece internship

internships for cse students

internships in chennai for cse students

internships in chennai for cse students 2019

internship for ece

internship in chennai for mechanical

10 days internship

mechanical internship certificate

internship for aerospace engineering students

winter internship 2019 for cse students

vcube said...

Excellent article. This was a very interesting essay to read.I'd want to thank you for the time and attention you put into creating this excellent post. This was a very interesting essay to read.I'd want to thank you for the time and attention you put into creating this excellent post.

Python training in Hyderabad