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, Dec 17 '14 at 05:52
Maybe this link would be helpful for you: http://msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin — Vokinneberg, Dec 17 '14 at 05:53
[LinqPad](http://www.linqpad.net/) is also a great tool for converting to/from Linq. — Reddog, Dec 17 '14 at 05:54
Use this http://www.sqltolinq.com/downloads to convert your sql queries to LINQ — Mox Shah, Dec 17 '14 at 06:15

score 3 · Accepted Answer · edited May 23 '17 at 12:05

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? — OverMars, Dec 18 '14 at 17:44
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 http://stackoverflow.com/a/5418534/314291 and http://stackoverflow.com/questions/21954520/creating-many-to-many-relationships-using-fluent-api-in-entity-framework — StuartLC, Dec 18 '14 at 17:50
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? — OverMars, Dec 18 '14 at 17:56
Assuming Code first, this will create a N:N junction table in Sql, and and inform EF of the relationship: http://stackoverflow.com/a/26638150/314291 — StuartLC, Dec 18 '14 at 18:02

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

1 Answers1