SQL to Linq Lambda

Question

Does anybody know how to convert this on outerjoin on LINQ Lambda?

I wan to achieve this using lambda linq

SELECT * FROM Posts as A LEFT JOIN Reactions as B on A.Id = B.PostId AND @userId = b.userid

Here is my current linq code

 return await _dbContext.Posts
           .GroupJoin(_dbContext.Reactions,
           post => post.Id, reaction => reaction.PostId,
           (post, reactions) => new { post, reactions })
           .SelectMany(x => x.reactions.DefaultIfEmpty(),
           (post, reaction) => new { post.post, reaction })

Answer 1

What you want to accomplish can be done in two different ways in SQL, and those ways can be translated to Linq.

Depending on your scenario (volume of data, indexes, etc) you may want to need one or another

Option A: Join the filtered data

SELECT a.Name, b.* 
FROM 
    tableA 
    LEFT JOIN tableB on 
        b.Action='delete' AND a.Id = b.Id

would be translated in LINQ to something similar to:

var query =
    from a in db.TableA
    join pet in db.TableB.Where(x => x.Action=="delete") on a equals b.TableA into gj
    from leftJoined in gj.DefaultIfEmpty()

and using method syntax:

var query = tableA
    .GroupJoin(
        tableB.Where(x => x.Action == "delete"),
        tableA => tableA, 
        tableB => tableB.tableA,
        (tableA, tableBs) => new {tableA, tableBs}
    ).SelectMany(x => x.tableBs.DefaultIfEmpty())

Option B: Do the join and later filter the data

SELECT a.Name, b.* 
FROM 
    tableA 
    LEFT JOIN tableB on a.Id = b.Id 
WHERE 
    b.Id = NULL OR b.Action='delete'

would be translated to:

var query =
    from a in db.TableA
    join pet in db.TableB on a equals b.TableA into gj
    from leftJoined in gj.DefaultIfEmpty()
    where lefjoined == null || leftjoined.Action == "delete"

Answer 2

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

You can use this approach

Query Syntax:

var query = (from post in Posts
            join reaction in Reactions
            on post.Id equals reaction.PostId
            into reaction
            from reaction in reaction.DefaultIfEmpty()
            select new
            {
                post.Id,
                //prod.Foo1,
                //post.Foo2,
                //reaction.Foo3,
                //reaction.Foo4,
                //you can select other fields too
            }).OrderBy(ps => ps.Id);

For more information visit Perform left outer joins

Answer 3

Normally you don't. Flattening out related data like that is simply not necessary in LINQ. Just fetch the data with its natural shape:

_dbContext.Posts.Include(p => p.Reactions)

This returns the Posts and any reactions, without having to repeat the Post data for each Reaction, or having nulls for Posts without Reactions.