How to do a full outer join in Linq?

I think I have the answer here, which is not as elegant as I’d hoped, but it should do the trick: var studentIDs = StudentClasses.Select(sc => sc.StudentID) .Union(StudentTeachers.Select(st => st.StudentID); //.Distinct(); — Distinct not necessary after Union var q = from id in studentIDs join sc in StudentClasses on id equals sc.StudentID into jsc from … Read more

Top 1 with a left join

Use OUTER APPLY instead of LEFT JOIN: SELECT u.id, mbg.marker_value FROM dps_user u OUTER APPLY (SELECT TOP 1 m.marker_value, um.profile_id FROM dps_usr_markers um (NOLOCK) INNER JOIN dps_markers m (NOLOCK) ON m.marker_id= um.marker_id AND m.marker_key = ‘moneyBackGuaranteeLength’ WHERE um.profile_id=u.id ORDER BY m.creation_date ) AS MBG WHERE u.id = ‘u162231993’; Unlike JOIN, APPLY allows you to reference … Read more

LINQ to SQL – Left Outer Join with multiple join conditions

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax: from p in context.Periods join f in context.Facts on p.id equals f.periodid into fg from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty() where p.companyid == 100 select f.value Or you could use a subquery: from p in context.Periods … Read more

Oracle “(+)” Operator

That’s Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn’t standardize OUTER joins. The query would be re-written in ANSI-92 syntax as: SELECT … FROM a LEFT JOIN b ON b.id = a.id This link is pretty good at explaining the … Read more