3

What i have is the next table:

My Table

What im trying to do in LINQ using C# is to group by the Created column (ignoring the time!) and showing its counts next to each date so i could grab any top rows i want. This query i got no problem doing in sql like the following way:

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

which results with:

enter image description here

but again, i'd like to do that in LINQ and simply all my tryouts failed, could anyone please guide me?

Popokoko
  • 6,413
  • 16
  • 47
  • 58

2 Answers2

3

Use the Date property of 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 DateTime.Date property. So you have to 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);
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thanks Tim but right after i do that im unable to access the result. i tried using "foreach (var alert in query)" right after, am i missing something? – Popokoko Aug 26 '14 at 07:30
  • What means _unable_? Have you tried `foreach(var alert in query)Console.WriteLine("Date:{0} Count:{1}",alert.Created,alert.Count);`? You can also _materialize_ it in a collection with `ToList` or `ToArray`. Or you could initialize a custom class. How many dates are in the table, what does `query.Count()` return? – Tim Schmelter Aug 26 '14 at 07:39
  • yes, that's exactly what i tried. the current exception i get is on the foreach line (not getting into its body at all). Exception: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – Popokoko Aug 26 '14 at 07:43
  • @Popokoko: i'm not that familiar with L2Entities, it seems that the `Date`-property is not supported. Here is the solution: http://stackoverflow.com/a/3186627/284240 – Tim Schmelter Aug 26 '14 at 07:45
  • I tried to tweak your answer with the reference you pointed me and also your updated solution, but unfortunately im getting the same problem while trying to iterate the result (that's weird since after searching around it looks like you pointed me to the right solution) – Popokoko Aug 26 '14 at 07:51
  • @Popokoko: `EntityFunctions` seems to be deprecated, try to use `DbFunctions.TruncateTime` (edited). Again, i'm inexperienced with it. So i also have to use google. Do you get a different error? Is the column nullable? – Tim Schmelter Aug 26 '14 at 07:56
  • just tried, same issue erm, i'm guessing we are missing something (though i really believe that TruncateTime(a.Created) should have worked!) – Popokoko Aug 26 '14 at 07:58
  • OK its working now! Used the following: .GroupBy(a => DbFunctions.TruncateTime(a.Created)) @Tim Schmelte please update your answer, and ill reward you :) – Popokoko Aug 26 '14 at 08:04
  • @Popokoko: _now_ i've corrected it, i had not noticed that i've still used `TruncateTime(a.Created.Date)` instead of `TruncateTime(a.Created)`. – Tim Schmelter Aug 26 '14 at 08:23
1

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

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

The count of items is just property of the value list you get when you use a date as a key.

foreach(date in multimap.Select(g => g.Key).ToList()){
    var itemCount = multimap[date].Count();
Margus
  • 19,694
  • 14
  • 55
  • 103
  • your solution seems very neat but im missing the descending order for the counts – Popokoko Aug 26 '14 at 08:12
  • You can add **.OrderByDescending(o => multimap[o].Count())** to foreach after **ToList()**. – Margus Aug 26 '14 at 08:16
  • @Margus: it also has the same issue as my answer initially, `Linq-To-Entities` doesn't support `DateTime.Date`. – Tim Schmelter Aug 26 '14 at 08:25
  • I actually checked @Margus solution, and it seemed to work, though it feels like its running less fast. I'll thank you both guys! – Popokoko Aug 26 '14 at 08:30