Predicate (date == DateTime.MaxValue) always returns false (EF 2.1-preview1-final)

[sir-boformer]

The query .Where(m => m.Date == DateTime.MaxValue) does not work, no results are returned.

This is a new problem that started occurring when I switched from Net Core 2.0 (with Microsoft.AspNetCore.All 2.1.0-preview1-2737) to Net Core 2.1 (with Microsoft.AspNetCore.All 2.1.0-preview1-final).

I reproduced this in my migrated main project and a separate newly setup test project

Steps to reproduce

Project: https://github.com/sir-boformer/EF-DateTime-Bug

// Model
public class Membership
{
    [Key]
    public long Id { get; set; }

    public string UserId { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

// Context
public class AppDbContext : DbContext
{
    public DbSet<Membership> Memberships { get; set; }

    public AppDbContext(DbContextOptions options) : base(options) {}
}

// ASP Net Controller
[Route("api/[controller]")]
[ApiController]
public class ValuesController : ControllerBase
{
    private readonly AppDbContext _context;

    public ValuesController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<IEnumerable<Membership>> GetAll()
    {
        return await _context.Memberships.ToListAsync();
    }

    [HttpGet("max")]
    public async Task<IEnumerable<Membership>> GetMax()
    {
        // Error occurs here, result set is always empty!
        return await _context.Memberships.Where(m => m.EndDate == DateTime.MaxValue).ToListAsync();
    }

    [HttpGet("min")]
    public async Task<IEnumerable<Membership>> GetMin()
    {
        // no problems here
        return await _context.Memberships.Where(m => m.StartDate == DateTime.MinValue).ToListAsync();
    }
}

Data set used for the test:

[
   {
      "id":5,
      "userId":"UserMinMax",
      "startDate":"0001-01-01T00:00:00",
      "endDate":"9999-12-31T23:59:59.9999999"
   },
   {
      "id":6,
      "userId":"UserMax",
      "startDate":"2018-03-04T00:00:00",
      "endDate":"9999-12-31T23:59:59.9999999"
   },
   {
      "id":7,
      "userId":"UserMin",
      "startDate":"0001-01-01T00:00:00",
      "endDate":"2018-03-24T00:00:00"
   },
   {
      "id":8,
      "userId":"User",
      "startDate":"2018-03-04T00:00:00",
      "endDate":"2018-03-24T00:00:00"
   }
]

Further technical details

EF Core version: EF 2.1-preview1-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10, Windows Server 2012 R2 Datacenter
IDE: Visual Studio 2017 15.6.1

[ajcvickers]

Note for triage:
Insert SQL:

[Parameters=
[@p0='9999-12-31T23:59:59', 
@p1='0001-01-01T00:00:00', 
@p2='UserMinMax' (Size = 4000), 
@p3='9999-12-31T23:59:59', 
@p4='2018-03-04T00:00:00', 
@p5='UserMax' (Size = 4000), 
@p6='2018-03-24T00:00:00', 
@p7='0001-01-01T00:00:00', 
@p8='UserMin' (Size = 4000), 
@p9='2018-03-24T00:00:00', 
@p10='2018-03-04T00:00:00', 
@p11='User' (Size = 4000)], 
CommandType='Text', CommandTimeout='30']

      SET NOCOUNT ON;
      DECLARE @inserted0 TABLE ([Id] bigint, [_Position] [int]);
      MERGE [Memberships] USING (
      VALUES (@p0, @p1, @p2, 0),
      (@p3, @p4, @p5, 1),
      (@p6, @p7, @p8, 2),
      (@p9, @p10, @p11, 3)) AS i ([EndDate], [StartDate], [UserId], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([EndDate], [StartDate], [UserId])
      VALUES (i.[EndDate], i.[StartDate], i.[UserId])
      OUTPUT INSERTED.[Id], i._Position
      INTO @inserted0;

      SELECT [t].[Id] FROM [Memberships] t
      INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
      ORDER BY [i].[_Position];

Query SQL:

 SELECT [m].[Id], [m].[EndDate], [m].[StartDate], [m].[UserId]
      FROM [Memberships] AS [m]
      WHERE [m].[EndDate] = '9999-12-31T23:59:59.999'

[ajcvickers]

@sir-boformer This also fails for me on EF Core 2.0.1. Can you confirm that this was something that worked with EF Core 2.1 before preview1 but now fails with EF Core 2.1 preview1? Or does it only fail when running EF Core on .NET Core 2.1 as apposed to .NET Core 2.0? Do you know if it worked with EF Core 2.0 on any platform?

[sir-boformer]

I can confirm that it works that it works with version:

Microsoft.AspNetCore.All 2.1.0-preview1-27377
TargetFramwork: netcoreapp2.0
Branch: https://github.com/sir-boformer/EF-DateTime-Bug/tree/preview1-27377

Further investigation:

It also works with 2.1.0-preview1-27377 and netcoreapp2.1
Branch: https://github.com/sir-boformer/EF-DateTime-Bug/tree/preview1-27377-net21

That means the switch to .NET Core 2.1 did not cause this.

It also works with the latest stable version 2.0.6 and netcoreapp2.0
Branch: https://github.com/sir-boformer/EF-DateTime-Bug/tree/2.06-net2.0

[sir-boformer]

The problem seems to be the date precision in SQL queries.

The value of DateTime.MaxValue is 9999-12-31T23:59:59.9999999

The query in 2.1-preview1-final:

Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [m].[Id], [m].[EndDate], [m].[StartDate], [m].[UserId]
FROM [Memberships] AS [m]
WHERE [m].[EndDate] = '9999-12-31T23:59:59.999'

In 2.06 the query looks like this:

Executed DbCommand (47ms) [Parameters=[@__MaxValue_0='?'], CommandType='Text', CommandTimeout='30']
SELECT [m].[Id], [m].[EndDate], [m].[StartDate], [m].[UserId]
FROM [Memberships] AS [m]
WHERE [m].[EndDate] = @__MaxValue_0

A temporary workaround is to use >= DateTime.MaxValue instead of ==.

[smitpatel]

In SqlServer
date literal has no time part
datetime literal has 3 fractional seconds part
datetime2 literal has 7 fractional seconds part
At present we have single type mapping SqlServerDateTimeTypeMapping which is used by all 3 types and generates literal in the format of what datetime would expect. Hence it is giving incorrect result in this case (where server type is datetime2)

[smitpatel]

https://github.com/aspnet/EntityFrameworkCore/blob/13b4f7da77f6cad405f08d9b74d4d168ad382154/src/EFCore.SqlServer/Storage/Internal/SqlServerDateTimeTypeMapping.cs#L19

@bricelam - Do you know reason if any why we chose this specific format?