如何将现有T-SQL代码转换为C# LINQ查询（无外键表关联）

原T-SQL代码 #

SELECT p1.PatientId, 
SUM(p1.TotalAmount) as TotalTreatmentAmount, 
SUM(p1.Discount) As TotalTreatmentDiscount,
SUM(p1.Payable) as TotalTreatmentPayable,
SUM(p1.Balance) as TotalTreatmentBalancce,
p2.TotalTreatmentPaidAmount
FROM PatientPayments as p1
join (SELECT p2.PatientId, SUM(p2.PaidAmount) as TotalTreatmentPaidAmount
FROM PatientPaymentHistories p2
where p2.PaymentType != 'Refund' 
group by p2.PatientId) as p2 
ON p2.PatientId = p1.PatientId
group by p1.PatientId, p2.TotalTreatmentPaidAmount

现有代码的问题 #

你的LINQ代码直接关联两张原始表，没有先对PatientPaymentHistories做过滤退款类型+分组求和，也没有对PatientPayments做分组汇总，最终导出的是明细数据而非T-SQL对应的汇总结果，这是代码不符合预期的核心原因。

正确的LINQ实现（查询语法） #

public IActionResult ExportPatientPaymentDataToExcel()
{
    // 先处理PatientPaymentHistories的子查询：过滤退款+按PatientId分组求和
    var paymentHistorySummary = 
        from p2 in _context.PatientPaymentHistories
        where p2.PaymentType != "Refund"
        group p2 by p2.PatientId into g
        select new 
        {
            PatientId = g.Key,
            TotalTreatmentPaidAmount = g.Sum(x => x.PaidAmount)
        };

    // 关联PatientPayments的分组结果和上面的汇总数据
    var patientPaymentSummary =
        from p1 in _context.PatientPayments
        group p1 by p1.PatientId into g
        join ph in paymentHistorySummary on g.Key equals ph.PatientId
        select new 
        {
            PatientId = g.Key,
            TotalTreatmentAmount = g.Sum(x => x.TotalAmount),
            TotalTreatmentDiscount = g.Sum(x => x.Discount),
            TotalTreatmentPayable = g.Sum(x => x.Payable),
            TotalTreatmentBalance = g.Sum(x => x.Balance),
            ph.TotalTreatmentPaidAmount
        };

    // 构建DataTable
    DataTable PatientPaymentTable = new DataTable("PatientPayment");
    PatientPaymentTable.Columns.AddRange(new DataColumn[6]
    {
          new DataColumn("Patient ID"),
          new DataColumn("Total Amount"),
          new DataColumn("Total Discount"),
          new DataColumn("Total Payable"),
          new DataColumn("Total Balance"),
          new DataColumn("Total Paid"),
    });

    // 填充汇总数据到DataTable
    foreach (var summary in patientPaymentSummary)
    {
        PatientPaymentTable.Rows.Add(
            summary.PatientId,
            summary.TotalTreatmentAmount,
            summary.TotalTreatmentDiscount,
            summary.TotalTreatmentPayable,
            summary.TotalTreatmentBalance,
            summary.TotalTreatmentPaidAmount
        );
    }

    // 导出Excel逻辑保持不变
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(PatientPaymentTable);
        using (MemoryStream stream = new MemoryStream())
        {
            wb.SaveAs(stream);
            return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"{GetLocalDateTime().Date.ToShortDateString()} PatientPayment_Data.xlsx");
        }
    }
}

关键说明 #

• 先对PatientPaymentHistories执行过滤和分组求和，得到每个患者的非退款支付总额，和T-SQL的子查询逻辑完全对应。
• 再对PatientPayments按PatientId分组，计算各项汇总值，然后和上面的支付历史汇总数据关联。
• DataTable新增了Total Balance列，对应T-SQL中的TotalTreatmentBalancce（原T-SQL拼写有误，代码中修正为TotalTreatmentBalance）。

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