How to connect 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 get an object from the following class:

 QueryClass { productproductcategory, category} 

Where the production category refers to:

 ProductProductCategoryClass {product, productcategory} 

I do not understand where the combined “table” is; I was expecting a single class that contains all the properties from the classes involved.

My goal is to populate another object with some properties obtained from the request:

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

How can I achieve this?

Answer 1

For joins, I strongly prefer the query syntax for all details that are happily hidden (not the least of which are transparent identifiers associated with intermediate projections, on paths that are obvious in the syntax-point equivalent). However, you asked about 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 = mcCatId // other assignments }); 

If you need, you can save the connection in a local variable and reuse it later, however, without other details, on the contrary, I see no reason to introduce a local variable.

Alternatively, you can throw Select into the last lambda of the second Join (again, if there are no other operations depending on the results of the join), which will 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 having made the last attempt to sell you on the query syntax, it will 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 to having query syntax. I know that some stores have such credentials - often based on the notion that the query syntax is somewhat more limited than the point syntax. There are other reasons, such as "why should I learn the second syntax if I can do everything and much more in point syntax?" As this last part shows, there are details that hide the query syntax that can make it worth covering with the improved readability that it brings: all those intermediate projections and identifiers that you have to prepare, fortunately, are not front and center, stage in query syntax - they are the background. Now from my soapbox - anyway, thanks for the question. :)

Answer 2

What you saw is what you get - and this is exactly what you asked for here:

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

This is a lambda expression that returns an anonymous type with these two properties.

In your categorical products, you just need to go through these properties:

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

Answer 3

take a 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 included the join condition from the where clause

note: Services classes just deform (encapsulate) database operations

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

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}"); } 

Answer 6

it was a long time ago, but my answer might help someone:

if you have already defined the relationship correctly, you can use this:

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