1

I am trying to query the database using EF Core 3, group DateTime field by Date and Hours and add Hour part to Date on Select. So far all my Linqs fail.

Fail case 1. Add hours to Date using AddHours.

Logs.GroupBy(g => new
{
    g.DateStamp.Date,
    g.DateStamp.Hour,
    g.Result
}).Select(s => new
{
    Date = s.Key.Date.AddHours(s.Key.Hour),
    s.Key.Result,
    Conversions = s.Count(),
    Cost = s.Sum(sum => sum.ConversionCost)
}).OrderBy(o => o.Date)

Error:

The datepart hour is not supported by date function dateadd for data type date.

Not sure exactly where is the problem, AddHours is not supported by Linq to Entity provider or s.Key.Date fields can't contain hours anymore because it becomes Date only field.

Fail case 2. Create new DateTime object and pass variables.

Logs.GroupBy(g => new
{
    g.DateStamp.Date,
    g.DateStamp.Hour,
    g.Result
}).Select(s => new
{
    Date =new DateTime(s.Key.Date.Year, s.Key.Date.Month, s.Key.Date.Day,  s.Key.Hour, 0, 0),   
    s.Key.Result,
    Conversions = s.Count(),
    Cost = s.Sum(sum => sum.ConversionCost)
}).OrderBy(o => o.Date)

Error:

InvalidOperationException: The LINQ expression 'OrderBy<<>f__AnonymousType1, DateTime>( source: Selectf__AnonymousType0, Log>, <>f__AnonymousType1>( source: GroupByf__AnonymousType0, Log>( source: DbSet, keySelector: (l) => new { Date = l.DateStamp.Date, Hour = l.DateStamp.Hour, Result = l.Result }, elementSelector: (l) => l), selector: (e) => new { Date = new DateTime( e.Key.Date.Year, e.Key.Date.Month, e.Key.Date.Day, e.Key.Hour, 0, 0 ), Result = e.Key.Result, Conversions = Count(e), Cost = Sum( source: e, selector: (sum) => sum.ConversionCost) }), keySelector: (e0) => e0.Date)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The OrderBy(o => o.Date) stops working and gives an exception, why? I have no idea either.

So the final question would be, how to group DateTime field from SQL Database using EF Core by Date+Hour and move it to Select into one field and also perform OrderBy by new Date field at the end.

Tomas
  • 17,551
  • 43
  • 152
  • 257
  • Can you specify what is in Logs and what is your objective to have after said OrderBy? – CarlosMorgado Feb 12 '20 at 12:51
  • In general the way is like in your case 1. As you can see, EF Core translates in to SQL. The runtime exception should be from the database, and looks like the underlying database table column type is **date**, which has no time portion. So your query makes no sense for such type of columns (`value.Date == value` and `value.Hour == 0`). – Ivan Stoev Feb 13 '20 at 08:35

2 Answers2

1

I'm able to comment on the fail case 1 as I've also encountered the same issue.

  1. x.Date is converted to sql as CONVERT(date, x)
  2. x.AddHours(y) is converted to sql as DATEADD(hour, x, y)
  3. We have both, so it's DATEADD(hour, CONVERT(date, x), y) and it's not possible, as you can't add hours to data of type date hence the error message.

On the sql level it's usually fixed by adding additional cast to datetime2, sth like

CAST(CONVERT(date, x) AS datetime2)

So you deal on datetime2 level again and able to add hours to the resulting value.

Having said that I dunno how to force EF to generate additional cast (both (DateTime) or Convert.ToDateTime were of no help), so here is a workaround I came up with (can't say whether it could fail with overflow once)

   x.AddMilliseconds(-EF.Functions.DateDiffMillisecond(TimeSpan.Zero, x.TimeOfDay))
    .AddHours(y)

Tested it with EF Core 3.1.12 and it works for me as expected. This is the output

(DATEADD(
    hour, 
    CAST(y AS int), 
    DATEADD(
        millisecond, 
        CAST(
            CAST(
                -DATEDIFF(MILLISECOND, '00:00:00', CAST(x AS time)) AS float) AS int
            ), 
        x
    )
)

Would guess that case 2 fails, as EF is not able to translate new DateTime expression, but it's just error being cryptic.

Uladzislaŭ
  • 1,680
  • 10
  • 13
1

I had the same issue when using EFCore 6, I was getting an error doing a Date.AddHours because it looks like it cast to date instead of datetime. In order to get it to work I had to cast to object and then cast to DATETIME, then I was able to add my hours.

Logs.GroupBy(g => new
{
    g.DateStamp.Date,
    g.DateStamp.Hour,
    g.Result
}).Select(s => new
{
    Date = ((DateTime)(object)s.Key.Date).AddHours(s.Key.Hour),
    s.Key.Result,
    Conversions = s.Count(),
    Cost = s.Sum(sum => sum.ConversionCost)
}).OrderBy(o => o.Date)
Luis Tepepa
  • 31
  • 3