[SOLVED] Entity Framework not working correctly with DateTime column when converted to compatibilty 130

Entity Framework not working correctly with DateTime column when converted to compatibilty 130

The below code was working with SQL Server Compatibility Level 100. However updating to 130, starting making the code below fail. I believe it has to do with the datetime and precision. The item isnt deleted anymore. Any clue why and is there a work around. We want to use some new functions in the 130 level.

_gameJobsRepository.Delete(q => q.GameId == gameJob.GameId && q.DateCreated <= gameJob.DateCreated);

Solution

This is a known issue with EF6 and SQL Server 2016+ in compat level 130+. SQL Server introduced a breaking change, and it broke EF6.

The workarounds are

Stay on compat level 120
Change all your datetime columns to datetime2
Keep all your datetime columns and use a command interceptor to change the parameter types (see the github issue)
Migrate to EF Core

Also this might get resolved in EF 6.3, but there's been no commitment, as it looks like a bit of work to fix without introducting breaking changes.