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.