How do you perform a left outer join with a where clause using linq lambda extension methods

Question

I have the following code in sql and I want to make the linq lambda query. Is there a way to achieve this?

SELECT *
FROM dbo.Idea i
    LEFT OUTER JOIN dbo.IdeaCollaborator ic
        ON ic.Idea_Id = i.Id
WHERE i.Submitter_Id = 'Peter'
      OR ic.User_Id = 'Peter';

I take a reference to How do you perform a left outer join using linq extension methods

and get

db.Ideas
 .GroupJoin(
   db.IdeaCollaborators,
   i => i.Id,
   ic => ic.Idea_Id,
   (x, y) => new { Ideas = x, IdeaCollaborators = y })
 .SelectMany(
   x => x.IdeaCollaborators.DefaultIfEmpty(),
   (x, y) => new
   { x.Ideas.Id, x.Ideas.IdeaStatus_Id, y.User_Id}
 )

But I got stuck

score 0 · Answer 1 · answered Jun 26 '19 at 21:57

The answer was the use of .FirstOrDefault() in the relation of the where, because in the relation the FirstOrDefault take the same item that we are going to take from the select so works for the where clause

db.Ideas
 .GroupJoin(
  db.IdeaCollaborators,
  i => i.Id,
  ic => ic.Idea_Id,
  (x, y) => new { Ideas = x, IdeaCollaborators = y })
 .Where(gj => gj.Ideas.Submitter_Id == "Peter" | gj.IdeaCollaborators.FirstOrDefault().User_Id == "Peter")
 .SelectMany(
  x => x.IdeaCollaborators.DefaultIfEmpty(),
  (x, y) => new
  { x.Ideas.Id, x.Ideas.IdeaStatus_Id, y.User_Id })
);

How do you perform a left outer join with a where clause using linq lambda extension methods

1 Answers1