如何用LINQ将两行数据合并为一行？附TSQL参考示例

当前原始数据 #

期望输出格式 #

对应TSQL实现 #

;WITH T1 AS
(
SELECT
        Name,
        Currency1 Currency1Debtor,
        Currency2 Currency2Debtor
    FROM
        (
        SELECT
                    Name,
                    CurrencyName,
                    Debtor
            FROM
                    #Temp
        ) AS SourceTable
    PIVOT
        (
            SUM(Debtor) FOR CurrencyName IN (Currency1, Currency2)
        ) AS PivotTable
),
T2 AS
(
SELECT
            Name ,
            Currency1 Currency1Creditor,
            Currency2 Currency2Creditor
    FROM
            (
            SELECT
                        Name,
                        CurrencyName,
                        creditor
                FROM
                        #Temp
            ) AS SourceTable
    PIVOT
            (
                SUM(creditor) FOR CurrencyName IN (Currency1, Currency2)
            ) AS PivotTable
)
SELECT
            T1.*,
            T2.Currency1Creditor,
            T2.Currency2Creditor
    FROM
            T1
        INNER JOIN
            T2
                ON T1.Name = T2.Name

LINQ实现方案 #

首先定义对应的数据实体类：

// 原始数据实体
public class AgentCurrencyRecord
{
    public string Name { get; set; }
    public string CurrencyName { get; set; }
    public decimal Debtor { get; set; }
    public decimal Creditor { get; set; }
}

// 聚合后目标实体
public class AggregatedAgentRecord
{
    public string Name { get; set; }
    public decimal Currency1Debtor { get; set; }
    public decimal Currency1Creditor { get; set; }
    public decimal Currency2Debtor { get; set; }
    public decimal Currency2Creditor { get; set; }
}

然后通过LINQ分组聚合实现转换：

// 假设originalRecords是你的原始数据集合（可以是EF查询或内存集合）
var aggregatedResult = originalRecords
    .GroupBy(record => record.Name)
    .Select(group => new AggregatedAgentRecord
    {
        Name = group.Key,
        // 聚合Currency1的Debtor和Creditor
        Currency1Debtor = group.Where(r => r.CurrencyName == "Currency1").Sum(r => r.Debtor),
        Currency1Creditor = group.Where(r => r.CurrencyName == "Currency1").Sum(r => r.Creditor),
        // 聚合Currency2的Debtor和Creditor
        Currency2Debtor = group.Where(r => r.CurrencyName == "Currency2").Sum(r => r.Debtor),
        Currency2Creditor = group.Where(r => r.CurrencyName == "Currency2").Sum(r => r.Creditor)
    })
    .ToList();

逻辑说明 #

这个LINQ实现和TSQL的思路完全对应：

1. 按Name分组，将同一代理的多行数据归为一组
2. 对每组内的不同货币类型，分别求和Debtor和Creditor，映射到目标实体的对应字段
3. 如果是在EF Core中使用，该查询会被翻译成类似TSQL的聚合查询，数据库端执行；如果是内存集合，直接在本地完成聚合

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