SQL Server 2016 and EntityFramework 6.1.3 Datetime change detection issue about ef6 HOT 8 CLOSED

Why would this issue be closed?
I hope you are not expecting every shop that is using EF and SQL 2016 to update all their DateTime datatypes to DateTime2 just because EF "assumed" it should be using DateTime2 when the actual datatype in the DB was DateTime?

This problem is HUGE and affects anyone using EF to "SaveChanges" since the underlying code in EF thinks that the DateTime fields in .NET are not matching the SQL DateTime fields and will cause a DB record change.

As of now the only "real" solution is to set the DB level to 120 until the EF team can "fix" this problem.
This is not a SQL problem but instead is an EF problem in that EF "assumed" it should be using DateTime2 when the actual datatype in the DB was DateTime.

Maybe there could be a .NET attribute on the DateTime properties that could tell EF which datatype to use?

Something like...

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

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

from ef6.

I just updated the StateLastChangedDate column to be a datetime2(7) column, and it appears to work, so there's probably a conversion/precision issue when handling datetime fields with sql server 2016.

from ef6.

@rakker91 I am looking at this now. Let me start saying that in general DATETIME2 is a much better choice of a column type than DATETIME to store values from a .NET DateTime property without any precision loss. The only reason we have not made the switch in EF Code First to create DATETIME2 columns by default is that it would be a breaking change.

Second, I think I may have identified a behavior change in SQL Server 2016 that could explain why you only see the issue with database compatibility 130, but I still need some help understanding why and reproducing the exact symptoms you described.

I am playing with the following code in a simple console application. If you could use it as a starting point to produce a repro, that would be great:

using System;
using System.Linq;
using System.Data.Entity;
using System.Diagnostics;

namespace ReproEF6_49
{
    class Program
    {
        static void Main(string[] args)
        {
            var ticks = 636072141911233333; // same as '2016-08-19 14:36:31.1233333' 
            var date = new DateTime(ticks);

            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

            using (var db = new MyContext())
            {
                //db.Database.ExecuteSqlCommand(
                //    TransactionalBehavior.DoNotEnsureTransaction,
                //    @"ALTER DATABASE [ReproEF6_49.MyContext] SET COMPATIBILITY_LEVEL = 120");

                db.Events.Add(new Event { Date = date });
                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                db.Database.Log = Console.WriteLine;
                var myEvent = db.Events.First();
                myEvent.Date = date; // triggers a change, regardless of compatibility level 
                db.ChangeTracker.DetectChanges();
                var property = db.Entry(myEvent).Property(e => e.Date);
                Debug.Assert(property.OriginalValue.Ticks == 636072141911230000); // value is truncated
                Debug.Assert(property.CurrentValue.Ticks == ticks);
                db.SaveChanges(); // triggers an UPDATE 
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Event> Events { get; set; }
    }

    public class Event
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
    }
}

Note that values on a .NET DateTime are truncated when stored in a SQL Server DATETIME column, regardless of the database compatibility level. That is why in my code sample EF will detect a difference between the original value and the current value and will produce a spurious update (i.e. the actual value of the column on the database will not be modified).

Possible root cause

I believe the issue could be caused by on of the breaking changes documented at https://msdn.microsoft.com/en-us/library/ms143179.aspx and explained in more detail at https://connect.microsoft.com/SQLServer/feedback/details/3104723:

Posted by Dan Guzman on 10/1/2016 at 9:04 PM
...
The datetime value of '2016-09-27 18:03:03.297' is actually '2016-09-27 18:03:03.2966666...' because SQL Server internally uses 1/300 second interval units for the time portion. The infinitely repeating value is rounded to the fixed datetime precision of 3, resulting in the '2016-09-27 18:03:03.297' value. However, when the value is converted to a higher precision type, the repeating value is rounded to the target data type precision, which is '2016-09-27 18:03:03.2966667' in the case of datetime2(7).
This behavior is different than before SQL 2016, where the internal value was first rounded to a precision of 3, losing the available sub-millisecond value. As you observed, the conversion behavior may be controlled with the database compatibility level.

The difference in behavior can be easily observed using EF or directly in SQL, but I still don't understand why they would trigger a spurious update.

The following code executes the same comparison under different compatibility levels and produces different results:

ALTER DATABASE [MyDatabase]   
SET COMPATIBILITY_LEVEL = 120 ;
GO

declare @mydatetime2  as DATETIME2 ;
declare @mydatetime as DATETIME;
set @mydatetime2 = '2016-08-19 14:36:31.1233333';
set @mydatetime = @mydatetime2;

SELECT DATEDIFF (nanosecond, @mydatetime2, @mydatetime ) AS "Result for Level 120"
GO
-- Result for Level 120
-- -333300

ALTER DATABASE [MyDatabase]   
SET COMPATIBILITY_LEVEL = 130 ;
GO

declare @mydatetime2  as DATETIME2 ;
declare @mydatetime as DATETIME;
set @mydatetime2 = '2016-08-19 14:36:31.1233333';
set @mydatetime = @mydatetime2;

SELECT DATEDIFF (nanosecond, @mydatetime2, @mydatetime ) AS "Result for Level 130"
GO
-- Result for Level 130
-- 33

In essence the implicit conversion between DATETIME2 and DATETIME in this SQL script is similar to what would happens in EF: by default we produce parameters of type DATETIME2 for queries, inserts and updates, even if the target column is actually of type DATETIME.

However EF compares values to decide if updates are necessary, so this is not sufficient to explain what you are seeing.

from ef6.

from ef6.

Note for triage:

I haven't been able to reproduce the exact issue reported here, but it appears to be related to a breaking change in SQL Server 2016. The workaround (confirmed by the customer) is to change the database column to be DATETIME2.

Besides this issue, we have seen before that the fact that we use SqlDbType.DateTime2 for parameters that match columns of type DATETIME causes silent truncation (e.g. http://entityframework.codeplex.com/workitem/2185 was about DATETIME keys not working correctly). However I don't think we have had enough data to prioritize making changes in this area.

Also, we have considered the possibility to have Code First create columns of type DATETIME2 by default, but that would be a breaking change.

In summary, my recommendation is to close this issue as won't fix.

from ef6.

https://support.microsoft.com/en-us/help/4010261/sql-server-2016-improvements-in-handling-some-data-types-and-uncommon-

from ef6.

Hi, recently we meet this issue when upgrading from SQL server 2008 to SQL 2016. Seems the EF use Datetime2 as the default from beginning, does anyone know what the breaking change in SQL 2016? Is there any way to solve it excepti changing SQL column from datetime to datetime2?

from ef6.

@AlexChongMicrosoft look at issue #578 posted by @ImGonaRot above. There are multiple suggestions in that thread.

from ef6.