CODEWITHSUNDEEP
c#sqllinq
e-on
e-on

Reputation: 1605

multiple left joins with Linq

I'm struggling with a Linq query involving left joins. Here is the SQL I'm trying to convert:

SELECT  EmailStats.EmailAddress, EmailVoting.DateAdded, EmailVoting.ResponseText, 
        EmailStats.DateSent, EmailAlerts.Name, UserDetails.EmployeeNumber
FROM    EmailAlerts 
INNER JOIN EmailStats 
    ON     EmailAlerts.EmailAlertID = EmailStats.EmailAlertID 
INNER JOIN UserDetails 
    ON     EmailStats.UserId = UserDetails.UserId 
LEFT OUTER JOIN EmailVoting 
    ON     EmailAlerts.EmailAlertID = EmailVoting.EmailAlertID 
    AND    EmailVoting.UserId = EmailStats.UserId 
Where EmailAlerts.EmailAlertID = 43 AND EmailStats.IsTestSend = 0

The SQL is returning the correct data with the EmailVoting fields null if the rows don't exist. Below is the LINQ I currently have:

from ea in db.EmailAlerts
join es in db.EmailStats on ea.EmailAlertID equals es.EmailAlertID
join ud in db.UserDetails on es.UserId equals ud.UserId
join ev in db.EmailVoting on ea.EmailAlertID equals ev.EmailAlertID into vm
from v in vm.DefaultIfEmpty()
join ev in db.EmailVoting on es.UserId equals ev.UserId into udItems
from u in udItems.DefaultIfEmpty()
where v.EmailAlertID == emailAlertID

What I thought was the same in LINQ, isn't displaying correctly and is in fact displaying an entry with a different EmailAlertID. Anyone see where I might be going wrong?

Thanks

Upvotes: 1

Views: 96

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

from ea in db.EmailAlerts
join es in db.EmailStats on ea.EmailAlertID equals es.EmailAlertID
join ud in db.UserDetails on es.UserId equals ud.UserId
join ev in db.EmailVoting on new {ev.EmailAlertID, ev.UserId} equals new {ea.EmailAlertID, es.UserId} into vm
from v in vm.DefaultIfEmpty()
where v.EmailAlertID == emailAlertID

Upvotes: 3

Related Questions