LINQ生成多销售点现金簿性能优化及替代方案咨询

架构老林

2026-5-12

优化现金簿查询性能：LINQ改进与存储过程替代方案

首先咱们拆解下你当前LINQ代码的核心性能瓶颈，然后一步步给出优化方案，重点满足你倾向用存储过程的需求。

原代码的性能问题根源

你的代码目前存在几个典型的性能杀手：

N+1查询灾难：外层循环遍历销售点，内层又遍历每日交易，每次循环都要多次访问数据库（查支出、收入、期初余额），导致大量不必要的数据库往返请求。
重复过滤与冗余计算：多次重复写相同的日期、地点过滤条件，而且每次都要重新计算总支出/总收入，没有复用中间结果。
内存加载过载：频繁调用ToList()把全量数据拉到内存，既浪费带宽又增加内存消耗。
日期比较的低效写法：用Year/Month/Day拆分日期比较，会导致数据库无法有效利用TransactionDate上的索引，拖慢查询速度。

方案一：LINQ代码快速优化（临时过渡方案）

如果暂时不想切换到存储过程，可以试试这些调整：

批量预加载所有数据
一次性把所有符合条件的交易拉到内存，然后在内存中分组处理，避免多次数据库查询：

var allTransactions = _context.vw_Cashbook
    .Where(t => t.TransactionDate >= fromDate && t.TransactionDate <= toDate 
                && (locationId == 0 || t.LocationId == locationId))
    .ToList();

// 按销售点分组
var locationGroups = allTransactions.GroupBy(t => t.LocationId);

按日期分组计算汇总
对每个销售点的交易，按日期分组，一次性算出每日的总支出、总收入，同时批量获取期初余额：

foreach (var locationGroup in locationGroups)
{
    var location = locationGroup.First();
    var dailyGroups = locationGroup.GroupBy(t => t.TransactionDate.Date);
    var cashBooks = new List<dto_CashBook>();
    // 批量获取该销售点所有日期的期初余额，避免循环调用
    var bfDict = _financeRepository.GetCashBookBBFBatch(dailyGroups.Select(g => g.Key), location.LocationId);

    foreach (var dailyGroup in dailyGroups.OrderBy(g => g.Key))
    {
        var date = dailyGroup.Key;
        var expenses = dailyGroup.Where(t => t.TransactionType == "expense")
                                .Select(x => new dto_Expense { 
                                    RecieverName = x.Name,
                                    ReferenceNumber = x.ReferenceNumber,
                                    Amount = x.Amount,
                                    TransactionId = x.TransactionId,
                                    PaymentModeId = x.PaymentModeId,
                                    TransactionDate = x.TransactionDate
                                }).ToList();
        var payments = dailyGroup.Where(t => t.TransactionType == "payment")
                                .Select(x => new dto_Payment { 
                                    Amount = x.Amount,
                                    TransactionId = x.TransactionId,
                                    PaymentDate = x.TransactionDate,
                                    Invoice = new dto_Invoice {
                                        CustomerName = x.Name,
                                        InvoiceNumber = x.ReferenceNumber,
                                        TransactionId = x.TransactionId
                                    }
                                }).ToList();
        var totalExpense = expenses.Sum(x => x.Amount);
        var totalPayment = payments.Sum(x => x.Amount);
        var bf = bfDict.TryGetValue(date, out var value) ? value : 0;
        var cb = bf + totalPayment - totalExpense;

        cashBooks.Add(new dto_CashBook {
            BalanceBF = bf,
            TotalExpense = totalExpense,
            TotalPayment = totalPayment,
            BalanceCD = cb,
            ReportDate = date,
            Expense = expenses,
            Payment = payments
        });
    }

    lstCashBookList.Add(new dto_CashBookList {
        LocationName = location.LocationName,
        CashBook = cashBooks
    });
}

这里需要给_financeRepository新增一个批量获取期初余额的方法，一次性查询所有需要的日期，避免循环调用。

方案二：存储过程替代（推荐，性能提升最明显）

存储过程可以把所有逻辑放到数据库端执行，减少网络传输，利用SQL的高效聚合能力，彻底解决N+1问题。

第一步：创建存储过程

假设你的vw_Cashbook包含LocationId、LocationName、TransactionDate、TransactionType、Amount、Name、ReferenceNumber等字段，GetCashBookBBF的逻辑是获取指定日期前一天的期末余额（请根据你的实际业务调整期初余额计算逻辑）：

