Left/right join with several joins in the query

Question

I have a reference table, in this table 3 fields (Id, User1, User2). The field User2 can be nullable, but we use 0 when it's not used.

When I executed the Linq query below, the records where User2 == 0 are not part of the result.

How can I do a left/right join in this case ?

class MyReference
{
    public int Id { get; set; }
    public int User1 { get; set; }
    public int User2 { get; set; }
}

class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class MyClassResult
{
    public int Id { get; set; }
    public string U1FirstName { get; set; }
    public string U2FirstName { get; set; }
}

var result =
    (from myReference in context.MyReference
     join u1 in context.USER on myReference.User1 equals User.Id
     join u2 in context.USER on myReference.User2 equals User.Id
     select new MyClassResult
     {
         Id = myReference.Id,
         U1FirstName = u1.FirstName,
         U2FirstName = u2.FirstName
     }).ToList();

Answer 1

You must use into keyword and DefaultIfEmpty to get null elements :

var result = (from myReference in context.MyReference
                join u1 in context.USER on myReference.User1 equals u1.Id
                join u2 in context.USER on myReference.User2 equals u2.Id into x2
                from un2 in x2.DefaultIfEmpty()
                select
                    new MyClassResult {
                        Id = myReference.Id, 
                        U1FirstName = u1.FirstName, 
                        U2FirstName = un2 != null ? un2.FirstName : null
                    });

As an alternative, especially if myReference.User1 can be 0 too, you could use the let keyword to get user informations before final select :

var result = (from myReference in context.MyReference
            let u1 = users.FirstOrDefault(u => u.Id == myReference.User1)
            let u2 = users.FirstOrDefault(u => u.Id == myReference.User2)
            select
            new MyClassResult {
                Id = myReference.Id,
                U1FirstName = u1 != null ? u1.FirstName : null,
                U2FirstName = u2 != null ? u2.FirstName : null
            });