Loading nested relationships using Entity Framework

Question

I'm looking for a way to Load nested relationships with a where clause.

Imagine that query example below.

select * 
from directors d
inner join managers m 
  on d.id = m.director
inner join employees e
  on m.id = e.manager
where e.name = 'John'

It's easy to load all the information from those entities by doing this:

ctx.Directors.Include(x => x.Managers.Select(y => y.Employees)).ToList();

but how can I simulate my where clause at the query posted above?

Knowing that I'm using Entity Framework 6, is it possible to do that? I have researched for this and didn't find an answer to my question.

EDIT 1

I'm asking this, because I'm trying to use this in a WCF Restful aplication. And it's returning with a StackOverflowException, probably because of a circular reference when I build the backward reference for the entities.

I think you already tried it but: ctx.Directors.Include(x => x.Managers.Select(y => y.Employees).Where(z => z.name == "John")).ToList(); doesn't works? — Ludovic Feltz
– Ludovic Feltz, Commented Jan 9, 2018 at 14:14
This is actually not as trivial as it looks at first, because your SQL query would return a filtered sub-collection of the EF navigation properties. — grek40
– grek40, Commented Jan 9, 2018 at 14:44

Aaron · Accepted Answer · 2018-01-09 14:30:54Z

2

From looking at your LINQ query, something like this should work:

from director in ctx.Directors
from manager in director.Managers
from employee in manager.Employees
where employee.Name == "John"
select director;

answered Jan 9, 2018 at 14:30

Aaron

6627 silver badges16 bronze badges

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

Comments

grek40 · Accepted Answer · 2018-01-09 14:53:00Z

2

Basically, you should consider re-thinking your EF-Query in the other direction (Assuming that you have 1:many relationships):

ctx.Employees
    .Include(x => x.Manager.Director)
    .Where(x => x.Name == "John")
    .ToList();

Then you have a list of employees named John with their associated managers and directors.

The problem with starting at directors is, you can either load all related managers unconditionally, or you need to select your results into a new data structure instead of using the default EF mapping.

edited Jan 9, 2018 at 14:53

answered Jan 9, 2018 at 14:47

grek40

13.5k1 gold badge29 silver badges54 bronze badges

3 Comments

ruynunes Over a year ago

grek40, I know that I have this in the opposite way, but I'm working in a project where I use WCF with Restful, and when I return the data to the client, I receive a StackOverflowException. As I researched, the problem is with the circular reference. Do you know anything about this?

grek40 Over a year ago

@ruynunes not really and this question is definitely not written as a circular reference problem on data serialization kind of thing so this is going way out of topic.

ruynunes Over a year ago

I'm sorry, I really should put this at my question, and I'll update that .

Dan Dohotaru · Accepted Answer · 2018-01-09 19:12:32Z

1

As an alternative to using navigational properties you could simply translate your sql query in the linq equivalent. Navigational properties are great but make sure you actually need them.

var query = from director in ctx.Directors
            join manager in ctx.Managers
                on director.Id equals manager.DirectorId
            join employee in ctx.Employees
                on manager.Id equals employee.ManagerId
            where employee.Name == "John"
            select new
            {
                director,
                manager,
                employee,
            };

moreover projecting your queries to some sort of dedicated DTOs will certainly solve your circular references as well

Edit Try not to serialize your domain directly, but rather create the view model in the shape needed for outside layers. If you really need to expose your entities to the outside world, you might want to turn off lazy loading. As a matter of fact turn off lazy loading anyways as Lazy loading and serialization don’t mix well

edited Jan 9, 2018 at 19:12

answered Jan 9, 2018 at 18:13

Dan Dohotaru

3,1291 gold badge23 silver badges15 bronze badges

1 Comment

ruynunes Over a year ago

Probably the best way to do this, without using the backward navigation at Entity Framework . Thanks for the answer !

Collectives™ on Stack Overflow

Loading nested relationships using Entity Framework

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related