Entity Framework - 1 to ... to many to 1 select

Question

IHAC that prefers entity framework over code where possible, existing DB (so changing the schema is out of the question). I'm trying to get a list of unique E elements given an A id. I'm new to MS Entities, and its not how I would handle it (I think for some of the complex things that this group is doing with them they are slower than snot on ice) but Its what I've been handed.

Here's a quickie diagram: db example diagram THIS IS NOT MY DB DESIGN! Don't shoot the questioner :)

Here's the SQL I'd write:

SELECT E.e_Id, E.e_Name
FROM
    A,
    B,
    C,
    D,
    E
WHERE
    A.a_Id = 'someid'
    AND A.a_Id = B.a_Id
    AND B.b_Id = C.b_Id 
    AND C.d_Id = D.d_Id
    AND D.e_Id = E.e_Id
GROUP BY E.e_Id, E.e_Name

Stepwise, I can get to the table D Entities, but then I can't figure out how to get to the E Entities:

        var bLocal = context.AEntities
            .Where( a => a.a_Id == 'someid' )
            .Select( b => b.B );

        var dLocal = bLocal
            .SelectMany( b => b.D );

        var eLocal = dLocal. ????
               - OR? - 
        var eLocal = context.EEntities ???

you can do multiple froms in one statement in linq using query syntax. `from t1 in table1 from t2 in table2` etc. — Silvermind, Mar 19 '14 at 14:03
@Silvermind, he's just using the old style SQL89 formatting for his sql joins rather than inner joins (SQL92), not creating a cartesian product. — crthompson, Mar 19 '14 at 14:16
@paqogomez, yes but... he is doing so, but only restricting by including the `where`'s. You're example shows an inner join which he exactly needs. He could already have done that in the sql query, but to each his own. Your answer is the actual way to go, but man, do joins in method chains look ugly. ;) — Silvermind, Mar 19 '14 at 16:05
@paqogomez not yet :( The model they created doesn't have the relationship table C in it, so I can't get there from here (that I can see) And yes, they use the butt ugly method syntax everywhere. Why wouldn't they include the relationship table in the model? — rcarver, Mar 19 '14 at 16:29

crthompson · Accepted Answer · 2014-03-19T16:42:57.370

2

Translating your sql into linq is pretty straight forward:

var results = (from a in A
               join b in B on a.a_Id equals b.a_Id
               join c in C on b.b_Id equals c.b_Id 
               join d in D on c.d_Id equals d.d_Id
               join e in E on d.e_Id = e.e_Id
               where a.a_Id == 'someid'
               group e by new { e.e_Id, e.e_Name }
               select e)

If you wanted to do a method chain it would look like this:

var results = (A.Join(B, a => a.a_Id, b => b.a_Id, (a, b) => new {a, b})
    .Join(C, @t => b.b_Id, c => c.b_Id, (@t, c) => new {@t, c})
    .Join(D, @t => c.d_Id, d => d.d_Id, (@t, d) => new {@t, d})
    .Join(E, @t => d.e_Id = e.e_Id, e => , (@t, e) => new {@t, e})
    .Where(@t => a.a_Id == 1)
    .GroupBy(@t => new {e.e_Id, e.e_Name}, @t => e)
           select e)

Personally.. I'd stick with the query syntax. Full disclosure, ReSharper did the method chain conversion so its possible that it could be prettier.

edited Mar 19 '14 at 16:42

answered Mar 19 '14 at 14:06

crthompson

15,653
6
58
80

Where's the where? `(A.a_Id == 'someid')` – Kevin Mar 19 '14 at 14:07
Customer isn't using LINQ :( I'm not all that familiar with EF/Linq but from what I can see in the entity model the relationship table C isn't directly represented, its indirectly represented via the navigation properties. So I'm not seeing how I can do this LINQ query. – rcarver Mar 19 '14 at 15:54
@rcarver If you can do it in sql, you can do it in linq. If `C` is just linked off of.. say `B` you dont need to do the `C` join. Just add a restriction to the `B` join in the where. `&& b..c_Id == d.c_Id` – crthompson Mar 19 '14 at 16:01
@rcarver You might also benefit from [Linqer](http://www.sqltolinq.com/). It can read your entity model, then you can put in straight SQL and it will convert it to Linq. Before you buy it tho, check out a few of [these answers](http://stackoverflow.com/a/12240849/2589202) – crthompson Mar 19 '14 at 16:06
@paqogomez b.C is an EntityCollection which is why my breakout above doesn't work. I'm almost ready to tightly couple the sucker to the db and write a proc to do it for me. Of course, my inexperience with EF/Linq could be that I'm just not seeing it. – rcarver Mar 19 '14 at 16:32
If their model had been setup as I'd expect this would be the right answer, I ended up with a different solution specific to the customers framework implementation, but I'm going to mark this as the correct answer. Thanks for the help! – rcarver Mar 19 '14 at 17:48

score 0 · Answer 2 · answered Mar 19 '14 at 14:02

0

Are all navigation properties present? I.e. can you just navigate using dLocal.E? If not, use context.EEntities.Where(e => e.e_Id == dLocal.e_Id) (probably use Single() instead).

answered Mar 19 '14 at 14:02

D.R.

20,268
21
102
205

well, thats where it breaks down. The relationships are there, but I have many D's and I want the many E's but only the unique ones (hence the group by) – rcarver Mar 19 '14 at 16:08

Entity Framework - 1 to ... to many to 1 select

2 Answers2