Search code examples
c#entity-frameworkgroup-byigrouping

Group by using the Entity Framework

I receive 48 files per day on a half hourly basis from market. These files have a start time as a property. I am using Entity Framework to view these files in a web application and as the files have a UK time but I am working with a european market the trading day begins the day before at 11pm and so I want to group these together based on the trading day.

In SQL I can accomplish this by:

select cast(DATEADD(hour, 1, START_TIME) as date), count(cast(START_TIME as date)) 
from imbalancecost
group by cast(DATEADD(hour, 1, START_TIME) as date)

I am trying to achieve a similar result in C# using the following attempt:

IEnumerable<IGrouping<DateTime, ImbalanceCost> imbalanceCost = db.ImbalanceCost.GroupBy(ic => ic.START_TIME).ToArray();

Is there any means of first adding the hour onto my grouping and then using only the date part of this new calculated value?

Solution

  • Is there any means of first adding the hour onto my grouping and then using only the date part of this new calculated value?

    In LINQ to Entities (EF6) it's a matter of using respectively the canonical functions DbFunctions.AddHours and DbFunctions.TruncateTime:

    db.ImbalanceCost.GroupBy(ic => 
    DbFunctions.TruncateTime(DbFunctions.AddHours(ic.START_TIME, 1)).Value)

    Note that .Value (or cast to DateTime) is to make the result DateTime rather than DateTime? returned by the canonical method in case ic.START_TIME is not nullable, hence you know the result is not nullable as well.