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

[AsValeO]

_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

[ajcvickers]

Note for triage: It seems this was also a problem with the old stack, and from a little searching it doesn't seem like there is an obviously better type mapping.

[ajcvickers]

Just found this on the backlog: #770

[rowanmiller]

@divega we want to discuss this with you when you are back

[AsValeO]

EFC 2.0 RC

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

Failed to convert parameter value from a TimeSpan to a DateTime.
Object must implement IConvertible.
Type: InvalidCastException

Well, year has passed, still can't work with TimeSpan. It worked in EFC 1.0 RC1.
I think this is nessesary feature for EFC 2.0.
Or is there at least any workaround here?

[divega]

Clearing up milestone do that we can discuss in triage.

[ajcvickers]

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:
C# 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".

[AndriySvyryd]

Confirming @ajcvickers findings. The second exception is from a query bug, but even if fixed it would result in the first exception anyway.

[AndriySvyryd]

Another workaround is to calculate the deadline on the client:
C# 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.

[smitpatel]

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.
C# 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]

There are multiple issues around this area.

• The second exception comes from query because we generate local parameter of TimeSpan but when creating DbParameter we use inferred typemapping from the other column which is DateTime. And parameter of DateTime cannot be created with value of TimeSpan. Generally this error is not encountered because the types of both nodes of BinaryExpression are same. In this rare case + is overloaded with 2 different types of args. Hence the error.
• If we make query pipeline to handle above issue then we hit the overflow issue as in very first post. So Adding TimeSpan may not always work.
• Even if the value does not cause the overflow, the + operator on SqlServer does not work on data types datetime & time. Hence there is no direct SqlServer translation available for this method. In future we can transform when this method is encountered to something translatable but it would be complex & prone to loss of data.

Closing the issue as the work-around translate to server & also avoid overflow issue. We can address client eval or future translation if there is enough customer feedback.

[smitpatel]

@divega - Can you put appropriate labels for this issue? I am not sure what is correct one.

[divega]

@smitpatel I found myself copying the majority of the contents of the issue. I ended up repurposing it instead. Feel free to improve the title or the first comment.

[Ciantic]

I throw my obvious suggestion here too:

Map it like this: TimeSpan.FromSeconds(VALUE FROM DATABASE) and when storing throw this in the DB TimeSpan.TotalSeconds. it should cover years.

It would not cover microseconds but I think most often TimeSpans need values greater than 24 hours, than precision of microseconds.

And when TimeSpan is just a integer of seconds, the queries are easier to compose I think.

[ajcvickers]

@smitpatel to comment.

[smitpatel]

Filed #18939 for subset of these we can actually translate. Closing this issue as won't fix.