How to perform Join between multiple tables in LINQ lambda

Question

I am trying to perform a Join between multiple tables in LINQ. I have the following classes:

Product {Id, ProdName, ProdQty}

Category {Id, CatName}

ProductCategory{ProdId, CatId} //association table

And I use the following code (where product, category and productcategory are instances of the above classes):

var query = product.Join(productcategory, p => p.Id, pc => pc.ProdID, (p, pc) => new {product = p, productcategory = pc})
                   .Join(category, ppc => ppc.productcategory.CatId, c => c.Id, (ppc, c) => new { productproductcategory = ppc, category = c});

With this code I obtain an object from the following class:

QueryClass { productproductcategory, category}

Where producproductcategory is of type:

ProductProductCategoryClass {product, productcategory}

I do not understand where the joined "table" is, I was expecting a single class that contains all the properties from the involved classes.

My aim is to populate another object with some properties resulting from the query:

CategorizedProducts catProducts = query.Select(m => new { m.ProdId = ???, m.CatId = ???, //other assignments });

how can I achieve this goal?

Alopecia

I didn't understand...why m.ProdId = ??? instead of prodId = m.ProdId? – Fortyfour

Because I do not know in advance how to navigate and get ProdId – Alopecia

Answer 1

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. :)

Hansen

Thanks your solution is more complete. I agree query syntax in some cases is more clear but you've guessed right, I have been asked to use lambda. Moreover I have to make this joins over 6 tables, and the dot notation in this case is more neat – Alopecia

@Hansen What if we need addition condition in the JOIN statement? How do we do that? For example, in the join pc in productcategory on p.Id equals pc.ProdId line, we need to add and p.Id == 1. – Insomuch

It seems suspect that you want p.Id == 1 since that's more of a where-filter than it is a join criteria. The way you'd do a join on more than one criteria generally is to use an anonymous type: join pc in productcategory on new { Id = p.Id, Other = p.Other } equals new { Id = pc.ProdId, Other = pc.Other }. This works in Linq-to-Objects, and I presume the same will work with database queries as well. With Databases, you may be able to forego complicated join queries by defining foreign keys as appropriate and accessing related data through the related property. – Hansen

In your example:in the dot syntax, the ppc ppc.p are anonymous types right? In the query syntax, the p.id you use in the last select is still a product object am i right? So with query syntax is easier if you join multiple tables to do operations in the final returning shema like min minby? – Nonstandard

thank you so much, how can i rename columns, like pc.ProdId as 'UserProdId" – Zipnick

@Zipnick it depends on which syntax you're using and where you intend to alias the column. In the final select, you can introduce UserProdId as the property name in the anonymous type. Prior to that, if you're wanting to code in terms of UserProdId for query syntax you could just introduce it with a let UserProdId = pc.ProdId; if you're using lambda/fluent syntax and we're not talking about the final .Select it will be more pain than it's worth because it's another .SelectMany where you introduce it, followed later by longer property chains to reference it and the other columns. – Hansen

Answer 2

What you've seen is what you get - and it's exactly what you asked for, here:

(ppc, c) => new { productproductcategory = ppc, category = c}

That's a lambda expression returning an anonymous type with those two properties.

In your CategorizedProducts, you just need to go via those properties:

CategorizedProducts catProducts = query.Select(
      m => new { 
             ProdId = m.productproductcategory.product.Id, 
             CatId = m.category.CatId, 
             // other assignments 
           });

Alidus

Thanks. I understand the discussion about the anonymous class but its properties contain just the class objects that fulfill the query? And what happens after I perform the 2 join? productproductcategory.product is not joined with category right? – Alopecia

@CiccioMiami: Well, the properties are references to objects, yes. It's not really clear what you mean by "not joined" - what information do you not get from your query that you want to get? – Alidus

With the first join I get the join between products and productcategory. With the second I get the join between productcategory(joined product) and category. That means the information about the multiple join is just contained to productproductcategory. This means that product (and category) are just joined with productcategory. – Alopecia

@CiccioMiami: Sorry, I'm not following you - but if you specify the join, it will do it. Have you tried using the code in my answer? Does it not do what you want? – Alidus

Sorry, I wanted to get to your code. The assignment of CatId works fine. For ProdId it should be m.productproductcategory.product.Id OR m.productproductcategory.productcategory.ProdId. The two assignments differ, first is on product (joined with productcategory) the second is with productcategory joined with both product and category. Do you follow my reasoning? – Alopecia

@CiccioMiami: Yes - but the two should give the same result, due to the join. Will edit my code. – Alidus

Ok that was my doubt I was not sure whether the second join performed the join on both properties or just on the one containing the field for the joining condition – Alopecia

@CiccioMiami: It doesn't matter, because the values will be the same. Assuming this is actually going to a SQL server somewhere, I strongly urge you to look at the generated SQL - hopefully that will put your mind at ease. – Alidus

Thanks I'll have a look with LINQPad – Alopecia

Answer 3

take look at this sample code from my project

public static IList<Letter> GetDepartmentLettersLinq(int departmentId)
{
    IEnumerable<Letter> allDepartmentLetters =
        from allLetter in LetterService.GetAllLetters()
        join allUser in UserService.GetAllUsers() on allLetter.EmployeeID equals allUser.ID into usersGroup
        from user in usersGroup.DefaultIfEmpty()// here is the tricky part
        join allDepartment in DepartmentService.GetAllDepartments() on user.DepartmentID equals allDepartment.ID
        where allDepartment.ID == departmentId
        select allLetter;

    return allDepartmentLetters.ToArray();
}

in this code I joined 3 tables and I spited join condition from where clause

note: the Services classes are just warped(encapsulate) the database operations

Pulchi

Answer 4

 public ActionResult Index()
    {
        List<CustomerOrder_Result> obj = new List<CustomerOrder_Result>();

       var  orderlist = (from a in db.OrderMasters
                         join b in db.Customers on a.CustomerId equals b.Id
                         join c in db.CustomerAddresses on b.Id equals c.CustomerId
                         where a.Status == "Pending"
                         select new
                         {
                             Customername = b.Customername,
                             Phone = b.Phone,
                             OrderId = a.OrderId,
                             OrderDate = a.OrderDate,
                             NoOfItems = a.NoOfItems,
                             Order_amt = a.Order_amt,
                             dis_amt = a.Dis_amt,
                             net_amt = a.Net_amt,
                             status=a.Status,  
                             address = c.address,
                             City = c.City,
                             State = c.State,
                             Pin = c.Pin

                         }) ;
       foreach (var item in orderlist)
       {

           CustomerOrder_Result clr = new CustomerOrder_Result();
           clr.Customername=item.Customername;
           clr.Phone = item.Phone;
           clr.OrderId = item.OrderId;
           clr.OrderDate = item.OrderDate;
           clr.NoOfItems = item.NoOfItems;
           clr.Order_amt = item.Order_amt;
           clr.net_amt = item.net_amt;
           clr.address = item.address;
           clr.City = item.City;
           clr.State = item.State;
           clr.Pin = item.Pin;
           clr.status = item.status;

           obj.Add(clr);



       }

Scion

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Orangy

Answer 5

var query = from a in d.tbl_Usuarios
                    from b in d.tblComidaPreferidas
                    from c in d.tblLugarNacimientoes
                    select new
                    {
                        _nombre = a.Nombre,
                        _comida = b.ComidaPreferida,
                        _lNacimiento = c.Ciudad
                    };
        foreach (var i in query)
        {
            Console.WriteLine($"{i._nombre } le gusta {i._comida} y nació en {i._lNacimiento}");
        }

Scheller

simple just that, but is better with lambda exp like some folks said. – Scheller

Answer 6

it has been a while but my answer may help someone:

if you already defined the relation properly you can use this:

        var res = query.Products.Select(m => new
        {
            productID = product.Id,
            categoryID = m.ProductCategory.Select(s => s.Category.ID).ToList(),
        }).ToList();

Sickly

Could you give more detail on "already defined the relation properly"? – Complementary