如何按QuoteNo和Product分组，为QuoteLine对象填充下一行的日期与价格字段？

问题背景 #

现有数据库表quotation_lines，结构与数据如下：

需要映射到以下C#对象：

public class QuoteLine{
    public string QuoteNo {get;set;}
    public string Product {get;set;}
    public DateOnly DateEffective {get;set;}
    public decimal Price {get;set;}
    public DateOnly NextDate {get;set;}
    public decimal NextPrice {get;set;}
}

需求：按QuoteNo和Product分组，将同组内下一行的DateEffective和Price填充到当前行的NextDate和NextPrice字段（最后一行无下一行时，可设为默认值如DateOnly.MinValue和0）。

方案1：创建数据库视图（推荐，大数据量场景） #

使用SQL窗口函数LEAD()，直接在数据库层面完成下一行数据的关联：

CREATE VIEW vw_QuoteLinesWithNextData AS
SELECT
    [Quote No] AS QuoteNo,
    Product,
    [Date Effective] AS DateEffective,
    Price,
    -- 取同组下一行的生效日期，无则为NULL
    LEAD([Date Effective]) OVER (PARTITION BY [Quote No], Product ORDER BY [Date Effective]) AS NextDate,
    -- 取同组下一行的价格，无则为NULL
    LEAD(Price) OVER (PARTITION BY [Quote No], Product ORDER BY [Date Effective]) AS NextPrice
FROM quotation_lines;

说明 #

• PARTITION BY [Quote No], Product：按报价单号和产品分组，确保只在同组内查找下一行
• ORDER BY [Date Effective]：保证分组内数据按生效日期排序，确保下一行是时间上的下一个版本
• LEAD()函数：返回当前行之后指定偏移量的行的字段值，这里偏移量默认是1（即下一行）
• 映射到C#时，需处理NULL值：比如将NextDate的NULL转为DateOnly.MinValue，NextPrice的NULL转为0

方案2：C#端LINQ处理（小数据量场景） #

如果数据量不大，也可以在C#代码中通过分组排序后关联下一行：

// 假设已从数据库读取原始数据到list（仅填充前4个字段）
var rawQuoteLines = new List<QuoteLine>
{
    // 原始数据示例
    new QuoteLine { QuoteNo = "Q1", Product = "ABC", DateEffective = DateOnly.Parse("1-Jan-20"), Price = 10.00m },
    new QuoteLine { QuoteNo = "Q1", Product = "ABC", DateEffective = DateOnly.Parse("1-Mar-20"), Price = 15.00m },
    // ... 其他数据
};

var processedLines = rawQuoteLines
    .GroupBy(line => new { line.QuoteNo, line.Product })
    .SelectMany(group => 
        group.OrderBy(line => line.DateEffective)
             .Select((line, index) => new QuoteLine
             {
                 QuoteNo = line.QuoteNo,
                 Product = line.Product,
                 DateEffective = line.DateEffective,
                 Price = line.Price,
                 // 不是最后一行则取下一行的日期，否则设为默认值
                 NextDate = index < group.Count() - 1 ? group.ElementAt(index + 1).DateEffective : DateOnly.MinValue,
                 // 不是最后一行则取下一行的价格，否则设为0
                 NextPrice = index < group.Count() - 1 ? group.ElementAt(index + 1).Price : 0m
             })
    )
    .ToList();

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