Including child entities when running a sproc using Entity Framework V1

Question

I've inherited some code and my current task is to improve it's performance. Straight away I've noticed a foreach loop containing lazy loads of child elements. I'm trying to remove the lazy loads since, in the example below, there's going to be 1 + (3 * n) calls to the database!

using (BaseEntityConnection BaseEntityContext = new BaseEntityConnection()) {
List<Parents> entities = BaseEntityContext.CallASproc(storeOrgLevelId, startDate, endDate).ToList<Parents>();
foreach (Parents entity in entities)
{
 entity.Child1Reference.Load();
 entity.Child2Reference.Load();
 entity.Child3Reference.Load();
}
}

My initial thoughts turned to using .Include() but I don't see how that's possible when I'm initially calling a sproc. (Assume for the moment the sproc can't be generalised to Linq to entities.) I also thought that if I loaded the child entities using Linq to Entities before the call to the sproc then the tracking manager within the context would employ an 'Identity Map' to identify entities that are already loaded and therefore would figure out that it doesn't need to hit the database when I call .Load(). However, I guess I've also got to call .Attach() on each of the child references - but this seems clunky and I haven't made it work yet either!!

Does anybody know how I can get the object graph loaded for the entities returned by the sproc with the minimum of round-trips to the database?

Answer 1

First, Load() always hits the DB. However, you don't need to call it if the related entity is already loaded, which it will be if it's already been loaded into the same context previously. Check the IsLoaded property before calling Load().

Include works for entities returned from a SP. It will JOIN in the source of the other entities in SQL just like you might do "manually". Have you tried it? That said, it may be faster to call Load a few times than to JOIN to the related child entities if the SP result set is large.

Finally, another alternative is to project in LINQ instead of using Include. The result will be the same, but you can precompile the query (with CompiledQuery), which saves the prepare time, which could be significant if you have a lot of Includes and if you run the query repeatedly.