CODEWITHSUNDEEP
c#entity-frameworkgroup-byigrouping
Jay
Jay

Reputation: 3082

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?

Upvotes: 1

Views: 95

Answers (2)

Camilo Terevinto
Camilo Terevinto

Reputation: 32058

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();

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205539

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.

Upvotes: 3

Related Questions