Efcore: System.InvalidOperationException: 'An exception occurred while reading a database value. The expected type was 'System.TimeSpan' but the actual value was of type 'System.DateTime'.'

[RoshanJeewantha]

The following Linq query generates the titled exception.

from d in ds 
select (d.ExpiryDate.Value - currentDate).Days

ds is a DbSet.
d.ExpiryDate is type of DateTime?.
currentDate is type of DateTime.

If we use (d.ExpiryDate.Value.Subtract(currentDate)).Days, then we don't get the exception.

Please refer to the Steps to reproduce for complete source code.

Not sure, whether this is related to https://github.com/aspnet/EntityFrameworkCore/issues/13025

Exception message:

System.InvalidOperationException: 'An exception occurred while reading a database value. The expected type was 'System.TimeSpan' but the actual value was of type 'System.DateTime'.'

Stack trace:

   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at TestHostForCastException.Program.Main(String[] args) in C:\EFCoreTestBed\Program.cs:line 24

Inner Exception 1:
InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.String'.

Steps to reproduce

Repro included at https://github.com/avinash-phaniraj-readify/EFCoreTestBed/tree/CastErrorInProjectionWithDateTime

Further technical details

EF Core version: 2.1
Database Provider: EntityFrameworkCore.SqlServerCompact35
Operating system: Windows 10.0.17134
IDE: Visual Studio 2017 15.8.0

[ajcvickers]

@smitpatel to communicate to @ErikEJ

[smitpatel]

@ErikEJ - You need to do something similar to https://github.com/aspnet/EntityFrameworkCore/pull/11110 for SqlCE

[ErikEJ]

Interesting - my last comment on the original issue was an unanswered question: SQL CE fyi?

[smitpatel]

@ErikEJ - Sorry. Missed it. In future, I will be extra careful to tag you whenever something changes in SqlServer since it is very similar to SqlCe.

[ajcvickers]

@smitpatel Can you please also add this to the provider-changes log.

[smitpatel]

@ajcvickers - Are we adding 2.1 provider changes?

[ajcvickers]

@smitpatel Anything post 2.1.0.0 RTM. But I take you point--I didn't realize we shipped this before RTM.

[RoshanJeewantha]

Thank you.

[ErikEJ]

Thanks for the heads up @smitpatel - I should have paid better attention.

I will be extra careful to tag you whenever something changes in SqlServer since it is very similar to SqlCe.

Yes, somehow SQL CE at the core must share some code with SQL Server..

An updated 3.5 package with the fix has just been released to NuGet: https://www.nuget.org/packages/EntityFrameworkCore.SqlServerCompact35/2.1.0.3

[ajcvickers]

Closing here, as the required change was in the Compact provider.