Efcore: Cannot use DateTime.MaxValue null-coalesce in LINQ-TO-EF

[irkush]

I am currently converting an old project from EF 6.X to EF CORE 3.1 and I am experiencing issues which did not occur in EF 6.

When I try a simple null-coalesce on a DateTime-column together with DateTime.MaxValue I get the following exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The issue seems to be that the precision on DATETIME-columns is less than what the query-generator is generating.

It does not seem to affect DATETIME2-columns and even if I decorate the column with:
``` C#
builder.Property(e => e.DateValue).HasColumnType("datetime");

it doesn't work.


This is the SQL generated:
``` SQL
SELECT COALESCE([t].[DateValue], '9999-12-31T23:59:59.999') AS [Date]
      FROM [TestTable] AS [t]

Steps to reproduce

1. Create table and populate

CREATE TABLE dbo.TestTable
(Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DateValue DATETIME
)

INSERT INTO dbo.TestTable
(DateValue)
VALUES
(NULL),
(GetDate())

1. Setup a basic application targeting the table and execute the following code:
   ``` C#
   class Program
   {
   static void Main(string[] args)
   {
   var context = new TestContext();

       var result = context.TestTable.Select(x => new
       {
           Date = x.DateValue ?? DateTime.MaxValue
       });
   
   
       foreach (var re in result)
       {
           Console.WriteLine(re);
       }
   
       Console.Read();
   }
   

   }

   public class TestContext : DbContext
   {
   public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });
   public DbSet TestTable { get; set; }

   protected override void OnConfiguring(DbContextOptionsBuilder options)
   {
       options.UseSqlServer(@"data source=(local)\SQLSERVER;Initial Catalog=Test;Integrated Security=SSPI;");
       options.UseLoggerFactory(MyLoggerFactory);
   
   }
   
   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.ApplyConfiguration(new TestTableConfiguration());
   }
   

   }

   public class TestTableConfiguration : IEntityTypeConfiguration
   {
   public void Configure(EntityTypeBuilder builder)
   {
   builder.HasKey(x => x.Id);

       builder.Property(x => x.DateValue)
           .HasColumnType("datetime");
   }
   

   }

   public class TestTable
   {
   public int Id { get; set; }
   public DateTime? DateValue { get; set; }
   }

### Exception

Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'ConsoleApp1.TestContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.Read()
at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() ClientConnectionId:3abe472a-5d56-4da3-865a-97c1a96e6f0d Error Number:242,State:3,Class:16 Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at M
```

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Framework 4.7.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.4

[irkush]

It seems to generate exception no matter which value I put into the DateTime. So it has nothing to do with MaxValue, only with the fact that EF core generates to much precision on the value sent to the SQL Server.

[irkush]

Further Update: Seems like my configuration of the column did not work as intended and the original error has disappeared. I can't however still not use null-coalesce with DateTime.MaxValue even with the correct configuration. Have updated the issue with the correct error I am getting now.

[ajcvickers]

@irkush In SQL Server, a datetime column can only support values up to '9999-12-31T23:59:59.997'. .NET's DateTime.MaxValue is '9999-12-31T23:59:59.999'. Therefore SQL Server rejects it. Yes, this is silly, but it's been like this forever and I doubt it will change.

If the column is instead datetime2 in the database, and EF maps to this as a datetime2, which is the default for EF Core, then everything should work. datetime2 is recommend over datetime.

An alternative if you must use a datetime column is to use DateTime.Parse("9999-12-31T23:59:59.997") instead of DateTime.MaxValue.

[Allsetra-RB]

I had the same issue when also converting from EF to EF Core. I realize it's a bug in MSSQL, but EF didn't break on it. These kind of things make converting to EF Core that much harder, especially since this bug is hard to detect (I was using a Count and it complains about dates!?). Converting to datetime2 is often not an option, though I agree that this would be better.

I think you should consider clamping used dates, at least if not limited to the max value. You're the one generating the query, so you're the one that needs to take MSSQL syntax into account.

[roji]

@Allsetra-RB I'm not sure exactly what EF6 does, but if you're proposing that EF Core implicitly change 9999-12-31T23:59:59.999 into 9999-12-31T23:59:59.997 without the user's knowledge, that sounds like a bad idea to me. Imagine using EF Core against multiple databases, where the same DateTime value ends up being different in SQL Server and Sqlite... Not good.

