Linq to Entity Left Outer Join

Question

Hi All,

I have an Entity model with Invoices, AffiliateCommissions and AffiliateCommissionPayments.

Invoice to AffiliateCommission is a one to many, AffiliateCommission to AffiliateCommissionPayment is also a one to many

I am trying to make a query that will return All Invoices that HAVE a commission but not necessarily have a related commissionPayment. I want to show the invoices with commissions whether they have a commission payment or not.

Query looks something like:

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();
}

This query above only returns items with an AffiliateCommissionPayment.

Answer 1

I'm not sure if EF supports this (nor am I sure if you are using EF2 or EF4), but this is the solution in Linq2Sql so it might be worth trying:

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(); 
} 

The main change here is the into acpg after your join, and the DefaultIfEmpty line.

Answer 2

It's almost always a mistake to use join in LINQ to SQL and LINQ to Entities.

Guessing that the association from AffiliateCommission to AffiliateCommissionPayment is called Payment, you can just do:

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 and LINQ to Entities will both coalesce nulls. The casts are necessary because the inferred type will be based on the type of AffiliateCommissionPayment.paymentDate, which might not be nullable. If it is, you don't need the cast.