Ef6: SQL Server 2016 and EntityFramework 6.1.3 Datetime change detection issue

[rakker91]

We recently upgraded to SQL Server 2016 and have found a compatibility issue with EF 6.1.3 Code First. What we're seeing is that datetime data types are being detected as changed, when they haven't actually changed, which is causing records to be written to the database when they shouldn't, which is causing concurrency exceptions when we check against timestamps.

If we change the database compatibility to 2014, everything works as expected.

When in 2016 compatibility mode, the following sql statement will be generated by EF, but this statement does not appear when running against previous versions of SQL Server. Note that the field in question (StateLastChangedDate) is NOT changing:

exec sp_executesql N'UPDATE [dbo].[TranslationTarget]
SET [StateLastChangedDate] = @0
WHERE (([Id] = @1) AND ([LastEdited] = @2))
SELECT [LastEdited]
FROM [dbo].[TranslationTarget]
WHERE @@ROWCOUNT > 0 AND [Id] = @1',N'@0 datetime2(7),@1 uniqueidentifier,@2 binary(8)',@0='2016-08-19 14:36:31.1233333',@1='725DE153-266D-43A1-9811-D63FEE3DFA22',@2=0x0000000000002AC4

The fields are defined as:

[StateLastChangedDate] [datetime] NULL
[LastEdited] [timestamp] NOT NULL

When we look at our audit records, we see repeated updates for that table, all with exactly the same values, other than the RowVersion column.

For example, a single update produce the following StateLastChangedDate updates (note how they're all the same):
2016-08-19 14:36:31.123
2016-08-19 14:36:31.123
2016-08-19 14:36:31.123
2016-08-19 14:36:31.123
2016-08-19 14:36:31.123

Help? :)

[rakker91]

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.

[divega]

@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:

``` C#
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:

``` SQL
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.

[rakker91]

Diego,

Thanks for looking at this. I agree, datetime2 is a much better choice, and we did go ahead and update everything to use datetime2 which wasn’t much fun. ☺

I won’t be able to look at this until next week at the earliest, but I’ll try and spend some time on it then and see if I can’t get you a simple reproduction. I agree that the change in 2016 is likely the cause.

Robert

[divega]

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.

[ErikEJ]

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

[ImGonaRot]

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; }

[AlexChongMicrosoft]

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?

[adolfo1981]

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