[Entity Framework] Loading a huge entity tree with EF

Question

I need to load a model, existing of +/- 20 tables from the database with Entity Framework.

So there are probably a few ways of doing this:

1. Use one huge Include call
2. Use many Includes calls while manually iterating the model
3. Use many IsLoaded and Load calls

Here's what happens with the 2 options

1. EF creates a HUGE query, puts a very heavy load on the DB and then again with mapping the model. So not really an option.

2. The database gets called a lot, with again pretty big queries.

3. Again, the database gets called even more, but this time with small loads.

All of these options weigh heavy on the performance. I do need to load all of that data (calculations for drawing).

So what can I do?

a) Heavy operation => heavy load => do nothing :) b) Review design => but how? c) A magical option that will make all these problems go away

Answer 1

When you need to load a lot of data from a lack of different tables, there is no "magic" solution which makes all problems go away. But in addition to what you have already discussed, you should consider projection. If you don't need every single property of an entity, it is often cheaper to project the information you do need, i.e.:

from parent in MyEntities.Parents
select new
{
    ParentName = ParentName,
    Children = from child in parent.Children
               select new
               {
                   ChildName = child.Name
               }
}

One other thing to keep in mind is that for very large queries, the cost of compiling the query can often exceed the cost of executing it. Only profiling can tell you if this is the problem. If this turns out to be the problem, consider using CompiledQuery.

Answer 2

You might analyze the ratio of queries to updates. If you mostly upload the model once, then everything else is a query, then maybe you should store an XML representation of the model in the database as a "shadow" of the model. You should be able to either read the entire XML column in at once fairly quickly, or else maybe you can do your calculations (or at least the fetch of the values necessary for the calculations) using XQuery.

This assumes SQL Server 2005 or above.

Answer 3

You could consider caching your data in memory instead of getting it from the database each time.

I would recommend Enterprise Library Caching Application block: http://msdn.microsoft.com/en-us/library/dd203099.aspx