Xandrmoro Xandrmoro -4 years ago 184
C# Question

EF6 eager loading inside POCO

Lets say, I have such structure:

  • Branch: Id

  • Group: Id

  • BranchGroup: BranchId, GroupId, Since, Till

Relations are
Branch 1..N BranchGroup 1..1 Group
, so that generated
class has an
ICollection<BranchGroup> BranchGroups
navigation property.

And there is a property inside
POCO, returning current groups (simplified a bit):

public List<Group> Groups => BranchGroups.Where(bg => bg.Since <= DateHelper.Current &&
bg.Till >= DateHelper.Current)
.Select(bg => bg.Group).ToList();

I call it and db is exploded with thousands of queries, each for one group. Is it possible to somehow optimize it without having access to
(or at least reach current context from inside entity)?

UPD: simplest real query looks like this:

public List<Group> Groups => BranchGroups.Where(bg => bg.Since <= DateHelper.Current &&
bg.Till >= DateHelper.Current)
.Select(bg => bg.Group)
.Where(g => !g.Deleted).ToList();

Answer Source

That seems to be not possible - whenever you access navigation property (BranchGroups in this case), whole related set is pulled from database, so the rest of your query (Where, Select, Where) is performed over data set in memory, so it not translated to database query. Then, bg.Group is also navigation property, so to get one, separate database query is performed, just as you observe.

Real POCO objects should not have any relation to DbContext, they are just plain objects. So you cannot (and should not be able to) reach to the context from inside POCO (or that is not POCO any more).

So your options are:

  1. Fill Groups property when constructing Branch object (not good if you don't always need to load those Groups).
  2. Fetch Groups with the help of external context reference (not from inside POCO).
  3. Maybe, when constructing Branch object, set Groups property like this:

      branch.Groups = context.BranchGroups.Where(c => c.BranchID = branch.ID && ...); // the rest of your query

    Then it will still be POCO (Groups are just IEnumerable) but contains query you need which is lazy loaded on access (and no explicit references to context).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download