How to Perform Join Between Multiple Tables in Linq Lambda

How to perform Join between multiple tables in LINQ lambda

For joins, I strongly prefer query-syntax for all the details that are happily hidden (not the least of which are the transparent identifiers involved with the intermediate projections along the way that are apparent in the dot-syntax equivalent). However, you asked regarding Lambdas which I think you have everything you need - you just need to put it all together.

var categorizedProducts = product
    .Join(productcategory, p => p.Id, pc => pc.ProdId, (p, pc) => new { p, pc })
    .Join(category, ppc => ppc.pc.CatId, c => c.Id, (ppc, c) => new { ppc, c })
    .Select(m => new { 
        ProdId = m.ppc.p.Id, // or m.ppc.pc.ProdId
        CatId = m.c.CatId
        // other assignments
    });

If you need to, you can save the join into a local variable and reuse it later, however lacking other details to the contrary, I see no reason to introduce the local variable.

Also, you could throw the Select into the last lambda of the second Join (again, provided there are no other operations that depend on the join results) which would give:

var categorizedProducts = product
    .Join(productcategory, p => p.Id, pc => pc.ProdId, (p, pc) => new { p, pc })
    .Join(category, ppc => ppc.pc.CatId, c => c.Id, (ppc, c) => new {
        ProdId = ppc.p.Id, // or ppc.pc.ProdId
        CatId = c.CatId
        // other assignments
    });

...and making a last attempt to sell you on query syntax, this would look like this:

var categorizedProducts =
    from p in product
    join pc in productcategory on p.Id equals pc.ProdId
    join c in category on pc.CatId equals c.Id
    select new {
        ProdId = p.Id, // or pc.ProdId
        CatId = c.CatId
        // other assignments
    };

Your hands may be tied on whether query-syntax is available. I know some shops have such mandates - often based on the notion that query-syntax is somewhat more limited than dot-syntax. There are other reasons, like "why should I learn a second syntax if I can do everything and more in dot-syntax?" As this last part shows - there are details that query-syntax hides that can make it well worth embracing with the improvement to readability it brings: all those intermediate projections and identifiers you have to cook-up are happily not front-and-center-stage in the query-syntax version - they are background fluff. Off my soap-box now - anyhow, thanks for the question. :)

lambda expression join multiple tables with select and where clause

If I understand your questions correctly, all you need to do is add the .Where(m => m.r.u.UserId == 1):

var userInRole = db.UserProfiles.
    Join(db.UsersInRoles, u => u.UserId, uir => uir.UserId,
    (u, uir) => new { u, uir }).
    Join(db.Roles, r => r.uir.RoleId, ro => ro.RoleId, (r, ro) => new { r, ro })
    .Where(m => m.r.u.UserId == 1)
    .Select (m => new AddUserToRole
    {
        UserName = m.r.u.UserName,
        RoleName = m.ro.RoleName
    });

Hope that helps.

Multiple table join using lambda/linq c# with DTO

I think part of the reason you are seeing 9 records is because the syntax you are using is the one for left outer joins in Linq.

What might work is using subqueries to get the data you want in a format you want it.

For example

var res = from cpr in db.CiscoPhoneReport
          join app in db.ApplicationSummary on cpr.PhoneReportID equals app.PhoneReportID
          where cpr.PhoneReportID == phoneReportID
          select new PhoneReport
          {
              AppSummary = new AppSummary
              {
                  // Mappings
              },
              CSQModel = (from model in db.CSQActivityReport
                          where model.PhoneReportId == phoneReportID
                          select new CSQModel
                          {
                              // Mappings
                          }).ToList()
          }

You were right that you need the CSQModels to be some sort of collection, be it a List or even a basic ICollection of type CSQModel. You can write another sub query for the CallDistributionSummary as needed.

Left join multiple tables using lambda expression

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

---

Related Topics