.net – Querying an Entity’s child collections in Entity Framework 4.1

entity-framework-4.1linq-to-entitiesnet

I have a set of entities that I have queried from IDbSet in my DbContext. I now want to iterate over each entity and query their child collections, which are defined in the entity as ICollection. Is it correct to call AsQueryable() on the child collections and run my linq query on that? If so, will my queries be linq-to-objects or does the collection object populated by EF implement IQueryable that goes to the database?

Thanks for any insight on this.

Best Answer

It whole depends on how your entities are defined and if lazy loading is enabled. Your query to IDbSet will be linq-to-entities. If lazy loading is enabled accessing each navigation property of the loaded entity will trigger database query which will load all related entities. AsQueryable has no effect here you will still execute linq-to-objects query on all loaded data. In such scenario it is really better approach to use eager loading and load your related entities together with the main entity:

var query = context.YourEntitySet.Include(e => e.YourNavProperty);

In some cases executing this query can internally produce very big result sets.

If you have a lot of related entities and you really want to load only some very small subset you can use following approach:

context.Entry(yourLoadedMainEntity)
       .Collection(e => e.YourNavProperty)
       .Query()
       .Where(...)
       .Load();

This the way to force EF to load only subset of related entities with linq-to-entities. You still have to execute this for each loaded main entity. Executing so many queries is very slow. It is still N+1 problem.

Another and most complex optimization is loading all main entities in the single query and all related entities in another query:

var query = context.YourEntitySet;
var relatedQuery = from e in context.YourRelatedEntitySet
                   join m in context.YourEntitySet on e.MainId equals m.Id
                   where ...
                   select e;

Once you execute both queries Entity framework should ensure that navigation properties are correctly filled with related entities but this works only when lazy loading is turned off but entities are tracked by context (well I never tried this with DbContext API but it worked with ObjectContext API).

Related Topic