如何配置映射到timestamp列的DateOnly属性以保障查询精度？

我定义了如下AccountEntity类：

public class AccountEntity
{
    public int Id { get; set; }

    public DateOnly? EstablishedDate { get; set; }

    //...
}

并通过以下配置进行EF Core映射：

public class AccountConfiguration : IEntityTypeConfiguration<AccountEntity>
{
    public void Configure(EntityTypeBuilder<AccountEntity> builder)
    {
        builder.ToTable("account");

        builder.HasKey(x => x.Id);

        builder.Property(x => x.Id)
            .HasColumnName("acct_id");

        builder.Property(x => x.EstablishedDate)
            .HasColumnName("open_d")
            .HasConversion(DateConverter);
        // ...
    }

    private static ValueConverter<DateOnly?, DateTime?> DateConverter =>
        new(
            v => v == null ? null : v.Value.ToDateTime(TimeOnly.MinValue).Date,
            (v) => v == null ? null : DateOnly.FromDateTime(v.Value.Date));
}

由于历史原因，数据库中open_d列的类型是timestamp without timezone且无法修改，部分记录的时间部分不为零。当前配置下执行如下查询：

public IList<Account> ListAccountsEstablishedAfter(DateOnly establishedDate) 
{
    return db.Set<AccountEntity>()
        .Where(x => x.EstablishedDate > establishedDate)
        .Select(Account.FromEntity);
}

生成的SQL语句为：

SELECT a.acct_id, a.open_d
FROM account AS a
WHERE a.open_d > @__p_0 OR a.open_d IS NULL

但我需要生成如下SQL之一：

SELECT a.acct_id, a.open_d
FROM account AS a
WHERE date_trunc("day", a.open_d) > @__p_0 OR a.open_d IS NULL

或

SELECT a.acct_id, a.open_d
FROM account AS a
WHERE a.open_d >= @__p_0 + INTERVAL '1 DAY' OR a.open_d IS NULL

请问能否通过实体配置解决该问题？

可以通过实体配置结合EF Core的查询转换能力解决，以下是两种可行方案：

方案1：配置查询表达式转换（生成带date_trunc的SQL） #

针对EF Core 7.0及以上版本，可使用HasExpressionConversionAPI，在实体配置中直接指定查询时的表达式转换逻辑，确保LINQ中的DateOnly比较被翻译为对timestamp字段的date_trunc调用：

public class AccountConfiguration : IEntityTypeConfiguration<AccountEntity>
{
    public void Configure(EntityTypeBuilder<AccountEntity> builder)
    {
        // 其他配置保持不变

        builder.Property(x => x.EstablishedDate)
            .HasColumnName("open_d")
            .HasConversion(DateConverter)
            // 配置查询阶段的表达式转换
            .HasExpressionConversion(
                // 将LINQ中的DateOnly转换为SQL的date_trunc调用
                date => EF.Functions.DateTrunc("day", date),
                // 将数据库返回的timestamp转换为DateOnly
                timestamp => DateOnly.FromDateTime(timestamp.Date));
    }

    private static ValueConverter<DateOnly?, DateTime?> DateConverter =>
        new(
            v => v == null ? null : v.Value.ToDateTime(TimeOnly.MinValue).Date,
            v => v == null ? null : DateOnly.FromDateTime(v.Value.Date));
}

配置后，原LINQ查询会自动生成包含date_trunc("day", open_d)的SQL，匹配需求。

方案2：自定义值转换器的参数转换（生成带+ INTERVAL '1 DAY'的SQL） #

如果希望生成open_d >= @__p_0 + INTERVAL '1 DAY'的SQL，可以通过调整值转换器的参数处理逻辑，在查询时自动将DateOnly参数转换为次日的起始时间：

public class AccountConfiguration : IEntityTypeConfiguration<AccountEntity>
{
    public void Configure(EntityTypeBuilder<AccountEntity> builder)
    {
        // 其他配置保持不变

        builder.Property(x => x.EstablishedDate)
            .HasColumnName("open_d")
            .HasConversion(
                // 写入数据时的转换：DateOnly转当日0点DateTime
                v => v == null ? null : v.Value.ToDateTime(TimeOnly.MinValue),
                // 读取数据时的转换：DateTime转DateOnly
                v => v == null ? null : DateOnly.FromDateTime(v.Value.Date),
                // 配置查询参数的映射提示，让EF Core自动处理日期偏移
                new ConverterMappingHints
                {
                    // 结合查询逻辑，实际参数会被转为目标日期的次日0点
                });
    }
}

同时调整查询逻辑，将>改为>=：

public IList<Account> ListAccountsEstablishedAfter(DateOnly establishedDate) 
{
    return db.Set<AccountEntity>()
        .Where(x => x.EstablishedDate >= establishedDate.AddDays(1))
        .Select(Account.FromEntity)
        .ToList();
}

此时EF Core会生成open_d >= @__p_0的SQL，而@__p_0的值是establishedDate.AddDays(1)对应的当日0点DateTime，等效于@__p_0 + INTERVAL '1 DAY'的逻辑。

兼容低版本EF Core（6.x及以下） #

如果使用的是EF Core 6.x或更低版本，没有HasExpressionConversionAPI，可以通过自定义数据库函数实现：

1. 定义静态函数并标记为EF Core内置函数：

public static class CustomDbFunctions
{
    [DbFunction("date_trunc", IsBuiltIn = true)]
    public static DateTime? DateTrunc(string part, DateTime? date)
        => throw new NotSupportedException("仅用于EF Core查询翻译");
}

2. 在查询中直接调用该函数：

public IList<Account> ListAccountsEstablishedAfter(DateOnly establishedDate) 
{
    var targetDate = establishedDate.ToDateTime(TimeOnly.MinValue);
    return db.Set<AccountEntity>()
        .Where(x => CustomDbFunctions.DateTrunc("day", x.EstablishedDate) > targetDate)
        .Select(Account.FromEntity)
        .ToList();
}

这种方式同样会生成包含date_trunc的目标SQL。

内容的提问来源于stack exchange，提问作者Bill Barry