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   gt  p Id  pc   gt  pc ProdID   p  pc    gt  new  product   p  productcategory   pc                       Join category  ppc   gt  ppc productcategory CatId  c   gt  c Id   ppc  c    gt  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   gt  new   m ProdId        m CatId          other assignments       how can I achieve this goal

User · Answer

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();

User · Answer

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   gt  p Id  pc   gt  pc ProdId   p  pc    gt  new   p  pc         Join category  ppc   gt  ppc pc CatId  c   gt  c Id   ppc  c    gt  new   ppc  c         Select m   gt  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   gt  p Id  pc   gt  pc ProdId   p  pc    gt  new   p  pc         Join category  ppc   gt  ppc pc CatId  c   gt  c Id   ppc  c    gt  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

User · Answer

What you ve seen is what you get - and it s exactly what you asked for  here    ppc  c    gt  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   gt  new                 ProdId   m productproductcategory product Id                CatId   m category CatId                   other assignments

User · Answer

take look at this sample code from my project  public static IList lt Letter gt  GetDepartmentLettersLinq int departmentId        IEnumerable lt Letter gt  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

User · Answer

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

User · Answer

public ActionResult Index                 List lt CustomerOrder Result gt  obj   new List lt CustomerOrder Result gt             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

[c#] How to perform Join between multiple tables in LINQ lambda

Examples related to c#

Examples related to linq

Examples related to join

Examples related to lambda

Examples related to multiple-tables