如何在Entity Framework Core按日期分组时包含日期范围内所有日期（含零订单日）

我正在使用Entity Framework Core，尝试在指定日期范围内按日期对订单进行分组，目标是获取该范围内每一天的订单数量，包括没有订单的日期（显示数量为0）。

以下是我的代码：

using Microsoft.EntityFrameworkCore;

await using OrdersContext db = new OrdersContext();

var from = new DateTime(2023, 12, 28).ToUniversalTime();
var to = from.AddDays(30);
var group = await db.Orders
    .Where(x => x.OrderDate >= from && x.OrderDate <= to)
    .GroupBy(x => x.OrderDate.Date)
    .Select(x => new { Day = x.Key, Count = x.Count() })
    .OrderBy(x => x.Day)
    .ToListAsync();
group.ForEach(x => Console.WriteLine($"{x.Day}: {x.Count}"));

public class OrdersContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(
            "Host=localhost;Database=ef_core_missing_days;Username=postgres;Password=postgres"
        );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Order>()
            .HasData(
                new Order { Id = 1, OrderDate = new DateTime(2024, 1, 1).ToUniversalTime() },
                new Order { Id = 2, OrderDate = new DateTime(2024, 1, 2).ToUniversalTime() },
                new Order { Id = 3, OrderDate = new DateTime(2024, 1, 3).ToUniversalTime() },
                new Order { Id = 4, OrderDate = new DateTime(2024, 1, 5).ToUniversalTime() },
                new Order { Id = 5, OrderDate = new DateTime(2024, 1, 7).ToUniversalTime() }
            );
        base.OnModelCreating(modelBuilder);
    }
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
}

目前的问题是，输出仅打印数据库中有订单的日期：

2024-01-01: 1
2024-01-02: 1
2024-01-03: 1
2024-01-05: 1
2024-01-07: 1

但我希望生成从2023-12-28到2024-01-27范围内的所有日期，对无订单的日期显示数量为0。

如何修改查询以包含日期范围内的所有日期，即使这些日期没有订单？

我曾尝试在查询后手动添加缺失的日期，但这不是我需要的方案。我寻求直接基于IQueryable实现的解决方案，无需在获取数据后手动处理。

核心思路是先生成日期范围内的完整日期序列，再与订单数据做左连接，确保所有日期被保留，无订单日期的计数为0。以下两种方案均基于IQueryable实现，所有逻辑在数据库端执行。

方法1：利用PostgreSQL原生函数generate_series生成日期序列 #

PostgreSQL内置的generate_series函数可以直接生成指定范围的日期序列，通过EF Core调用该函数后与订单表左连接统计：

using Microsoft.EntityFrameworkCore;

await using OrdersContext db = new OrdersContext();

var from = new DateTime(2023, 12, 28).ToUniversalTime();
var to = from.AddDays(30);

// 调用PostgreSQL函数生成日期序列
var dateSeries = db.Set<DateOnly>()
    .FromSqlRaw($"SELECT generate_series('{from:yyyy-MM-dd}'::date, '{to:yyyy-MM-dd}'::date, '1 day'::interval)::date AS date");

// 左连接订单表并分组统计
var result = await dateSeries
    .LeftJoin(
        db.Orders.Where(o => o.OrderDate >= from && o.OrderDate <= to),
        date => date,
        order => DateOnly.FromDateTime(order.OrderDate.Date),
        (date, order) => new { Date = date, Order = order }
    )
    .GroupBy(x => x.Date)
    .Select(g => new { Day = g.Key, Count = g.Count(x => x.Order != null) })
    .OrderBy(x => x.Day)
    .ToListAsync();

result.ForEach(x => Console.WriteLine($"{x.Day}: {x.Count}"));

注意事项 #

• 需使用EF Core 6及以上版本，确保DateOnly能正确映射到PostgreSQL的date类型。
• 若偏好DateTime类型，可调整generate_series的返回类型为timestamp，并修改连接条件。

方法2：内存生成日期序列后转为IQueryable（跨数据库兼容） #

如果不想依赖数据库特定函数，可在内存中生成日期序列，再转为IQueryable让EF Core翻译为SQL执行：

using Microsoft.EntityFrameworkCore;

await using OrdersContext db = new OrdersContext();

var from = new DateTime(2023, 12, 28).ToUniversalTime();
var to = from.AddDays(30);

// 内存生成完整日期序列，转为IQueryable后由EF Core处理
var dates = new List<DateOnly>();
for (var date = DateOnly.FromDateTime(from.Date); date <= DateOnly.FromDateTime(to.Date); date = date.AddDays(1))
{
    dates.Add(date);
}
var dateQuery = dates.AsQueryable();

// 左连接统计订单数量
var result = await dateQuery
    .LeftJoin(
        db.Orders.Where(o => o.OrderDate >= from && o.OrderDate <= to),
        d => d,
        o => DateOnly.FromDateTime(o.OrderDate.Date),
        (d, o) => new { Date = d, Order = o }
    )
    .GroupBy(x => x.Date)
    .Select(g => new { Day = g.Key, Count = g.Count(x => x.Order != null) })
    .OrderBy(x => x.Day)
    .ToListAsync();

result.ForEach(x => Console.WriteLine($"{x.Day}: {x.Count}"));

优势与局限 #

• 优势：不依赖数据库特性，兼容性强。
• 局限：若日期范围极大（如数年），内存生成序列可能影响性能，此时推荐使用数据库原生函数方案。

关键原理 #

• 左连接（LeftJoin）：确保所有生成的日期都被保留，即使没有对应的订单记录。
• 条件计数：Count(x => x.Order != null)仅统计非空的订单记录，无订单的日期自然返回0。

内容的提问来源于stack exchange，提问作者diper ps4