Linq中传递引用同一表两条关联记录的筛选查询方案问询

Got it, let's tackle this problem. You want to work with related pairs of CustomerTransaction records (a self-join on the table) and pass a filter that can reference both records in the pair—here's a robust, EF-friendly implementation that keeps query translation intact:

1. Define a Method Accepting a Dual-Parameter Expression #

The core change is switching from a single-parameter expression to Expression<Func<CustomerTransaction, CustomerTransaction, bool>>. This lets the caller write logic that compares or filters both records in the pair. We'll also use an expression visitor to ensure EF can translate the query to SQL properly (no client-side evaluation surprises):

// Helper class to align expression parameters with our query's aliases
public class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _oldParam;
    private readonly ParameterExpression _newParam;

    public ParameterReplacer(ParameterExpression oldParam, ParameterExpression newParam)
    {
        _oldParam = oldParam;
        _newParam = newParam;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == _oldParam ? _newParam : base.VisitParameter(node);
    }
}

// Your updated data retrieval method
void GetRelatedTransactionPairs(Expression<Func<CustomerTransaction, CustomerTransaction, bool>> pairFilter)
{
    // Create parameters matching the query's record aliases (t1 = first record, t2 = second)
    var t1Param = Expression.Parameter(typeof(CustomerTransaction), "t1");
    var t2Param = Expression.Parameter(typeof(CustomerTransaction), "t2");

    // Replace the filter's parameters with our query-specific ones
    var adjustedFilterBody = new ParameterReplacer(pairFilter.Parameters[0], t1Param)
        .Visit(pairFilter.Body);
    adjustedFilterBody = new ParameterReplacer(pairFilter.Parameters[1], t2Param)
        .Visit(adjustedFilterBody);

    // Build a lambda that works with our query parameters
    var filterLambda = Expression.Lambda<Func<CustomerTransaction, CustomerTransaction, bool>>(
        adjustedFilterBody, t1Param, t2Param);

    // Execute the self-join query with the custom filter
    var query = CustomerTransaction
        .SelectMany(t1 => CustomerTransaction, filterLambda)
        .Select(result => new 
        { 
            FirstTransaction = result.Item1, 
            SecondTransaction = result.Item2 
        })
        .Take(2);

    query.Dump();
}

2. Call the Method with a Pair-Filtering Expression #

Now you can pass a filter that references both records in the pair. For example, to get pairs of transactions for the same customer where the first transaction's amount is less than the second:

Expression<Func<CustomerTransaction, CustomerTransaction, bool>> pairQuery = (t1, t2) =>
    t1.CustomerID == t2.CustomerID 
    && t1.Amount < t2.Amount 
    && t1.TransactionID != t2.TransactionID; // Avoid matching a record to itself

GetRelatedTransactionPairs(pairQuery);

3. Optional: Explicit Join for Better Performance #

If you want to enforce a specific join key (like CustomerID) instead of a flexible Cartesian product + filter, you can modify the method to separate join logic from post-join filtering:

void GetRelatedPairsWithExplicitJoin<TKey>(
    Expression<Func<CustomerTransaction, TKey>> joinKeySelector,
    Expression<Func<CustomerTransaction, CustomerTransaction, bool>> postJoinFilter)
{
    var query = CustomerTransaction
        .Join(
            inner: CustomerTransaction,
            outerKeySelector: joinKeySelector,
            innerKeySelector: joinKeySelector,
            resultSelector: (t1, t2) => new { t1, t2 })
        .Where(joined => postJoinFilter.Compile()(joined.t1, joined.t2)) // Use expression replacement here too for full EF translation
        .Select(joined => new 
        { 
            FirstTransaction = joined.t1, 
            SecondTransaction = joined.t2 
        })
        .Take(2);

    query.Dump();
}

Call it like this:

GetRelatedPairsWithExplicitJoin(
    joinKeySelector: t => t.CustomerID,
    postJoinFilter: (t1, t2) => t1.TransactionDate < t2.TransactionDate);

Key Notes #

• Avoid Compile() for large datasets: The ParameterReplacer ensures EF translates the entire query to SQL, preventing slow client-side evaluation.
• Flexibility: The first method lets you define any kind of pair relationship (not just same-customer), while the explicit join method is more performant for known, key-based relationships.

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