1

There are a bunch of examples of how to make a quasi-left join in Linq here (this, also this, and even this). They all point to joining a handful of parent tables into an "outer" table, then selecting from that table using DefaultIfEmpty(). Sounds easy enough, so I created this snippet in LinqPad:

dim foo = (
    from c in context.Contacts
    join p in context.Plants on c.PlantID equals p.PlantID into outer
    from o in outer.DefaultIfEmpty()
    where p.PlantCode = 2
    select c.ContactName, p.PlantName
)

The equivalent SQL statement would be: 

SELECT c.ContactName, p.PlantName
FROM Contacts c
LEFT JOIN Plants p ON c.PlantID = p.PlantID
WHERE p.PlantCode = 2

Looks like it should work, but LinqPad keeps stopping on the join...into line with this error: ')' expected.

Am I doing something wrong? Or is LinqPad losing its mind?

Community
  • 1
  • 1
tmountjr
  • 1,423
  • 2
  • 22
  • 38
  • I assume you're using VB.NET since you're using the `Dim` keyword. If so please tag your question to indicate VB.NET. – Ahmad Mageed Aug 14 '13 at 15:12
  • Your query doesn't make sense. The condition 'where p.PlantCode = 2' negates the effect of the outer join and turns it into an inner join. What are you actually trying to do? – Joe Albahari Aug 15 '13 at 03:50
  • I wasn't debugging the query; I was debugging why my LINQ left join wasn't working. The example query was just there to demonstrate a left join that failed, and whether or not it makes sense from a query standpoint, it made enough sense from a syntax standpoint that Ahmad was able to point me in the right direction. – tmountjr Aug 15 '13 at 14:13
  • Ah. It's just that there's a much easier way to do a left outer join in LINQ, but it will work only if the query is semantically valid. Would you like me fix your query and then post it? – Joe Albahari Aug 16 '13 at 01:49
  • Sure, drop it as an answer below. – tmountjr Aug 16 '13 at 13:22

1 Answers1

1

Unlike C#, VB.NET expects a Group Join clause to perform a left outer join, along with the Group keyword. Check out the MSDN documentation, specifically the "Perform a Left Outer Join by Using the Group Join Clause" section.

Try the following query:

From c In context.Contacts
Group Join p In context.Plants On c.PlantID Equals p.PlantID Into outer = Group
From o In outer.DefaultIfEmpty()
Where o.PlantCode = 2
Select c.ContactName, p.PlantName

EDIT: you should use o.PlantCode instead of p.PlantCode since the p variable won't be in scope when querying against the grouping.

Notice the use of outer = Group, which is the way to alias the grouping. Alternately, if you don't care to alias it, you could write the query against Group directly:

From c In context.Contacts
Group Join p In context.Plants On c.PlantID Equals p.PlantID Into Group
From o In Group.DefaultIfEmpty()
Where o.PlantCode = 2
Select c.ContactName, p.PlantName
Ahmad Mageed
  • 94,561
  • 19
  • 163
  • 174
  • 1
    Thanks, Ahmad, that almost worked with one trivial problem - once I group that table I can't refer to it by its original alias `p`; instead I have to refer to it from its group alias `o`. So instead of `p.PlantCode` or `p.PlantName` I have to use `o.PlantCode` and `o.PlantName`. The error was pretty easy to find and fix, but I figured I'd include it here in case anyone else hit it. – tmountjr Aug 14 '13 at 15:20
  • @mounty yep that's correct. I just wrote a comment before I saw yours. I'll update my post. – Ahmad Mageed Aug 14 '13 at 15:22