CODEWITHSUNDEEP
c#sqlsql-serverlinq
Buğra Ekuklu
Buğra Ekuklu

Reputation: 3278

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

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.

Upvotes: 0

Views: 1145

Answers (1)

Buğra Ekuklu
Buğra Ekuklu

Reputation: 3278

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;

Upvotes: 1

Related Questions