CODEWITHSUNDEEP
sql-serverentity-frameworkjoin
Geoffrey
Geoffrey

Reputation: 23

How to join 3 tables in entity framework 6

I have the following query:

select distinct X.* from X
inner join Y on X.ID = Y.ID1
inner join Z on Y.ID= Z.ID2
where Z.param = 1

I'm having trouble with the second Join and came up so far with

entities.X
.Join(entities.Y, t1 => t1.ID, t2 => t2.ID1, (t1, t2) => new { X= t1, Y= t2 }).Select(x => x.X)

But adding a second join keeps me telling that the anonymous Type does not contain a definition for t2.ID

entities.X
.Join(entities.Y, t1 => t1.ID, t2 => t2.ID1, (t1, t2) => new { X= t1, Y= t2 })
.Join(entities.Z, t2 => t2.ID, t3 => t3.ID1, (t2, t3) => new { Y= t2, Z= t3 }).Select(x => x.X)

Any ideas?

Upvotes: 2

Views: 5343

Answers (2)

Mateusz Przybylek
Mateusz Przybylek

Reputation: 5845

If you have configured navigation property 1-n I would recommend you to use:

entities.X
  .SelectMany(x => x.Ys,               // 1st join
    (x, y) => new { X = x, Y = y })    // 1st projection
  .SelectMany(b => b.Y.Zs,             // 2st join etc...
    (b, z) => new 
    {
      b.X,                             //listed separatelly to not dig in
      b.Y, 
      Z = z
    })
  .Select(b => b.X)
  .Distinct();

Much more clearer to me and looks better with multiple nested joins.

Upvotes: 0

Thejaka Maldeniya
Thejaka Maldeniya

Reputation: 1076

In the second Join clause, the item type is the anonymous type you just created in the previous (first) Join clause. In it the fields are defined as X and Y instead of t1 and t2, so these are the fields you should use.

entities.X
.Join(entities.Y, t1 => t1.ID, t2 => t2.ID1, (t1, t2) => new { X = t1, Y = t2 })
.Join(entities.Z.Where(p => p.param == 1), t2 => t2.Y.ID, t3 => t3.ID2, (t, t3) => new { X = t.X, Z = t3 })
.Select(u => u.X)
.Distinct();

(Edited to reflect the original SQL query, and explanation added)

Note: Where clause may be added at the end as well, but I used the current form because it is usually better to filter first (i.e. before joining, etc.).

Upvotes: 3

Related Questions