EF4 how to join a table to two or more other tables

Question

How to join a table to more than one table? something like this we can easily do in SQL

SELECT p.* FROM Person.BusinessEntity AS e
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
AND e.BusinessEntityID = p.BusinessEntityID  INNER JOIN Person.BusinessEntityContact AS b 
ON e.BusinessEntityID = b.BusinessEntityID AND p.BusinessEntityID = b.PersonID

Thanks in advance

See last example on this page: http://msdn.microsoft.com/en-us/library/bb896266.aspx — kol, Dec 06 '11 at 21:59
yes it shows how to join between two tables, and msdn also examples to join with two fields but only to one other table. I need to make join with two other tables like in " INNER JOIN Person.BusinessEntityContact AS b ON e.BusinessEntityID = b.BusinessEntityID AND p.BusinessEntityID = b.PersonID" — Bhuvan, Dec 06 '11 at 22:04
see this answer: http://stackoverflow.com/questions/1264993/linq-left-join-on-multiple-or-conditions or basically the join uses a new object for comparison `join b in Person.BusinessEntityContact on new {e.BusinessEntityID, p.BusinessEntityID} equals new { b.BusinessEntityID, b.PersonID}` — Gary.S, Dec 06 '11 at 22:40

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Generally, you just stack the joins just like you'd do in SQL. I see that the original question has been clarified a bit, and a composite key join is likely necessary so it will be a combination of stacking joins with composite keys. Gary.S noted the correct syntax for the composite key part in his comments to the original question, but I've ammended my answer here to include the same composite key selection. For regular joins you use the

 join … in … on … equals …

And for composite keys you use anonymous types to contain all the key columns from both entities.

Here is an example that should be about what you are looking for

var items = from e in Person.BusinessEntity
        join p in Person.Person on 
             e.BusinessEntityID equals p.BusinessEntityID
        join b in Person.BusinessEntityContact on 
             new {e.BusinessEntityID, p.BusinessEntityID} equals 
             new { b.BusinessEntityID, b.PersonID} 
        select p;

You might also need to look at the GroupJoin, which you need in order to do a left outer join in LINQ.

Group join looks like:

join … in … on … equals … into …

Good examples of most cases can be found at the 101 samples page

score 1 · Answer 2 · answered Dec 07 '11 at 08:14

There is a school of thought that if you have created your entity framework model correctly then you shouldn't have to use joins at all in your queries. All your joins should actually be navigation properties (i've taken out the Person schema from the query) i.e.

BusinessEntity.Person.Where(x => x == yourID)

or more clearly probably

Entity1.Entity2.Where(x => x == yourID)

Not my original idea - Julie Lerman says it in her EF book

EF4 how to join a table to two or more other tables

2 Answers2