在Linq查询中传递引用两个联接记录的选择查询

Question

假设我有一个名为CustomerTransaction的表，我可以将选择条件传递给类似

void GetData1(Expression<Func<CustomerTransaction, bool>> selection)

{

    CustomerTransaction.Where(selection).Take(2).Dump();

}

使用类似的代码

Expression<Func<CustomerTransaction, bool>> query = r => (r.CustomerID == 1);

GetData1(query);

但是，我希望能够联接此表中的两个记录并传递引用这两个表的选择条件。

例如，以下将编译（在LinqPad中），

void GetData2(Expression<Func<Tuple<CustomerTransaction,CustomerTransaction>, bool>> selection)

{

   var baseQuery = (from ct1 in CustomerTransaction 

        join ct2 in CustomerTransaction on ct1.MasterTransactionID equals ct2.RelatedTransactionID 

        select new Tuple<CustomerTransaction, CustomerTransaction> ( ct1, ct2)) ;

    var query = baseQuery.Where(selection).Dump();

}

Expression<Func<Tuple<CustomerTransaction,CustomerTransaction>, bool>> query2 =

       r => r.Item1.CustomerID != r.Item2.CustomerID;

GetData2(query2);

但是，它不会运行，因为SQL无法处理元组。

如果我尝试以SQL可以理解的方式编写此代码，那么我将无法通过选择标准，因为我不知道选择的类型

例如

void GetData3( ???  selection)

{

    var baseQuery = (from ct1 in CustomerTransaction

     join ct2 in CustomerTransaction on ct1.MasterTransactionID equals ct2.RelatedTransactionID

     select new { Customer1 = ct1.CustomerID, Customer2 = ct2.CustomerID }  );

    baseQuery.Where(selection).Dump();

}

显然，实际查询更为复杂，并且它们是多个选择条件，因此，我试图避免为每个可能的部分条件重复查询。

有没有办法解决？

Answer 1

您可以创建一个表示您的join结果的显式类，并Where在该类上创建表达式：public class CT2 {&nbsp; &nbsp; public CustomerTransaction ct1;&nbsp; &nbsp; public CustomerTransaction ct2;}然后，您可以在查询和Where表达式中使用该类：void GetData2(Expression<Func<CT2, bool>> selection) {&nbsp; &nbsp; var baseQuery = from ct1 in db.CustomerTransaction&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join ct2 in db.CustomerTransaction on ct1.MasterTransactionID equals ct2.RelatedTransactionID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select new CT2 { ct1 = ct1, ct2 = ct2 };&nbsp; &nbsp; var query = baseQuery.Where(selection).Dump();}Expression<Func<CT2, bool>> query2 =&nbsp; &nbsp; &nbsp; &nbsp;r => r.ct1.CustomerID != r.ct2.CustomerID;GetData2(query2);