efcore: Query: TimeSpan with DateTime arithmetic operations are not supported

Edit by @divega:

Fixing this issue is not necessarily about fixing the overflow or type inference problems we hit immediately with the different expressions in this bug but about finding translations of those expressions that actually work. Likely when we find expressions like this we could transform at least some of them into a combination of DateTime.Add and DateTime.Substract, client evaluation, etc., that don’t even require creating parameters of type TimeSpan.

Original customer report

The issue

After updating from RC1 this issue with TimeSpan and SqlDbType.Time appeared. Building.AddDate type is datetime in Azure SQL DB, DateTime in generated entity class.

using (var db = new MyContext())
            {
                var bs = await db.Building.AsNoTracking()
                    .Where(
                        x =>
                            DateTime.Now - x.AddDate > TimeSpan.FromDays(30)).ToListAsync();
            }

Exception message: SqlDbType.Time overflow. Value ‘30.00:00:00’ is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

Further technical details

DB: Azure SQL DB EF Core version: 1.0.0 Operating system: Windows Server 2012 Visual Studio version: 2015U3

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 15 (13 by maintainers)

Most upvoted comments

Another workaround is to calculate the deadline on the client:

var deadline = DateTime.Now + TimeSpan.FromDays(3);
q = q.Where(x => x.Added >= deadline);

But both of these workarounds suffer from DateTime.Now being evaluated on the client instead of the server, which might lead to different results.

AndriySvyryd on Jun 21, 2017

Since + is overloaded in DateTime which uses internal code to computation the new DateTime & there is no direct translation available in SqlServer. (+ is not defined for datetime & time value).

The best work around to achieve this in SqlServer would be to use DateTime.Add* functions instead of TimeSpan. The query in first post can be re-written as follows to give same result.

var bs = await db.Building.AsNoTracking()
    .Where(
        x => DateTime.Now > x.AddDate.AddDays(30)).ToListAsync();

If you are using different TimeSpan.From* function then find appropriate match in DateTime.Add* for that. EF Core translates DateTime.Add* functions to server.

It will also avoid issue of overflow.

smitpatel on Jun 26, 2017

Notes for triage: Looks like a couple of different things are happening here. In original bug, TimeSpan was being converted to Time, but the value overflowed. This is somewhat expected since the mapping for TimeSpan is to Time, but Time can easily overflow. This is discussed in #242 for mapped properties.

The new exception is different–it indicates that query is attempting to create a DateTime parameter but using a TimeSpan object. SqlClient cannot handle this and so throws. I suspect this is happening due to type inference in the query pipeline, but I haven’t verified this.

So, fundamentally, this issue is not about the general mapping of TimeSpan, which is covered by #242, but instead is about what query should do when translating this either in terms of creating parameters of the “correct” type (which can overflow) or doing some more complex translation which avoids this.

@AsValeO As a workaround, you can force client evaluation of the query:

q = q.ToList().Where(x => x.Added + TimeSpan.FromDays(3) >= DateTime.Now);

Before RC1 client evaluation was likely happening all the time, which is why it appeared to be “working”.

ajcvickers on Jun 19, 2017

Just found this on the backlog: #770

ajcvickers on Jul 8, 2016