1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jay
  • 3,012
  • 14
  • 48
  • 99

2 Answers2

3

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.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
1

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();
Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
  • Does not recognize the method System.DateTime.AddHours(Double) method, and this cmethod cannot be translated into a stored expression – Jay Apr 09 '18 at 15:32
  • 1
    @Jay Oops, I forgot you are on Entity Framework and not Core, try with my edit – Camilo Terevinto Apr 09 '18 at 15:37
  • ic.Date is now throwing an error that DateTime? does not contain a definition for date – Jay Apr 09 '18 at 15:42