Joining column with multiple tables LINQ

Question

There is a table I want to join with different columns in different tables.

This is how so far I did this

var purchData = (from a in db.AppRequest
join e in db.Employee on a.Req_By equals e.Id
join c in db.Company on e.CompanyId equals c.Id
join d in db.Designation on e.DesignId equals d.Id
join l in db.Master_Locations on a.Req_Location equals l.Id
join dep in db.Department on e.DepId equals dep.Id
join p in db.Purchase on a.Id equals p.Req_Id
join pi in db.PurchasingItems on p.Id equals pi.Purchase_Id
join pd in db.PurchasingDetails on p.Id equals pd.Purchase_Id
join pds in db.PurchasingDetailsSup on pd.Id equals pds.PurchasingDetails_Id
join s in db.M_Supplier on pds.Supp_Id equals s.Id
join payMethod in db.Master_PayMethods on s.Pay_Method equals payMethod.Id
join poNo in db.ApprovedPoNumbers on p.Id equals poNo.Purchase_Id
where a.Id == id && pds.IsApproved == true

In db.ApprovedPoNumbers table has purchase_Id and Supplier_Id

In db.PurchasingDetailsSup table has purchase_Id and Supplier_Id

So I want to know that here join poNo in db.ApprovedPoNumbers on p.Id equals poNo.Purchase_Id line I want to join the db.ApprovedPoNumbers table purchase_Id,Supplier_Id with db.PurchasingDetailsSup table purchase_Id and Supplier_Id

You can join on multiple columns like this join on new {p.Id, p.Supplier_ID} equals new {poNo.Purchase_ID, poNo.Supplier_ID} However if you have FK relationships setup between these tables then you should also have Navigation properties and it would be better to use them instead of all the joins. coding.abel.nu/2012/06/dont-use-linqs-join-navigate — juharr
– juharr, Commented Feb 15, 2022 at 11:53
@juharr is this way not possible ? join poNo in db.ApprovedPoNumbers on new { p.Id , pds.Supp_Id, } equals new {poNo.Purchase_Id, poNo.Supplier} Supplier Id is from another table related to PurchaseID — Dev Beginner
– Dev Beginner, Commented Feb 15, 2022 at 11:59
@DevBeginner If they are not from the same table, then there would not be a problem if you just use inner joins per table. Every inner joined table would function as an extra reducing filter. — Silvermind
– Silvermind, Commented Feb 15, 2022 at 12:09
Perhaps you're more comfortable using sql and you now how you would write that. If so, then you could add it to your question to make it more clear. — Silvermind
– Silvermind, Commented Feb 15, 2022 at 12:11
Yes, that would work, the values in the join condition do not have to both come from the same table. You can even do stuff like on new { Id = 3, x.Foo} equals new {y.Id, y.Foo}. What you cannot do is a more complex condition (anything that isn't of the form a = b AND c = d. For those you'd have to move some or all of the condition to the where clause instead. — juharr
– juharr, Commented Feb 15, 2022 at 12:39

Lajos Arpad · Accepted Answer · 2022-02-15 12:25:02Z

1

You can achieve that by building two objects with the criterias you intend to match and use the equals operator on them:

on new {poNo.purchase_Id, poNo.Supplied_Id} equals new {pds.purchase_Id, pds.Supplier_Id} into details

answered Feb 15, 2022 at 12:25

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Joining column with multiple tables LINQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related