LINQ to Entities group-by failure using .date

17

I am trying to do a Linq group by on just the date part of a datetime field.

This linq statement works but it groups by the date and the time.

var myQuery = from p in dbContext.Trends
          group p by p.UpdateDateTime into g
          select new { k = g.Key, ud = g.Max(p => p.Amount) };

When I run this statement to group by just the date I get the following error

var myQuery = from p in dbContext.Trends
          group p by p.UpdateDateTime.Date into g   //Added .Date on this line
          select new { k = g.Key, ud = g.Max(p => p.Amount) };

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

How can I do a group by for the date and not the date and time?

twamn

출처 c# linq linq-to-entities entity-framework-4 group-by
  • That is weird because the same exact code works for Linq to SQL! - azamsharp

3 답변

32

Use the EntityFunctions.TruncateTime method:

var myQuery = from p in dbContext.Trends
          group p by EntityFunctions.TruncateTime(p.UpdateDateTime) into g
          select new { k = g.Key, ud = g.Max(p => p.Amount) };

  • This works. It is a little cleaner than the other solution which also works. Thanks! - twamn
  • It works in all cases except when different timezones are used. Then the grouping is also done in timezones. Ive posted a bad solution here and would like som help to make the code cleaner: .NET LINQ to entities group by date (day) - Zeezer
  • Thanks. I was looking for this for days. You're the man - Dudipoli
5

Possible solution here which follows the pattern:

var q = from i in ABD.Listitem
    let dt = p.EffectiveDate
    group i by new { y = dt.Year, m = dt.Month, d = dt.Day} into g
    select g;

So, for your query [untested]:

var myQuery = from p in dbContext.Trends
      let updateDate = p.UpdateDateTime
      group p by new { y = updateDate.Year, m = updateDate.Month, d = updateDate.Day} into g
      select new { k = g.Key, ud = g.Max(p => p.Amount) };

  • This works with one small mod. "into g" must be added to the end of the third line. Thanks! - twamn
  • But I would say adrift's solution is the better one if you're using EF 4.0 .. I wasn't previously aware of the EntityFunctions utils.. - Pero P.
0

You can't use DateTime.Date in an Linq-to-Entities query. You either have group by the fields explicitly or create a Date field in the database. (I had the same problem - I used a Date field in the db, never looked back).

Linked

Related

Latest