将带WITH子句的T-SQL递归查询转换为LINQ表达式

Got it, let's break down how to convert your recursive T-SQL CTE to equivalent LINQ. First, let's start with a clear example of your original T-SQL logic to make sure we're aligned:

WITH acc AS (
    -- Anchor member: Select root accounts (acc_parent is NULL) and set [root] to their account number
    SELECT 
        acc_number,
        acc_balance,
        acc_parent,
        acc_number AS [root]
    FROM Accounts
    WHERE acc_parent IS NULL

    UNION ALL

    -- Recursive member: Join child accounts to their parent, inherit the parent's [root] value
    SELECT 
        child.acc_number,
        child.acc_balance,
        child.acc_parent,
        parent.[root]
    FROM Accounts child
    INNER JOIN acc parent ON child.acc_parent = parent.acc_number
)
-- Aggregate by root to get total balance per root account
SELECT 
    [root] AS RootAccountNumber,
    SUM(acc_balance) AS TotalBalance
FROM acc
GROUP BY [root]

Option 1: LINQ to Objects (In-Memory Data) #

If your account data is already loaded into memory (e.g., a List<Account>), you can use a recursive method to traverse each root's hierarchy, then aggregate the results:

// Assume your Account class looks like this:
public class Account
{
    public string AccNumber { get; set; }
    public decimal AccBalance { get; set; }
    public string AccParent { get; set; } // Null for root accounts
}

// Helper class to track each account with its root account number
public class AccountWithRoot
{
    public string AccNumber { get; set; }
    public decimal AccBalance { get; set; }
    public string RootAccountNumber { get; set; }
}

// Your main logic
var allAccounts = GetYourAccountsList(); // Replace with your in-memory data source

// Get all root accounts first
var rootAccounts = allAccounts.Where(a => a.AccParent == null);

// Recursive method to fetch all descendants (including the root) with their root account
IEnumerable<AccountWithRoot> GetAccountHierarchy(Account root)
{
    // Return the root itself with its own number as the root
    yield return new AccountWithRoot
    {
        AccNumber = root.AccNumber,
        AccBalance = root.AccBalance,
        RootAccountNumber = root.AccNumber
    };

    // Get direct children of the current account
    var children = allAccounts.Where(a => a.AccParent == root.AccNumber);
    foreach (var child in children)
    {
        // Recursively fetch children of this child, inheriting the original root's number
        foreach (var descendant in GetAccountHierarchy(child))
        {
            yield return descendant;
        }
    }
}

// Flatten all hierarchies into a single collection
var accountsWithRoot = rootAccounts.SelectMany(root => GetAccountHierarchy(root));

// Aggregate to get total balance per root account
var finalResult = accountsWithRoot
    .GroupBy(a => a.RootAccountNumber)
    .Select(g => new
    {
        RootAccountNumber = g.Key,
        TotalBalance = g.Sum(a => a.AccBalance)
    })
    .ToList();

Option 2: EF Core (Database-Side Recursion, EF Core 3.1+) #

If you're using Entity Framework Core 3.1 or later, you can leverage its built-in recursive CTE support to translate the logic directly to the database (matching your original T-SQL behavior):

using Microsoft.EntityFrameworkCore;

// In your DbContext, you should have a DbSet<Account> Accounts { get; set; }

// Define the recursive CTE
var recursiveCTE = context.Accounts
    .WithRecursiveCTE(acc =>
        // Anchor member: Select root accounts
        acc.StartWith(a => a.AccParent == null)
            .Select(a => new 
            { 
                a.AccNumber, 
                a.AccBalance, 
                a.AccParent, 
                RootAccountNumber = a.AccNumber 
            })
        // Recursive member: Join children to their parent and inherit the root
        .UnionAll((parent, child) =>
            context.Accounts
                .Where(c => c.AccParent == parent.AccNumber)
                .Select(c => new 
                { 
                    c.AccNumber, 
                    c.AccBalance, 
                    c.AccParent, 
                    RootAccountNumber = parent.RootAccountNumber 
                })
        )
    );

// Aggregate the results just like your T-SQL
var finalResult = recursiveCTE
    .GroupBy(a => a.RootAccountNumber)
    .Select(g => new
    {
        RootAccountNumber = g.Key,
        TotalBalance = g.Sum(a => a.AccBalance)
    })
    .ToList();

This EF Core approach will generate SQL almost identical to your original CTE, keeping the recursion logic on the database side for efficiency.

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