IMHO it's better for users to be confronted with this database limitation, and make the appropriate decision for how to deal with it. They can decide to use datetime2, or set up an EF Core value converter to perform this exact conversion themselves (999->997) - doing this explicitly is much better than magic done by EF under the hood.

[Allsetra-RB]

You wouldn't have to change 9999-12-31T23:59:59.999, only change usages of DateTime.MaxValue, which are easy enough to detect when visiting the expression tree. I agree that neither solution is very optimal, a magic translation versus a crash on normal logic. However if you replace usages of DateTime.MaxValue with a correctly matching SQL value is not as magical as changing the actual value.

Would you not steer clear of generating UNION's if you found that SQL Server has a bug in UNION syntax/logic? This particular thing is such a normal thing to do in code (C#) and it's strange that it doesn't yield a good SQL query from the engine specialized in talking to MSSQL.

[roji]

You wouldn't have to change 9999-12-31T23:59:59.999, only change usages of DateTime.MaxValue, which are easy enough to detect when visiting the expression tree

It's certainly easy to do, but DateTime.MaxValue has a clearly-defined value, ending with .999, not .997. Once again, it's quite problematic for the same C# thing (DateTime.MaxValue) to suddenly mean different things across databases.

Would you not steer clear of generating UNION's if you found that SQL Server has a bug in UNION syntax/logic?

If I had another way of translating a C# union operation, which ends up having the same results in the database, then sure - I'd do that. But this isn't a discussion around how to translate something - it's about changing values magically under the hood, without any user opt-in or knowledge.

At the end of the day, not everything that's expressible in C# is translatable to SQL Server. EF6 was certainly more SQL Server-centric than EF Core, which is far more cross-database approach. Note again that it's trivial for users to set up a value converter to do precisely what you're asking for - if that's what they want (and if they insist to not use datetim2 for some reason). The question is whether EF should be changing values as its default behavior - and I don't believe it should.

[Allsetra-RB]

Well there is something to say for the fact that if I use DateTime.MaxValue in a query, I am not trying to use the precise C# value. I am already semantically trying to take a maximum value of a date/time.

Maybe some kind of EF.Values.DateTimeMax would be more elegant (similar to EF.Functions), but it would have to contain some kind of database independent marker value that can be translated into a query. As a point of interest: this would fix DateTime.MinValue as well, which in MSSQL is a weird value like 1753-01-01. And you might have similar issues with decimals and other other types that in the database engine would allow for much more flexibility (decimal(5,2) or in MySql int(5)).

I mean, it wouldn't be weird to go looking for 'extended query compatibility' in EF.Functions or EF.Values or something. And if that EF.Values.DateTimeMax contains some kind of 'object', you can't mistakenly use it in regular C# code. I'm not sure how easy it is to make it comparable to the actual C# DateTime in predicates though...

[roji]

Maybe some kind of EF.Values.DateTimeMax would be more elegant

It's certainly possible, but at that point what's the value over just doing DateTime.Parse("9999-12-31T23:59:59.997"), which is all it is? Note that AFAIK this is also an exclusively SQL Server issue, so there's no need for a "database independent marker value" for the maximum date time. Another problematic point is that the .997 value is only the maximum for SQL Server datetime, but not for datetime2, which can happily contain DateTime.MaxValue - so something like EF.Values.DateTimeMax would be extremely confusing, to say the least.

Of course, it's trivial for people to just have a static readonly variable somewhere which contains the .997 value, and to use it everywhere in their queries; I'm not sure what the extra value would be of EF Core providing it.

[ErikEJ]

How about just using SqlDateTime.MaxValue ??

[roji]

Nice!

[smitpatel]

Well there is something to say for the fact that if I use DateTime.MaxValue in a query, I am not trying to use the precise C# value.

That is true for you. I wouldn't make a conclusion without data that every user of EF Core wants that. Hence as a framework, it is not wise for us to do it by default. Plus, there are so many different ways to do it in user code who wants it that way.

[Allsetra-RB]

Thanks guys, SqlDateTime.MaxValue is what I was looking for I guess.

As a closing note: I checked EF and apparently it generates a datetime2 variable, even when comparing to datetime. I guess that's how they solved the issue there. MSSQL will correctly cast one to the other when comparing.

I would suggest using SqlDateTime to others encountering this issue when converting from EF to EF Core.