Group by using the Entity Framework

Question

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?

Answer 1

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.

Answer 2

If I understand you correctly, you want to add an hour to the start time and group by the date part. That can be done as follows:

var imbalanceCost = db.ImbalanceCost
    .Select(x => EntityFunctions.AddHours(x.START_TIME, 1))
    .GroupBy(ic => ic.Value.Date)
    .ToArray();