如何在Entity Framework Core中编写多周期股票K线数据聚合查询？

我的SQL表存储了包含Open、High、Close、Low、Volume、LastTrade、Time字段的股票数据，需要查询生成1分钟、2分钟、5分钟、15分钟级别的K线数据。已找到如下SQL查询语句：

select
    ts,
    min(low) as low,
    max(high) as high,
    sum(volume) as volume,
    min(open) as open,
    min(close) as close
from
    (select
         t.*,
         c.*,
         first_value(open) over (partition by t.ts order by c.posix_time asc) as open,
         first_value(open) over (partition by t.ts order by c.posix_time desc) as close
     from
         generate_series('2016-01-01'::timestamp, '2016-01-02'::timestamp, interval '5 minute' ) t(ts)
     left join 
         candles c on '1970-01-01' + c.posix_time * interval '1 second' between t.ts and t.ts + interval '5 minute'
    ) t
group by
    ts; 

请问如何将该SQL查询改写为Entity Framework Core的查询代码？

实现步骤与代码 #

1. 定义实体类 #

先确保EF Core实体类与数据库表对应（假设表名为Candles）：

public class Candle
{
    public decimal Open { get; set; }
    public decimal High { get; set; }
    public decimal Low { get; set; }
    public decimal Close { get; set; }
    public long Volume { get; set; }
    public DateTime Time { get; set; } // 若存储的是时间戳，可改为long PosixTime，后续转换为DateTime
    // 按需添加LastTrade等其他字段
}

// 存储聚合后的K线结果
public class AggregatedKline
{
    public DateTime Ts { get; set; }
    public decimal Low { get; set; }
    public decimal High { get; set; }
    public long Volume { get; set; }
    public decimal Open { get; set; }
    public decimal Close { get; set; }
}

2. 生成时间序列（对应SQL的generate_series） #

构造指定时间范围内的聚合时间点序列：

// 配置时间范围和聚合间隔（可替换为1/2/15分钟）
var startTime = new DateTime(2016, 1, 1);
var endTime = new DateTime(2016, 1, 2);
var interval = TimeSpan.FromMinutes(5);

// 生成所有聚合时间点
var timeSeries = Enumerable.Range(0, (int)((endTime - startTime).TotalMinutes / interval.TotalMinutes) + 1)
    .Select(i => startTime.AddMinutes(i * interval.TotalMinutes))
    .ToList();

3. 构建EF Core查询 #

用LINQ实现原SQL的聚合逻辑，EF Core会自动转换为对应SQL：

using Microsoft.EntityFrameworkCore;

// 假设你的DbContext包含DbSet<Candle> Candles
var query = from ts in timeSeries.AsQueryable()
            join c in _context.Candles 
                on 1 equals 1 
                into joinedGroup
            from c in joinedGroup.DefaultIfEmpty()
            // 匹配时间区间内的K线数据（若用PosixTime，需先转换为DateTime）
            where c == null || c.Time >= ts && c.Time < ts.Add(interval)
            group new { c, ts } by ts into aggregatedGroup
            select new AggregatedKline
            {
                Ts = aggregatedGroup.Key,
                Low = aggregatedGroup.Min(x => x.c != null ? x.c.Low : decimal.MaxValue),
                High = aggregatedGroup.Max(x => x.c != null ? x.c.High : decimal.MinValue),
                Volume = aggregatedGroup.Sum(x => x.c != null ? x.c.Volume : 0),
                // 取区间内第一条数据的Open作为聚合K线的开盘价
                Open = aggregatedGroup.Where(x => x.c != null)
                        .OrderBy(x => x.c.Time)
                        .Select(x => x.c.Open)
                        .FirstOrDefault(),
                // 取区间内最后一条数据的Close作为聚合K线的收盘价
                Close = aggregatedGroup.Where(x => x.c != null)
                        .OrderByDescending(x => x.c.Time)
                        .Select(x => x.c.Close)
                        .FirstOrDefault()
            };

// 执行查询
var aggregatedKlines = await query.ToListAsync();

补充说明 #

• 若数据库中存储的是posix_time（秒级时间戳），需将c.Time替换为DateTimeOffset.FromUnixTimeSeconds(c.PosixTime).UtcDateTime。
• EF Core会将OrderBy().FirstOrDefault()转换为SQL的FIRST_VALUE窗口函数，与原SQL逻辑一致。
• 时间序列生成逻辑可根据EF Core版本调整，低版本可直接用内存列表转为可查询对象。

内容的提问来源于stack exchange，提问作者Sourabh Devpura