How to Explicit loading specific related entities for specific entities using Entity Framework?

Question

I have two database tables :

1) Locations which has many Staffs

2) Staffs which has many Roles

If I want to load only related staffs which have Supervisor role for specific location I would do like this:

  var location = dbContext.Locations.Find(locationId);
  dbContext.Entry(location).Collection(b => b.Staffs).Query().Where(s => s.Roles.Any(r => r.Name=="Supervisor"));

My question is how to achieve explicit loading for related Staffs with supervisor role for all locations(I don't need for specific one as above)?

So you want all Staff with a supervisor role regardless of the location? — Riv
– Riv, Commented Oct 24, 2017 at 10:10
@Riv No, I want to load only Staff with Supervisor role for all locations inside locations table of course with locations data — Simple Code
– Simple Code, Commented Oct 24, 2017 at 10:11
EF is not designed to support partially loaded reference collections. So you will need to decide between loading all locations via include or separately querying the wanted location staff. — grek40
– grek40, Commented Oct 24, 2017 at 10:19

Toxantron · Accepted Answer · 2017-10-24 12:13:41Z

1

I did something similar for our implementation of the repository pattern. The key is to invoke Load at the end.

We had

public virtual void Load<TOut>(T entity, Expression<Func<T, ICollection<TOut>>> loadExpression, Expression<Func<TOut, bool>> filter) where TOut : class
{
    Context.Entry(entity).Collection(loadExpression).Query().Where(filter).Load();
}

So for your case you can try

dbContext.Entry(location).Collection(b => b.Staffs).Query()
    .Where(s => s.Roles.Any(r => r.Name=="Supervisor")).Load();

And then loop over all locations and load the references. Another option is to write an explicit Linq2Entities query for your case.

var locationWithStaff = (from location in dbContext.Locations
                         select new
                         {
                             location,
                             Staffs = (from staff in location.Staffs
                                       where staff.Roles.Any(r => r.Name=="Supervisor")
                                       select staff).ToList()
                          }).ToList();

edited Oct 24, 2017 at 12:13

answered Oct 24, 2017 at 10:52

Toxantron

2,40615 silver badges24 bronze badges

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

1 Comment

Simple Code Over a year ago

Thanks for answer.I think it's a good trick to implement that

Riv · Accepted Answer · 2017-10-24 11:42:15Z

1

You can use SelectMany to flatten the Staff list across all locations. Then you can filter based on the role

dbContext.Locations.SelectMany(b => b.Staffs).Where(s => s.Roles.Any(r => r.Name=="Supervisor"));

You could return an anonymous type in the SelectMany which would contain Location properties eg.:

dbContext.Locations.SelectMany(x => x.Staffs, (x, Staffs) => 
                   new { locationID = x.LocationID, SomeOtherProperty = x.OtherProperty , Staff = Staffs })
                  .Where(y => y.Staff.Roles.Any(z => z.Name == "Supervisor"));

edited Oct 24, 2017 at 11:42

answered Oct 24, 2017 at 10:23

Riv

1,8491 gold badge16 silver badges16 bronze badges

3 Comments

Simple Code Over a year ago

This will only will load Staffs without the locations data , I think as @grek40 said "EF is not designed to support partially loaded reference collections"

Riv Over a year ago

@SimpleCode Do you need the entire Location entity or specific properties?

Simple Code Over a year ago

The entire location entities

Collectives™ on Stack Overflow

How to Explicit loading specific related entities for specific entities using Entity Framework?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related