如何在EF Core 6中按可空DateTimeOffset属性分组

问题描述 #

实体类 #

public class Transaction
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Required]
    public long UserId { get; set; }

    [Required]
    public DateTimeOffset? TransactAt { get; set; }

    ...

    [Required]
    public decimal? Amount { get; set; }
}

我需要按UserId和TransactAt.Date分组统计交易数据，编写的查询代码如下：

var transactions = await dbContext.Transactions
    .AsNoTracking()
    .Where(x.TransactAt.HasValue
        && x.TransactAt.Value.Date < DateTime.Today.Date)
    .GroupBy(x => new { x.UserId, x.TransactAt.Value.Date })
    .Select(m => new TransactionWithoutInvoiceModel
    {
        UserId = m.Key.UserId,
        TransactionIds = m.Select(x => x.Id).ToList(),
        Amounts = m.Select(x => x.Amount).ToList()
    })
    .ToListAsync();

执行时出现编译器错误：

Nullable value type may be null. csharp(CS8269)

尝试改用空传播运算符修改分组逻辑：

GroupBy(x => new { x.UserId, x.TransactAt?.Date })

又触发新的错误：

An expression tree lambda may not contain a null propagating operator. csharp(CS8072)

示例数据 #

Transactions

Id      UserId      Amount      TransactAt
------------------------------------------------------
1       123         100         2022-12-26 01:02:03+00
2       123         200         2022-12-26 01:05:03+00
3       123         300         2022-12-26 01:08:03+00
4       123         400         2022-12-26 01:10:03+00
5       123         500         2022-12-27 01:02:03+00
6       456         100         2022-12-26 01:02:03+00
7       456         200         2022-12-26 01:02:03+00

期望分组结果 #

[
   {
      "UserId":123,
      "TransactionIds":[1,2,3,4],
      "Amounts":[100,200,300,400]
   },
   {
      "UserId":123,
      "TransactionIds":[5],
      "Amounts":[500]
   },
   {
      "UserId":456,
      "TransactionIds":[6,7],
      "Amounts":[100,200]
   }
]

环境信息 #

• .NET SDK 6.0.400
• PostgreSQL 13.3
• Npgsql.EntityFrameworkCore.PostgreSQL 6.0.7

解决方案 #

问题核心是：编译器无法自动识别Where子句已经过滤掉TransactAt为null的记录，因此在GroupBy中访问x.TransactAt.Value时仍提示空值风险；而空传播运算符?.无法被EF Core转换为SQL表达式树，所以也不可用。

以下三种方式均可解决问题：

方式1：使用空包容运算符明确告知编译器值非空 #

既然Where已经筛选出TransactAt.HasValue为true的记录，可以用!运算符强制告诉编译器该值不会为null：

var transactions = await dbContext.Transactions
    .AsNoTracking()
    .Where(x => x.TransactAt.HasValue && x.TransactAt.Value.Date < DateTime.Today.Date)
    .GroupBy(x => new { x.UserId, Date = x.TransactAt!.Value.Date })
    .Select(m => new TransactionWithoutInvoiceModel
    {
        UserId = m.Key.UserId,
        TransactionIds = m.Select(x => x.Id).ToList(),
        Amounts = m.Select(x => x.Amount).ToList()
    })
    .ToListAsync();

方式2：先投影再分组 #

通过Select提前提取非空的日期字段，再进行分组，让编译器明确字段非空：

var transactions = await dbContext.Transactions
    .AsNoTracking()
    .Where(x => x.TransactAt.HasValue && x.TransactAt.Value.Date < DateTime.Today.Date)
    .Select(x => new 
    {
        x.Id,
        x.UserId,
        x.Amount,
        TransactDate = x.TransactAt.Value.Date
    })
    .GroupBy(x => new { x.UserId, x.TransactDate })
    .Select(m => new TransactionWithoutInvoiceModel
    {
        UserId = m.Key.UserId,
        TransactionIds = m.Select(x => x.Id).ToList(),
        Amounts = m.Select(x => x.Amount).ToList()
    })
    .ToListAsync();

方式3：使用PostgreSQL专属日期截断函数（推荐） #

针对PostgreSQL环境，使用EF Core提供的EF.Functions.DateTrunc函数直接在数据库层面截断日期，既避免空值问题，也能利用数据库索引提升性能：

var transactions = await dbContext.Transactions
    .AsNoTracking()
    .Where(x => x.TransactAt.HasValue && x.TransactAt < DateTime.Today)
    .GroupBy(x => new 
    { 
        x.UserId, 
        TransactDate = EF.Functions.DateTrunc("day", x.TransactAt!.Value)
    })
    .Select(m => new TransactionWithoutInvoiceModel
    {
        UserId = m.Key.UserId,
        TransactionIds = m.Select(x => x.Id).ToList(),
        Amounts = m.Select(x => x.Amount).ToList()
    })
    .ToListAsync();

注：x.TransactAt < DateTime.Today比比较Date属性更高效，数据库可直接利用时间字段的索引；DateTrunc("day", ...)的效果和Date属性一致，更符合PostgreSQL的语法规范。

内容的提问来源于stack exchange，提问作者Ed Wang