EF Core实现：合并同时间槽的双向转账记录行

使用.NET 7 + SQL Server，数据库表结构如下：

[Id] BIGINT IDENTITY(1, 1),
[Start] DATETIMEOFFSET NOT NULL,
[End] DATETIMEOFFSET NOT NULL,
[FromPersonId] INT NOT NULL,
[ToPersonId] INT NOT NULL,
[AmountUsd] DECIMAL NOT NULL,

实体类定义：

public class MoneyTransfer
{
    public long Id { get; private set; }

    public decimal AmountUsd { get; private set; }

    public DateTimeOffset Start { get; private set; }
    public DateTimeOffset End { get; private set; }

    public virtual Person FromPerson { get; private set; }
    public virtual Person ToPerson { get; private set; }

    // Constructor for EF
    private MoneyTransfer() { }
}

public class Person
{
    public int Id { get; private set; }

    public string Name { get; private set; }
    public string FullName { get; private set; }

    // Constructor for EF
    private Person() { }
}

希望通过EF Core生成以下DTO：

public record PersonMoneyTransferDto(DateTimeOffset Start, DateTimeOffset End, 
                                     decimal AmountToUsd, decimal AmountFromUsd);

public record AllPersonsMoneyTransfersDto(string PersonName, 
   IReadOnlyCollection<PersonMoneyTransferDto> MoneyTransfersToThisPerson);

需求是：获取指定人员与其他人员在各时间槽的双向转账记录（数据库中双向转账各存一行），将相同Start和End的两行记录合并为一个包含双向金额的对象。

核心思路是通过分组聚合将同一时间槽、同一交易对象的双向转账合并，计算转入/转出总金额，再映射到目标DTO。以下是完整的EF Core查询实现：

int targetPersonId = 1; // 替换为你要查询的目标人员ID

var result = await context.MoneyTransfers
    // 筛选所有与目标人员相关的转账（转出或转入）
    .Where(t => t.FromPersonId == targetPersonId || t.ToPersonId == targetPersonId)
    // 按时间槽（Start+End）和交易对象分组
    .GroupBy(t => new 
    {
        t.Start,
        t.End,
        // 确定交易对象ID：转出时对方是ToPersonId，转入时对方是FromPersonId
        OtherPersonId = t.FromPersonId == targetPersonId ? t.ToPersonId : t.FromPersonId
    })
    // 计算该分组内的转入/转出总金额
    .Select(g => new 
    {
        g.Key.Start,
        g.Key.End,
        g.Key.OtherPersonId,
        // 转入目标人员的总金额：仅统计ToPersonId为目标ID的记录
        AmountToUsd = g.Sum(t => t.ToPersonId == targetPersonId ? t.AmountUsd : 0),
        // 从目标人员转出的总金额：仅统计FromPersonId为目标ID的记录
        AmountFromUsd = g.Sum(t => t.FromPersonId == targetPersonId ? t.AmountUsd : 0)
    })
    // 关联Person表获取交易对象的名称
    .Join(context.Persons, 
          transfer => transfer.OtherPersonId, 
          person => person.Id, 
          (transfer, person) => new 
          {
              person.Name,
              TransferDto = new PersonMoneyTransferDto(
                  transfer.Start, 
                  transfer.End, 
                  transfer.AmountToUsd, 
                  transfer.AmountFromUsd)
          })
    // 按交易对象名称分组，生成最终的AllPersonsMoneyTransfersDto
    .GroupBy(x => x.Name)
    .Select(g => new AllPersonsMoneyTransfersDto(
        g.Key, 
        g.Select(x => x.TransferDto).ToList().AsReadOnly()))
    .ToListAsync();

逻辑说明： #

1. 筛选范围：先过滤出所有涉及目标人员的转账记录，避免无关数据参与计算。
2. 分组规则：以Start+End（时间槽）和交易对象ID作为分组键，确保同一时间槽内与同一个人的双向转账被归为一组。
3. 金额计算：通过Sum结合条件判断，分别统计该分组内转入、转出目标人员的总金额。即使只有单向转账，另一项金额会自动为0，无需额外处理。
4. 关联映射：关联Person表获取交易对象名称，最后按名称分组生成包含该对象所有时间槽转账记录的DTO。

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