Version 6.0.4+ causes translation failure of DateTime.Date when mapped to PG date

[SeanLeitzinger]

Using 6.0.3 works as expected. When upgrading to 6.0.4 or higher, all of my LINQ queries that use DateTime as a predicate break. The column type is "date". An example of the error:

System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(p => !(p.IsDeleted))
.Where(p => p.TransferDate != null && p.TransferDate.Value.Date.Year == __year_0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

The C# code:
var payments = await context.Payments.Where(x => x.TransferDate != null && x.TransferDate.Value.Date.Year == year).ToListAsync();

I'm having trouble finding the change in 6.0.4 that precipitated this issue. Is this a known breaking change?

[roji]

I'm not able to reproduce the problem, please see the code below. Can you please submit a minimal, runnable code sample which reproduces this, or tweak the below to make it fail?

Attempted repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var year = 2022;
var payments = await context.Payments.Where(x => x.TransferDate != null && x.TransferDate.Value.Date.Year == year).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Payment> Payments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Payment>().HasQueryFilter(p => !p.IsDeleted);
    }
}

public class Payment
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? TransferDate { get; set; }
}

[SeanLeitzinger]

Add the following to OnModelCreating:
modelBuilder.Entity().Property(x => x.TransferDate)
.HasColumnType("date");

This reproduces the issue. Thanks.

[roji]

Thanks, can see the failure now - will investigate.

[roji]

Out of curiosity, any particular reason you're calling Date there, given that TransferDate already represents a date? That's guaranteed to do nothing, and removing it makes the query work.

(I'll still fix this though)

[roji]

This happened as a result of #2336, which make the translation of DateTime.Date vary based on the type mapping (timestamp vs. timestamptz); date was left out.

[SeanLeitzinger]

Out of curiosity, any particular reason you're calling Date there, given that TransferDate already represents a date? That's guaranteed to do nothing, and removing it makes the query work.

(I'll still fix this though)

I'm not sure why I did it on that query. I don't do it anywhere else in the system now that I look at it. Possibly a mistake, or habit from doing it in other systems that are SQL Server based. Thanks for pointing it out since I can finally update now to 6.0.6.

[roji]

Sure thing. In any case, I'm preparing a fix that would simply ignore that Date property access, when the DateTime is mapped to a PG date.