Linq to Entity Left Outer Join
- by radman
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.
