Xandrmoro Xandrmoro - 26 days ago 10
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
Branch
class has an
ICollection<BranchGroup> BranchGroups
navigation property.

And there is a property inside
Branch
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
dbContext
(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();

Evk Evk
Answer

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).

Comments