Clever Geek Handbook

LINQ and EntityFramework: getting first result after group

I have the following table:

My table

What I am trying to do in LINQ using C # is to group by the Create column (ignoring the time!) And show the count next to each date so that I can grab any top rows I want. I have no problem with this query in sql as below:

SELECT CAST(Created AS DATE) 'Created', COUNT(*) AS 'Count'
FROM Alert
GROUP BY CAST(Created AS DATE)
ORDER BY 'Count' DESC

which is obtained with:

enter image description here

but again, I would like to do this in LINQ, and just all my attempts have failed, can anyone please guide me?

+3


source to share


2 answers


Use the property Date

for DateTime

:

var query = db.Alert
    .GroupBy(a => a.Created.Date)
    .Select(g => new { Created  = g.Key, Count = g.Count() })
    .OrderByDescending(x => x.Count);

It seems that Linq-To-Entities does not support the property DateTime.Date

. Therefore, you must use DbFunctions.TruncateTime

.



Then this should work:

var query = db.Alert
    .GroupBy(a => DbFunctions.TruncateTime(a.Created))
    .Select(g => new { Created  = g.Key, Count = g.Count() })
    .OrderByDescending(x => x.Count);
+3


source


@TimSchmelter is right, but I would like to remind you that in Linq you can do something like:

var multimap = db.Alert.ToLookup(o => o.Created.Date, o => o);


Cardinality is simply a property of a list of values ​​that you get when you use a date as a key.

foreach(date in multimap.Select(g => g.Key).ToList()){
    var itemCount = multimap[date].Count();
+1


source






More articles:


All Articles