System.Data.SqlClient.SqlException (0x80131904): The datepart hour is not supported by date function dateadd for data type date.

[doublnt]

Using .NET-Core 2.0 and EF-Core 2.0
When I run this code in ubuntu16.04 in Linux , then log show the error, but it is fine when I use in windows

public async Task<int> GetTodayQuestionCount()
{
    return await Entities
               .Where(q => q.IsActive && q.DateAdded >= DateTime.Now.Date && q.DateAdded < DateTime.Now.Date.AddHours(8))
               .Select(q => q.QID).CountAsync();
}

[smitpatel]

Can you post debug log output for query in both machines? Ideally, EF Core should generate same query. Also are you connecting to same server from both machines?

[ErikEJ]

@doublnt Pls share why this was closed!?

[doublnt]

@ErikEJ Sorry, I forgot.Following: I try to separate the query conditions like that

public async Task<int> GetTodayQuestionCount()
{
    var startTime = DateTime.Now.Date;
    var endTime = startTime.addHours(8);
    return await Entities
               .Where(q => q.IsActive && q.DateAdded >= startTime && q.DateAdded < endTime)
               .Select(q => q.QID).CountAsync();
}

But I didn't test in my production Environment. I will show the result after it is work in my Production Environment.

[doublnt]

@ErikEJ It's work when I use in my production environment

[uladz-zubrycki]

I encounter the same error for EF 3.1.12 with date_value.Date.AddHours(hours_value)

It fails since date_value.Date is converted to sql as CONVERT(date, date_value) and then .AddHours which is DATEADD(hour, date_value, hours_value) obviously fails as resulting expression is DATEADD(hour, CONVERT(date, date_value), hours_value) and you can't add hours to value of type date.

Using this as a workaround, but not sure whether it won't fail with overflow once

date_value
    .AddMilliseconds(-EF.Functions.DateDiffMillisecond(TimeSpan.Zero, date_value.TimeOfDay))
    .AddHours(hours_value)

[uladz-zubrycki]

@ajcvickers Could you please elaborate on why it's not planned? Developers are forced to use quite a hacky and dirty approach to deal with this issue, which isn't that obvious without additional investigation.

[ajcvickers]

@uladz-zubrycki It was closed by the original poster. If you think there is a bug here, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[uladz-zubrycki]

@ajcvickers Indeed. This change confused me

As to the reproduction, original snippet is enough. And here described a reason of such behavior #9566 (comment)

It's applicable for EF 3.1.12, haven't tried that with the latest EF version since project, where I faced this problem, is locked to EF 3.
It's the best I could do here, the rest is up to you.