Linq to Entity Left Outer Join

Posted by radman on Stack Overflow See other posts from Stack Overflow or by radman
Published on 2010-03-16T13:07:33Z Indexed on 2010/03/17 14:41 UTC
Read the original article Hit count: 315

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about left-join

Related posts about linq-to-entities