ASP.NET WEB API（Entity Framework）下含嵌套SUM的SQL语句转LINQ实现求助

Got it, let's break down how to convert your SQL query to LINQ, focusing on that tricky running balance calculation. First, let's recap the core logic: you're fetching transactions for a specific account, joining with currency data, and calculating a running balance that sums Debit - Credit for all prior (and current) transactions ordered by EntryNumber and ID.

1. Direct 1:1 Translation of Your Original SQL #

This approach mirrors your nested subquery directly in LINQ, which will generate SQL very similar to your original code:

// Define your account GUID as a variable for reusability
var targetAccountGuide = "D23265D8-E39A-4AF0-B652-0608FA61A866";

var query = from mainQ in db.Qry013
            // Join with TBL001 matching the currency guide
            join currency in db.TBL001 
              on mainQ.CurrencyGuide equals currency.CardGuide
            // Apply your filter conditions
            where mainQ.AccountGuide == targetAccountGuide && mainQ.Posted == 1
            select new 
            {
                mainQ.Debit,
                mainQ.Credit,
                // Calculate running balance using a nested subquery
                Balance = db.Qry013
                            .Where(subQ => 
                                subQ.AccountGuide == targetAccountGuide &&
                                subQ.EntryNumber <= mainQ.EntryNumber &&
                                subQ.ID <= mainQ.ID)
                            // Cast to nullable decimal to handle cases where there are no prior rows
                            .Sum(subQ => (decimal?)(subQ.Debit - subQ.Credit)) ?? 0,
                // Map ISNULL to C#'s null-coalescing operator ??
                Source = mainQ.Entryname ?? mainQ.InvoiceName,
                TransactionNumber = mainQ.EntryNumber ?? mainQ.BillNumber,
                CurrencyName = currency.CurrencyShortcut,
                mainQ.Rate,
                mainQ.CostCenterName,
                mainQ.BranchName,
                mainQ.EntryNote
            };

Key Notes for This Version: #

• The Balance property uses a nested Where + Sum to replicate your subquery logic. We cast to decimal? to avoid an exception if there are no prior transactions (since Sum on an empty set would return null, and we use ?? 0 to default that to 0).
• ISNULL in SQL translates directly to C#'s ?? null-coalescing operator for the Source and TransactionNumber fields.
• The join clause matches your SQL's INNER JOIN exactly.

2. Optimized Version with Window Functions (EF Core 3.0+) #

If you're using EF Core 3.0 or later, you can leverage SQL window functions for better performance. This replaces the nested subquery with a single scan of the table instead of running a subquery per row:

var targetAccountGuide = "D23265D8-E39A-4AF0-B652-0608FA61A866";

var query = from mainQ in db.Qry013
            join currency in db.TBL001 
              on mainQ.CurrencyGuide equals currency.CardGuide
            where mainQ.AccountGuide == targetAccountGuide && mainQ.Posted == 1
            // Order by the same fields used in your balance calculation
            orderby mainQ.EntryNumber, mainQ.ID
            select new 
            {
                mainQ.Debit,
                mainQ.Credit,
                // Use EF's SumOver window function to calculate running balance
                Balance = EF.Functions.SumOver(
                    mainQ.Debit - mainQ.Credit,
                    OrderBy(mainQ.EntryNumber).ThenBy(mainQ.ID)
                ),
                Source = mainQ.Entryname ?? mainQ.InvoiceName,
                TransactionNumber = mainQ.EntryNumber ?? mainQ.BillNumber,
                CurrencyName = currency.CurrencyShortcut,
                mainQ.Rate,
                mainQ.CostCenterName,
                mainQ.BranchName,
                mainQ.EntryNote
            };

Why This Is Better: #

• Window functions are far more efficient for running totals, especially with large datasets. Your original SQL would run a subquery for every row, while this version does a single pass over the data.
• The generated SQL will use SUM() OVER (ORDER BY EntryNumber, ID) which is exactly the optimized approach you'd write by hand in SQL.

Important: #

You'll need to have using Microsoft.EntityFrameworkCore; at the top of your file to access EF.Functions.SumOver.

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