CREATE PROCEDURE sp_GetCashBookReport
    @FromDate DATE,
    @ToDate DATE,
    @LocationId INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- 筛选符合条件的交易数据
    WITH DailyTransactions AS (
        SELECT
            LocationId,
            LocationName,
            CAST(TransactionDate AS DATE) AS ReportDate,
            TransactionType,
            Amount,
            Name,
            ReferenceNumber,
            TransactionId,
            PaymentModeId
        FROM vw_Cashbook
        WHERE TransactionDate BETWEEN @FromDate AND @ToDate
            AND (@LocationId = 0 OR LocationId = @LocationId)
    ),
    -- 计算每日的总支出、总收入
    DailySummaries AS (
        SELECT
            LocationId,
            LocationName,
            ReportDate,
            SUM(CASE WHEN TransactionType = 'expense' THEN Amount ELSE 0 END) AS TotalExpense,
            SUM(CASE WHEN TransactionType = 'payment' THEN Amount ELSE 0 END) AS TotalPayment
        FROM DailyTransactions
        GROUP BY LocationId, LocationName, ReportDate
    ),
    -- 计算每个销售点的期初余额（替换为你的实际BF计算逻辑）
    LocationBalances AS (
        SELECT
            dt.LocationId,
            dt.ReportDate,
            ISNULL((SELECT TOP 1 BalanceCD 
                    FROM tbl_CashBookBalances 
                    WHERE LocationId = dt.LocationId AND ReportDate < dt.ReportDate
                    ORDER BY ReportDate DESC), 0) AS BalanceBF
        FROM DailySummaries dt
    )
    -- 合并汇总与明细，返回最终结果
    SELECT
        ds.LocationId,
        ds.LocationName,
        ds.ReportDate,
        lb.BalanceBF,
        ds.TotalExpense,
        ds.TotalPayment,
        (lb.BalanceBF + ds.TotalPayment - ds.TotalExpense) AS BalanceCD,
        -- 支出明细打包为JSON
        (SELECT 
             RecieverName = Name,
             ReferenceNumber,
             Amount,
             TransactionId,
             PaymentModeId,
             TransactionDate
         FROM DailyTransactions dt
         WHERE dt.LocationId = ds.LocationId 
             AND dt.ReportDate = ds.ReportDate 
             AND dt.TransactionType = 'expense'
         FOR JSON PATH) AS ExpenseDetails,
        -- 收入明细打包为JSON
        (SELECT 
             Amount,
             TransactionId,
             PaymentDate = TransactionDate,
             Invoice = (SELECT 
                           CustomerName = Name,
                           InvoiceNumber = ReferenceNumber,
                           TransactionId
                       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
         FROM DailyTransactions dt
         WHERE dt.LocationId = ds.LocationId 
             AND dt.ReportDate = ds.ReportDate 
             AND dt.TransactionType = 'payment'
         FOR JSON PATH) AS PaymentDetails
    FROM DailySummaries ds
    JOIN LocationBalances lb ON ds.LocationId = lb.LocationId AND ds.ReportDate = lb.ReportDate
    ORDER BY ds.LocationId, ds.ReportDate;
END

第二步：C#中调用存储过程

用Entity Framework Core调用存储过程，将返回结果映射到你的DTO：

public IList<dto_CashBookList> CashBookReport(DateTime? fromDate, DateTime? toDate, int? locationId)
{
    try
    {
        var parameters = new[]
        {
            new SqlParameter("@FromDate", SqlDbType.Date) { Value = fromDate },
            new SqlParameter("@ToDate", SqlDbType.Date) { Value = toDate },
            new SqlParameter("@LocationId", SqlDbType.Int) { Value = locationId ?? 0 }
        };

        // 执行存储过程并获取结果
        var results = _context.Database.SqlQueryRaw<CashBookReportResult>(
            "EXEC sp_GetCashBookReport @FromDate, @ToDate, @LocationId",
            parameters
        ).ToList();

        // 分组映射到目标DTO
        var cashBookLists = results.GroupBy(r => r.LocationId)
            .Select(g => new dto_CashBookList
            {
                LocationName = g.First().LocationName,
                CashBook = g.Select(r => new dto_CashBook
                {
                    BalanceBF = r.BalanceBF,
                    TotalExpense = r.TotalExpense,
                    TotalPayment = r.TotalPayment,
                    BalanceCD = r.BalanceCD,
                    ReportDate = r.ReportDate,
                    TransactionStartDate = fromDate.Value,
                    TransactionEndDate = toDate.Value,
                    // 反序列化JSON明细
                    Expense = !string.IsNullOrEmpty(r.ExpenseDetails) 
                        ? JsonSerializer.Deserialize<List<dto_Expense>>(r.ExpenseDetails) 
                        : new List<dto_Expense>(),
                    Payment = !string.IsNullOrEmpty(r.PaymentDetails)
                        ? JsonSerializer.Deserialize<List<dto_Payment>>(r.PaymentDetails)
                        : new List<dto_Payment>()
                }).ToList()
            }).ToList();

        return cashBookLists;
    }
    catch (Exception ex)
    {
        // 建议添加日志记录，不要直接抛出原始异常
        throw;
    }
}

// 辅助类：映射存储过程返回结果
public class CashBookReportResult
{
    public int LocationId { get; set; }
    public string LocationName { get; set; }
    public DateTime ReportDate { get; set; }
    public decimal BalanceBF { get; set; }
    public decimal TotalExpense { get; set; }
    public decimal TotalPayment { get; set; }
    public decimal BalanceCD { get; set; }
    public string ExpenseDetails { get; set; }
    public string PaymentDetails { get; set; }
}

额外性能优化建议

给vw_Cashbook创建复合索引：CREATE NONCLUSTERED INDEX IX_vw_Cashbook_Location_Date_Type ON vw_Cashbook (LocationId, TransactionDate, TransactionType)，大幅提升过滤和分组速度。
如果不需要明细数据，可以修改存储过程只返回汇总信息，进一步减少数据传输量。
避免在循环中捕获又抛出异常，应该在顶层统一处理并记录日志，减少异常处理开销。

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

LINQ生成多销售点现金簿性能优化及替代方案咨询

原代码的性能问题根源 #

方案一：LINQ代码快速优化（临时过渡方案） #

方案二：存储过程替代（推荐，性能提升最明显） #

第一步：创建存储过程 #

第二步：C#中调用存储过程 #