title: Linq to Entity Left Outer Join
body: <p>I have an Entity model with Invoices, AffiliateCommissions and AffiliateCommissionPayments.</p>
  
  <p>Invoice to AffiliateCommission is a one to many, AffiliateCommission to AffiliateCommissionPayment is also a one to many</p>
  
  <p>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.</p>
  
  <p>Query looks something like:</p>
  
  <pre><code>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();
  }
  </code></pre>
  
  <p>This query above only returns items with an AffiliateCommissionPayment.</p>