Query: DateTime.Add* functions throw exception when argument is larger than int range

[george-polevoy]

AddMilliseconds and other functions only allows for 2^31 milliseconds, which is only about 24 days.

In a query, which requires millisecond offsets larger than 24 days, you would need something like:

let offsetDays = (long)offsetMillis / 86400000L
let dt= DbFunctions.AddMilliseconds(DbFunctions.AddDays(end, (int)offsetDays ), (int)(offsetMillis% 86400000L))

[ErikEJ]

Is that not in EF6?

[george-polevoy]

As I understand, DbFunctions type is not part of Entity Framework Core,

EF Core works with DateTime methods directly:
https://github.com/aspnet/EntityFramework/blob/1145ef9b3dd46a07a84cb04b11141ecc8bfc62d2/src/Microsoft.EntityFrameworkCore.SqlServer/Query/ExpressionTranslators/Internal/SqlServerDateAddTranslator.cs

which is fine for the API, since DateTime.AddMilliseconds accepts double as it's value argument, and double is capable to represent reasonable range of milliseconds.

But it's not clear if EF Core works around the 32-bit limitation in SQL Server. In SQL server, DATEADD function works with 32 bit int, not 64 bit bigint, that is a problem.

select dateadd(millisecond, 2147483647, '2017') is maximum what can be achieved.

[roji]

@george-polevoy, is there any reason not to use the other Add overloads (e.g. DateTime.AddYears()) to get around this? Why does the very big value have to be expressed (solely) in milliseconds?

[george-polevoy]

@roji, the reason for me is that code get's complicated with the workaround you suggest, even for very simple things.

This example only works under for the entire interval of 24 days:

var q = from i in ctx.GenerateLongSequence
    let localStart  = start.AddMilliseconds(i.I * step.TotalMilliseconds),
    let localEnd = start.AddMilliseconds( (i.I + 1) * step.TotalMilliseconds)
// ..

You can see that simple thing turns into a non-trivial date time manupulation to apply the workaround:

var q = from i in ctx.GenerateLongSequence
                    where i.I <= n
                    let startOffsetMillis = stepMillis * i.I
                    let startOffsetDays = (int)(startOffsetMillis / 86400000L)
                    let localStart = DbFunctions.AddMilliseconds(DbFunctions.AddDays(start, startOffsetDays), (int)(startOffsetMillis % 86400000L))
                    let endOffsetMillis = stepMillis * (i.I + 1)
                    let endOffsetDays = (int)(endOffsetMillis / 86400000L)
                    let localEnd = DbFunctions.AddMilliseconds(DbFunctions.AddDays(start, endOffsetDays), (int)(endOffsetMillis % 86400000L))
// ..

[smitpatel]

We generate Invalid SQL here when we get value which is larger than int range since we are not doing any validation on the arguments value.

We need to decide if we want to tackle with the value or just do client eval.

[ajcvickers]

We will try switching to client eval conditionally if the number would overflow. If the query must be store translated, then it must be written in a way that can be translated.

[roji]

It's also possible to check whether the number would overflow, and break it down into larger units (e.g. days), in effect doing the non-trivial code sample from this comment. This would allow store evaluation even when the number of milliseconds would overflow 32-bit, but isn't trivial to do.

[divega]

In case it helps, this issue a similar issue was reported for EF6 long time ago: http://entityframework.codeplex.com/workitem/1627.

My answer was:

Canonical functions are supposed to be building block-like functions that work well with our target databases. Given that it is very hard to make the argument for having DiffMillisecondsLong as a canonical function.

But because being able to obtain the total milliseconds diff for any arbitrary pair of dates, as well as being able to produce a long timestamp is a generally good scenario, I did some investigation and was able to come up with the following combination of canonical functions that can work for this:

DbFunctions.DiffDays(a, b) < 24 && DbFunctions.DiffDays(a, b) > -24 ?
(long)DbFunctions.DiffMilliseconds(a, b) :
(long)DbFunctions.DiffDays(a, b) * 86400000 +
(long)DbFunctions.DiffMilliseconds(DbFunctions.AddDays(a, DbFunctions.DiffDays(a, b)), b)

It would be nice if there was an easy way to encapsulate this into a reusable thing, e.g. a model-defined function or just a reusable expression that you could easily refer to in a LINQ query, but that is a different feature.

[smitpatel]

@divega - Should this be bug fix rather than enhancement?