CODEWITHSUNDEEP
sqllinqentity-frameworkleft-joinwhere-clause
OverMars
OverMars

Reputation: 1049

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

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

Upvotes: 2

Views: 98

Answers (1)

StuartLC
StuartLC

Reputation: 107267

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.

Upvotes: 3

Related Questions