Linq Group and Count in IEnumerable

Question

I have the following LINQ Query in my controller which queries my IEnumerable Collection, the grouping works when I output the results in my view but when I try and add a count on the column I have grouped it fails miserably. I was wondering if anyone could help at all, I have been looking at previous examples but I am missing something obvious.

Grouped //Working fine and returning grouped Descriptions

itemdetails = (from c in db.CLIENTDETAILS
                           join l in db.LOCATIONS on c.CLIENTNUMBER equals l.CLIENTNUMBER
                           where c.CLIENTNUMBER == clientNumber
                           join i in db.ITEMDETAILS on l.LOCNUMBER equals i.LOCNUMBER
                           where i.LOCNUMBER == l.LOCNUMBER
                           select i).GroupBy(it => it.DESC).Select(grp => grp.FirstOrDefault()).OrderBy(x => x.DESC)

What I have tried to get Group and Count in LINQ //Not working returning error

itemdetails = (from c in db.CLIENTDETAILS
                           join l in db.LOCATIONS on c.CLIENTNUMBER equals l.CLIENTNUMBER
                           where c.CLIENTNUMBER == clientNumber
                           join i in db.ITEMDETAILS on l.LOCNUMBER equals i.LOCNUMBER
                           where i.LOCNUMBER == l.LOCNUMBER
                           select i).GroupBy(it => it.DESC).Select(grp => new {DESC = grp.key, Count = grp.COUNT()}).OrderBy(x => x.DESC)

This give me the following error :- cannot implicitly convert type system linq iorderedqueryable to system.collections.generic.ienumerable

Thanks for your help as always.

http://stackoverflow.com/questions/7285714/linq-with-groupby-and-count — İsmet Alkan, Jun 26 '13 at 11:35
What is the type of `itemdetails`? This problem probably comes from the fact that it's not compatible with an `IEnumerable` of your anonymous class. — Alxandr, Jun 26 '13 at 11:35
Hi Alxandr, itemdetails is an ienumerable collection in a viewmodel — ullevi83, Jun 26 '13 at 12:22

score 2 · Accepted Answer · answered Jun 26 '13 at 11:34

Your two queries are returning different data, the first is returning items of type ItemDetail, while the second query is returning items of an anonymous type.

If you want an IEnumerable of an anonymous type, you will need to declare it using the var keyword, i.e.

var itemdetails = (from c in db.CLIENTDETAILS
                           join l in db.LOCATIONS on c.CLIENTNUMBER equals l.CLIENTNUMBER
                           where c.CLIENTNUMBER == clientNumber
                           join i in db.ITEMDETAILS on l.LOCNUMBER equals i.LOCNUMBER
                           where i.LOCNUMBER == l.LOCNUMBER
                           select i).GroupBy(it => it.DESC).Select(grp => new {DESC = grp.key, Count = grp.COUNT()}).OrderBy(x => x.DESC)

Thanks Lukazoid, so why does it allow me to group on the column DESC in the first query but when I try and Count on the same column in db.ITEMDETAILS it returns the error? I presume because it does not know what count is in my ienumerable collection itemdetails? — ullevi83, Jun 26 '13 at 12:30
This would be down to the ORM you are using, it seems like it is not able to translate the expression for `.Count()` into a SQL query. — Lukazoid, Jun 26 '13 at 13:01

Linq Group and Count in IEnumerable

1 Answers1