Multiple conditions on a left outer join clause in a LINQ query

Question

I would like to write the following SQL query with LINQ syntax to understand the fundamentals of LINQ queries.

SELECT q.*, qpph.*
  FROM [Questions] AS q
  LEFT OUTER JOIN [QuestionPoolPickHandles] AS qpph
    ON qpph.QuestionId = q.Id AND qpph.PickerId = 100
  WHERE qpph.Id IS NULL;

How can I apply left outer join and a condition in its ON clause at the same time using LINQ syntax? From my readings, it seems it is likely to be not possible.

Here's what I've tried so far.

var result = from q in context.Questions
             join qpph in context.PoolPickHandles
               on q.Id equals qpph.PickerId into Handles  // notice the 'qpph.PickerId = 100' is absent
             from m in Handles.DefaultIfEmpty()
             where m == null
             select q;

Any further elaboration will be appreciated.

See https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/basic-linq-query-operations#joining for some clues. — jwdonahue, Feb 28 '20 at 18:30
@KurtHamilton updated my question to show what I've done so far. — Buğra Ekuklu, Feb 28 '20 at 18:36
@jwdonahue thanks for the suggestion. Sadly, the example snippets are too superficial, or at least I'm not able to understand what I need. I would be glad if you can point me out. — Buğra Ekuklu, Feb 28 '20 at 18:37
https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause#left-outer-join — Train, Feb 28 '20 at 18:39
To perform a left outer join in LINQ, use the `DefaultIfEmpty `method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. so `Handles.DefaultIfEmpty(new Questions{ field1 = String.Empty, field 2= 0 })` I know field names and values are incorrect but the syntax should work. — Train, Feb 28 '20 at 18:42
@Train thank you for the suggestion, the syntax works. However, I'm still unable to add `qpph.PickerId = 100` condition to `LEFT OUTER JOIN`'s `ON` clause. Is there a way to do that? — Buğra Ekuklu, Feb 28 '20 at 18:52
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) will help you. — NetMage, Feb 28 '20 at 20:08
@RobertRichter If it's on a where clause, the query would only get those elements. However, I would like to eliminate the joined fields on the where clause with a `qpph.Id IS NULL`. — Buğra Ekuklu, Feb 29 '20 at 21:20

score 1 · Accepted Answer · answered Feb 29 '20 at 21:26

With the help of the comments on the question, I've figured out writing the query using LINQ syntax.

from q in context.Questions
join qpph in context.PoolPickHandles
  on new { questionId = q.Id, pickerId = user.Id } 
    equals new { questionId = qpph.QuestionId, pickerId = qpph.PickerId } 
      into Handles
from m in Handles.DefaultIfEmpty()
where m == null
select q;

Multiple conditions on a left outer join clause in a LINQ query

1 Answers1