Convert SQL statement to Linq Statement (3 table left joins + where)

Question

This is the working sql I need converted to Linq:

select * from dbo.Pod p left join dbo.PodEvent pe on p.PodId = pe.Pod_PodId left join dbo.Event e on pe.Event_EventId = e.EventId where e.StartDateTime >= '2014-12-24 09:00:00.000' and e.EndDateTime <= '2014-12-24 14:30:00.000'

I have been trying suggestions from different posts all day and this is the best I could do and the issue is that its an inner join, where I need it to be a left join so I can get all the pods for given time interval:

List<Pod> pods = (from p in db.Pods join pe in db.PodEvents on p.PodId equals pe.Pod.PodId join e in db.Events on pe.Event.EventId equals e.EventId where e.StartDateTime == userInfo.StartDateTime && e.EndDateTime <= userInfo.EndDateTime select p).ToList();

Thanks

Thanks for the suggestion, I have tried a few examples but then I have issues converting my results to a list... would you know of any material? — OverMars
– OverMars, Commented Dec 17, 2014 at 5:52
Maybe this link would be helpful for you: msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin — Vokinneberg
– Vokinneberg, Commented Dec 17, 2014 at 5:53
Use this sqltolinq.com/downloads to convert your sql queries to LINQ — Mox Shah
– Mox Shah, Commented Dec 17, 2014 at 6:15

Community · Accepted Answer · 2017-05-23 12:05:52Z

Given that the WHERE filter in your SQL will effectively render the Left Outer Join redundant, as any failed JOINS will be filtered out by the WHERE clause, you could use this fact to manually project an INNER JOIN through the junction table with no navigation (and the performance is likely to be awful):

var pods = db.Events.Where(e => e.StartDateTime >= userInfo.StartDateTime && e.EndDateTime < userInfo.EndDateTime) .Join(db.PodEvents, e => e.EventID, pe => pe.EventId, new (e, pe) => { Event = e, PodEvent = pe, Pod = db.Pods.Single(p => p.PodId == pe.PodID) }) .SelectMany(x => x.Pod);

However, given that you have navigation through pe.Event.EventId, why not fix the navigation on all three tables, which will allow the much simpler:

var pods = db.Events.Where(e => e.StartDateTime >= userInfo.StartDateTime && e.EndDateTime < userInfo.EndDateTime) .SelectMany(e => e.PodEvents.Select(pe => pe.Pod));

Also, if PodEvent is just a Junction Table (EventId, PodId) - by modelling this as a Many:Many in EF you can avoid the PodEvent junction entity altogether, and Event and Pod will become directly navigable.

Thank you so much for such a detailed answer! I have the ability to convert my junction table to many::many relationship, and I will. You mentioned a navigation issue, any idea how I could go about resolving it? or will removing the junction table fix the issue?
In Sql, you must have the junction table, but in EF, provided you just need the N:N keys in the table, you can navigate through it as if it was invisible - See stackoverflow.com/a/5418534/314291 and stackoverflow.com/questions/21954520/…
Please forgive my ignorance. Do you mean that if I do everything through EF (which I am), then I don't need my junction table? or I need my junction table on the sql side but I can have EF think it doesn't exist?
Assuming Code first, this will create a N:N junction table in Sql, and and inform EF of the relationship: stackoverflow.com/a/26638150/314291

Collectives™ on Stack Overflow

Convert SQL statement to Linq Statement (3 table left joins + where)

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related