Linq to Entity 左外连接

Question

我有一个包含发票、AffiliateCommissions 和 AffiliateCommissionPayments 的实体模型。

发票到 AffiliateCommission 是一对多，AffiliateCommission 到 AffiliateCommissionPayment 也是一对多

我正在尝试进行查询，该查询将返回所有有佣金但不一定有相关佣金付款的发票。我想显示带有佣金的发票，无论它们是否有佣金付款。

查询看起来像这样：

using (var context = new MyEntitities())
{
   var invoices = from i in context.Invoices
   from ac in i.AffiliateCommissions
   join acp in context.AffiliateCommissionPayments on ac.affiliateCommissionID equals acp.AffiliateCommission.affiliateCommissionID 
   where ac.Affiliate.affiliateID == affiliateID
   select new
   {
      companyName = i.User.companyName,
      userName = i.User.fullName,
      email = i.User.emailAddress, 
      invoiceEndDate = i.invoicedUntilDate,
      invoiceNumber = i.invoiceNumber,
      invoiceAmount = i.netAmount,
      commissionAmount = ac.amount,
      datePaid = acp.paymentDate,
      checkNumber = acp.checkNumber
   };
   return invoices.ToList();
}

上面的查询仅返回带有 AffiliateCommissionPayment 的项目。

Answer 1

我不确定 EF 是否支持此功能（我也不确定您使用的是 EF2 还是 EF4），但这是 Linq2Sql 中的解决方案，因此可能值得尝试：

using (var context = new MyEntitities()) 
{ 
   var invoices = from i in context.Invoices 
   from ac in i.AffiliateCommissions 
   join acp in context.AffiliateCommissionPayments on ac.affiliateCommissionID equals acp.AffiliateCommission.affiliateCommissionID into acp_join
   from acp_join_default in acpg.DefaultIfEmpty()
   where ac.Affiliate.affiliateID == affiliateID 
   select new 
   { 
      companyName = i.User.companyName, 
      userName = i.User.fullName, 
      email = i.User.emailAddress,  
      invoiceEndDate = i.invoicedUntilDate, 
      invoiceNumber = i.invoiceNumber, 
      invoiceAmount = i.netAmount, 
      commissionAmount = ac.amount, 
      datePaid = acp.paymentDate, 
      checkNumber = acp.checkNumber 
   }; 
   return invoices.ToList(); 
} 

这里的主要更改是 into acpgjoin 之后的 code> 和 DefaultIfEmpty 行。

Answer 2

在 LINQ 中使用 join 几乎总是一个错误SQL 和 LINQ to Entities。

猜测从 AffiliateCommission 到 AffiliateCommissionPayment 的关联称为 Payment，您可以这样做：

using (var context = new MyEntitities())
{
   var invoices = from i in context.Invoices
   from ac in i.AffiliateCommissions
   where ac.Affiliate.affiliateID == affiliateID
   select new
   {
      companyName = i.User.companyName,
      userName = i.User.fullName,
      email = i.User.emailAddress, 
      invoiceEndDate = i.invoicedUntilDate,
      invoiceNumber = i.invoiceNumber,
      invoiceAmount = i.netAmount,
      commissionAmount = ac.amount,
      datePaid = (DateTime?) ac.Payment.paymentDate,
      checkNumber = (int?) ac.Payment.checkNumber
   };
   return invoices.ToList();
}

LINQ to SQL 和 LINQ to Entities 都将合并 null。强制转换是必要的，因为推断的类型将基于 AffiliateCommissionPayment. paymentDate 的类型，该类型可能不可为 null。如果是的话，你就不需要演员了。