Rustin Cohle Rustin Cohle - 21 days ago 9
C# Question

Left join multiple tables using lambda expression

I have 2 tables:


specs {specId, desc, createdby, lastupdatedby}

users {userid, username}


I want the below linq query need to be written in pure lambda expression

from spec in specs
from user in users.where(x => x.userId== spec.createdby).DefaultIfEmpty()
from updatedUser in users.where(x => x.userId== spec.lastupdatedbyby).DefaultIfEmpty()
select new {
spec = spec
user = user,
updatedUser = updatedUser
}


Please assist.

Data would be like say:

spec[{1, test, 1234, 2345},{2, test1, 1234, null}]

users[{1234, Allen},{2345, Dwayne}]


So the result should be

[{1, test, Allen, Dwayne}, {2, test1, Allen, null}]

Answer

Let's start with these classes:

class Specs {
    public int specId { get; set; }
    public string desc { get; set; }
    public int createdby { get; set; }
    public int lastupdatedby { get; set; }
}

class Users {
    public int userId { get; set; }
    public string username { get; set; }
}

class UpdatedUser {
    public int userId {get; set;}
    public string username { get; set; }
}

Now the Linq query, for convenience I have created some example data:

var specs = new Specs[]
{
    new Specs{specId = 1, desc = "Spec1", createdby=1, lastupdatedby=1},
    new Specs{specId = 2, desc = "Spec2", createdby=2, lastupdatedby=3},    
    new Specs{specId = 3, desc = "Spec3", createdby=3, lastupdatedby=1},
    new Specs{specId = 4, desc = "Spec4", createdby=3, lastupdatedby=3},
};

var user = new Users[]
{
    new Users{userId = 1, username = "User1"},
    new Users{userId = 2, username = "User2"},
};

var updatedUser = new UpdatedUser[]
{
    new UpdatedUser{userId = 1, username = "UpdatedUser1"},
    new UpdatedUser{userId = 2, username = "UpdatedUser2"},         
};

var result = specs
    .GroupJoin(user, 
        s => s.createdby,
        u => u.userId,
    (s, u) => u.Select(x => new {spec = s, user = x})
            .DefaultIfEmpty(new {spec = s, user = (Users)null}))
.SelectMany(g => g)
.GroupJoin(updatedUser,
        firstJoin => firstJoin.spec.lastupdatedby,
        uu => uu.userId,
        (firstJoin, uu) => 
        uu.Select(y => new {spec = firstJoin.spec, user = firstJoin.user, updatedUser = y})
.DefaultIfEmpty(new {spec = firstJoin.spec, user = firstJoin.user, updatedUser = (UpdatedUser) null}))
    .SelectMany(g1 => g1)
    .ToList();

The GroupJoin extension method help you obtain a tuple with all the elements of the starting table with a list of elements of the joined table.

Now if you enumerate the results:

result.ForEach(item => {
    Console.WriteLine(item.spec.desc);
    Console.WriteLine(item.user != null ? item.user.username : "NULL");
    Console.WriteLine(item.updatedUser != null ? item.updatedUser.username : "NULL");
    Console.WriteLine();
});

You obtain this:

Spec1
User1
UpdatedUser1

Spec2
User2
NULL

Spec3
NULL
UpdatedUser1

Spec4
NULL
NULL