ef6: Fix Entity Framework so it does not "assume" the SQL datatype is DateTime2 when using SQL 2008 and greater

Similar posts here but it is marked as “closed / won’t fix”. https://github.com/aspnet/EntityFramework6/issues/49 https://github.com/aspnet/EntityFramework6/issues/325

Can this be looked at again since this is a “breaking” change in SQL 2016?

There is also another problem with code generated from EF since it creates “DateTime” columns for tables yet compares as “DateTime2” data types.

I also faced with the issue and spent a lot of time on it. For fix that on application data access layer you can use command interceptor:

    /// <summary>
    /// DateTimeInterceptor fixes the incorrect behavior of Entity Framework library when for datetime columns it's generating datetime2(7) parameters 
    /// when using SQL Server 2016 and greater.
    /// Because of that, there were date comparison issues.
    /// Links:
    /// https://github.com/aspnet/EntityFramework6/issues/49
    /// https://github.com/aspnet/EntityFramework6/issues/578
    /// Notes:
    /// Disable it if:
    /// 1) Database DateTime types will be migrating to DateTime2
    /// 2) Entity Framework team will fix the problem in a future version
    /// </summary>
    public class DateTimeInterceptor : IDbCommandInterceptor
    {
        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            ChangeDateTime2ToDateTime(command);
        }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private static void ChangeDateTime2ToDateTime(DbCommand command)
        {
            command.Parameters
                .OfType<SqlParameter>()
                .Where(p => p.SqlDbType == SqlDbType.DateTime2)
                .Where(p => p.Value != DBNull.Value)
                .Where(p => p.Value is DateTime)
                .Where(p => p.Value as DateTime? != DateTime.MinValue)
                .ToList()
                .ForEach(p => p.SqlDbType = SqlDbType.DateTime);
        }
    }

Don’t use it if your database has DateTime2 columns too.

Hope this will help someone 😃

The problem is, that EF6 does not work consistantly with datetime / datetime2(7) fields. Ok, we have datetime in database, that one is true. But if EF6 writes (SaveChanges) as datetime2 it should also read the column as datetime2. If EF6 would be consistant, everything would be ok…

Examlpe: exec sp_executesql N'UPDATE [dbo].[setup] SET [modified] = @0, [text] = @1 WHERE (([mykey] = @2) AND ([modified] = @3)) ',N'@0 datetime2(7),@1 varchar(max) ,@2 char(2),@3 datetime2(7)' ,@0='2021-06-09 12:54:46.7531175',@1='20000',@2='02',@3='2021-06-09 11:30:33.1670000'

Parameter @3 is wrong.

select modified, convert(datetime2(7),modified) as dt2 from dbo.setup where mykey='02'

datetime column in DB = 2021-06-09 11:30:33.167, converted to datetime2(7) in DB = 2021-06-09 11:30:33.1666667

in debug I can see that the DateTime object(modified) in Class (setup) has property TimeOfDay = 2021-06-09 11:30:33.1670000. So… If EF6 asumes all DateTime objects(columns) are of type “datetime2” it should also read the column as such. There is no way (as far as I know there is no way how to intercept SaveChanges and adjust value or column (ie.: convert(datetime2(7),modified = @3 ). The only solution is to change compatibility level down to 120 or to change the datetime to datetime2 in database. The second one is dangerous if we other code where we have datetime column (temporary tables, stored procedures etc…)

@adolfo1981

1. While it would be nice to default to DateTime, I think that by default the EF team will need to use DateTime2 as the default attribute decorate when nothing is supplied since that is already in production and all code base. The developers using EF will need to apply the DateTime attribute when the don’t want it to default to DateTime2.

2. I think you miss-understood by comment. I was saying that if you are not using DateTime2 in SQL you can download the source code from github, change all the places I mentioned at the top of the post from datetime2 to datetime, recompile, and it will work with SQL 2016.

3. I have no fix, only a suggestion to the EF team.

Currently I recompiled the code base for DateTime and am using it in our production system with SQL 2016. We have developers at our company actively working on converting our code from EF to EF Core since it does not have this problem.

@ImGonaRot I’ll take another look.

@ajcvickers Sure it could. Any place where it switches to DateTime2, check the .NET property attribute and (If and Only If) the user added “TypeName=DateTime” then let EF use DateTime rather than DateTime2.

Ex.

if (typeKind == PrimitiveTypeKind.DateTime)
    {
        // Check the "TypeName" attribute for DateTime
        // property.GetCustomAttributes(typeof(Column))... more code here
        if (typeNameAttribute == "DateTime")
        {
            typeName = "datetime";
        }
        else
        {
            typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
        }
    }

I would suggest caching the attributes so that we don’t have to use reflection every time since it is slow.

@dgenezini No. The attribute would be on the DbContext type, not the individual properties. In the interceptor we have no information about what entity property the command parameters are bound to, so the we can’t configure on a property-by-property basis. If we could, that would be a better solution.

So you had to decorate all your applicable DateTime properties with the ChangeDateTime2ToDateTimeAttribute? Oh or you put that attribute at the DbContext level instead?

I’m decorating the DbContexts, in my case all DateTime properties of the database will be DATETIME.

@dgenezini Did you try the Interceptor solution from @stasones ? That sounds promising to me and worked in my prototype.

@adolfo1981 , yes, just made a little change to check for a Attribute so I can have both scenarios simultaneously.

    public class DateTimeInterceptor : IDbCommandInterceptor
    {
        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            var DbContext = interceptionContext.DbContexts.FirstOrDefault();

            if ((DbContext != null) &&
                (Attribute.IsDefined(DbContext.GetType(), typeof(ChangeDateTime2ToDateTimeAttribute))))
            {
                ChangeDateTime2ToDateTime(command);
            }
        }

        ...
    }

Still, I believe that the [Column(TypeName =“datetime”)] attribute should be considered by entity framework.

Just ran into this issue by updating the compatibility of SQLServer to 130. How is there not a fix with entity framework and this yet?

Following @stasones lead, I have a more sophisticated version that asks SQL Server to suggest the parameter type mapping, and overrides EFs type mapping where the correct parameter type should be DateTime, DateTime2, or Date.

It uses the sp_describe_undeclared_parameters to get the parameter types, and assembles a cache of parameter type recommendations to reuse for subsequent executions of the same query text.

Listing Follows:

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.SqlClient;
using System.Linq;

namespace ef6DatetimeWorkaround
{
    class Foo
    {
        public int Id { get; set; }

        [Column(TypeName = "datetime")]
        public DateTime? DateTimeColumn { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime? DateTime2Column { get; set; }

        [Column(TypeName = "date")]
        public DateTime? DateColumn { get; set; }

    }

    public class DbConfig : DbConfiguration
    {
        public DbConfig()
        {
            this.SetDatabaseInitializer(new DropCreateDatabaseAlways<Db>());
            this.AddInterceptor(new DateTimeParameterFixer());
        }
    }

    [DbConfigurationType(typeof(DbConfig))]
    class Db : DbContext
    {
        public DbSet<Foo> Foos { get; set; }
    }
    class Program
    {

        static void Main(string[] args)
        {
            DateTime? now = DateTime.Now;

            using (var db = new Db())
            {
                db.Database.Initialize(true);
                var f = new Foo()
                {
                    DateColumn = now,
                    DateTime2Column = now,
                    DateTimeColumn = now
                };
                db.Foos.Add(f);
                db.SaveChanges();
            }
            using (var db = new Db())
            {
                db.Configuration.UseDatabaseNullSemantics = false;
                db.Database.Log = s => Console.WriteLine(s);

                var q = from f in db.Foos
                        where f.DateColumn == now
                        where f.DateTime2Column == now
                        where f.DateColumn == now
                        select f;

                var results = q.Single();

                Console.WriteLine("Success");


            }

        }
    }


    public class DateTimeParameterFixer : IDbCommandInterceptor
    {
        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            var dbContexts = interceptionContext.DbContexts.ToList();

            if (dbContexts.Count == 1)
            {
                FixDatetimeParameters(dbContexts[0], command);
            }

        }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private class SuggestedParameterType
        {
            public int parameter_ordinal { get; set; }
            public string name { get; set; }
            public int suggested_system_type_id { get; set; }
            public string suggested_system_type_name { get; set; }
            public Int16 suggested_max_length { get; set; }
            public byte suggested_precision { get; set; }
            public byte suggested_scale { get; set; }

        }

        private static ConcurrentDictionary<string, List<SuggestedParameterType>> batchCache = new ConcurrentDictionary<string, List<SuggestedParameterType>>();

        enum SqlTypeId
        {
            date = 40,
            datetime = 61,
            datetime2 = 42,
        }

        private static List<SuggestedParameterType> GetSuggestedParameterTypes(DbContext db, string batch, int parameterCount)
        {
            if (parameterCount == 0)
            {
                return new List<SuggestedParameterType>();
            }
            var con = (SqlConnection)db.Database.Connection;
            var conState = con.State;

            if (conState != ConnectionState.Open)
            {
                db.Database.Connection.Open();
            }
            var results = batchCache.GetOrAdd(batch, (sqlBatch) =>
            {
                var pBatch = new SqlParameter("@batch", SqlDbType.NVarChar, -1);
                pBatch.Value = batch;

                var rd = new List<SuggestedParameterType>();
                var cmd = new SqlCommand("exec sp_describe_undeclared_parameters @batch; ", con);
                cmd.Transaction = (SqlTransaction)db.Database.CurrentTransaction?.UnderlyingTransaction;

                cmd.Parameters.Add(pBatch);

                //sp_describe_undeclared_parameters does not support batches that contain multiple instances of the same parameter
                //
                //to workaround a common cause loop and transform on error expressions like:
                //
                // WHERE ([Extent1].[Date_Modified] = @p__linq__0) OR (([Extent1].[Date_Modified] IS NULL) AND (@p__linq__0 IS NULL))'
                //into
                // WHERE ([Extent1].[Date_Modified] = @p__linq__0) OR (([Extent1].[Date_Modified] IS NULL) AND (1=1))'
                // 
                // this works because the @param is null expression is irrelevant to the parameter type discovery.
                while (true)
                {
                    try
                    {
                        using (var rdr = cmd.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                var sp = new SuggestedParameterType()
                                {
                                    //https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-undeclared-parameters-transact-sql
                                    parameter_ordinal = rdr.GetInt32(0),
                                    name = rdr.GetString(1),
                                    suggested_system_type_id = rdr.GetInt32(2),
                                    suggested_system_type_name = rdr.GetString(3),
                                    suggested_max_length = rdr.GetInt16(4),
                                    suggested_precision = rdr.GetByte(5),
                                    suggested_scale = rdr.GetByte(6),
                                };


                                if (sp.suggested_system_type_id == (int)SqlTypeId.date || sp.suggested_system_type_id == (int)SqlTypeId.datetime2 || sp.suggested_system_type_id == (int)SqlTypeId.datetime)
                                {
                                    if (!sp.name.EndsWith("IgNoRe"))
                                    {
                                      rd.Add(sp);
                                    }
                                }
                            }
                            break;
                        }
                    }
                    catch (SqlException ex) when (ex.Errors[0].Number == 11508)
                    {
                        //Msg 11508, Level 16, State 1, Line 14
                        //The undeclared parameter '@p__linq__0' is used more than once in the batch being analyzed.
                        var paramName = System.Text.RegularExpressions.Regex.Match(ex.Errors[0].Message, "The undeclared parameter '(?<paramName>.*)' is used more than once in the batch being analyzed.").Groups["paramName"].Value;

                        string sql = (string)pBatch.Value;
                        if (sql.Contains($"{paramName} IS NULL"))
                        {
                            sql = sql.Replace($"{paramName} IS NULL", "1=1");
                            pBatch.Value = sql;
                            continue;
                        }
                        else
                        {
                            throw;
                        }
                       
                    }
                
                }
                return rd;


            });

            if (conState == ConnectionState.Closed)
            {
                con.Close();
            }
            return results;

        }

        private static void FixDatetimeParameters(DbContext db, DbCommand command)
        {
            if (!command.Parameters.OfType<SqlParameter>().Any(p => p.SqlDbType == SqlDbType.DateTime2 || p.SqlDbType == SqlDbType.DateTime))
            {
                return;
            }
            var suggestions = GetSuggestedParameterTypes(db, command.CommandText, command.Parameters.Count);

            if (suggestions.Count == 0)
            {
                return;
            }

            Dictionary<string, SqlParameter> paramLookup = new Dictionary<string, SqlParameter>();
            foreach (var param in command.Parameters.OfType<SqlParameter>())
            {
                if (param.ParameterName[0] == '@')
                {
                    paramLookup.Add(param.ParameterName, param);
                }
                else
                {
                    paramLookup.Add("@" + param.ParameterName, param);
                }
            }
            foreach (var suggestion in suggestions)
            {
                var param = paramLookup[suggestion.name];

                if (suggestion.suggested_system_type_id == (int)SqlTypeId.datetime2)
                {
                    param.SqlDbType = SqlDbType.DateTime2;
                    param.Scale = suggestion.suggested_scale;
                }
                else if (suggestion.suggested_system_type_id == (int)SqlTypeId.datetime)
                {
                    param.SqlDbType = SqlDbType.DateTime;
                }
                else if (suggestion.suggested_system_type_id == (int)SqlTypeId.date)
                {
                    param.SqlDbType = SqlDbType.Date;
                }

            }
        }
    }
}

Is there any other workaround? Tried the workarounds here and none work for only one DbContext. We have a legacy system in Delphi so we can’t convert datetime fields to datetime2 without updating the drivers, which is a BIG change. HasColumnType(“datetime”) would solve.

specifying modelBuilder.Entity<Log>().Property(p => p.Date).HasColumnType("datetime2").HasPrecision(7);

or [Column(TypeName = "datetime2")]

still produce: ((convert (datetime2, convert(varchar(255), [Extent1].[Date], 102) , 102)) >= @p__linq__0)

how to avoid it?

@adolfo1981 You are welcome. I have updated the query: there was an exception when the value of the parameter is DateTime.MinValue.

@adolfo1981 If you download the source code and change all the places I listed above from “datetime2” to “datetime” and recompile, it will work. This is assuming you are not using “datetime2” anywhere in SQL.

Also, need supports for Date datatype (without time).