locked
Loading a big entity tree with EF RRS feed

  • Question

  • Hi,

    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 3 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

    Monday, July 6, 2009 1:53 PM

Answers

  • Hi Bert,

    As you describe, you have a problem with designing a solution for your db loading, this is not a specific issue of EF since you'll encounter this problem with any DAL you'll choose, so we will discuss options and how to implement them in EF.

    First option, as Jaroslaw suggested is to load entire data by tables, this can be a problem if you have many main entities, because adversely you could load a gigantic set of entities which you don't even need. It could also be a problem if you have many entity types, because this will create as much queries as you'll get when doing the standard "IsLoaded/Load" (and even worse because of the amount of entities you load for no use).

    Second option is to load all the entities in one-shot, using a big stored procedure that returns multiple result-sets, this is possible using simple Ado.Net, but because you intend to use EF, you'll have a problem doing so, because calling such a stored procedure will require you to parse the returned results youself which will make EF's ORM irrelevant.

    Third option is to navigate through the relations, as you suggested, this will create as many queries as your amount of entities, but at least the queries will run quickly, because they'll have a where clause that will tell them which specific entities to return. This is done in EF using the standard "IsLoaded/Load" code - you can also use transparent lazy load, see the following code (again, with Jaroslaw's help):
    http://blogs.msdn.com/jkowalski/archive/2008/05/12/transparent-lazy-loading-for-entity-framework-part-1.aspx

    Fourth option, is to look at the entities design and check if the queries can be splitted to multiple queries, each bringing an entity with some of the relations, using the "Include" method, if the design allows it you can use a couple of queries, each with some includes, to cover the whole entities graph you need. This is of course depends on how your entities are related - usually works better if you don't have a very wide graph, but a balanced graph, where entity has a couple of related entities, those entities have some of their own and so on, it won't work good if you have one entity type which relates to 20 other entity types (very wide). As for loading relations, you'll have to write some code to create a "select where id in ()" query (you can do it using entity sql).

    I you'll publish your entities model, perhaps we can find the most suitable solution.

    Best of luck,
    Ido.
    Monday, July 6, 2009 6:47 PM

All replies

  • Actually there may be something that fits in the c) category.

    If you want to pre-load the entire database (as opposed to a fraction of it) you can just try loading each entityset individually and EF will perform the right collection and reference fixup for you. Imagine this is Northwind. You can do:

    context.Customers.ToList()
    context.Orders.ToList()
    context.Products.ToList()
    context.OrderLines.ToList()
    context.Categories.ToList()

    After this operation every customer will have their Orders collection populated, every Order will have their Customer property hooked up properly and so on. The only caveat is that this doesn't work for many-many relationships which are modeled as associations, because there's no way to query the association set in EF, but if your association is an entity with some additional payload it should be fine.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 6, 2009 3:51 PM
  • Hi Bert,

    As you describe, you have a problem with designing a solution for your db loading, this is not a specific issue of EF since you'll encounter this problem with any DAL you'll choose, so we will discuss options and how to implement them in EF.

    First option, as Jaroslaw suggested is to load entire data by tables, this can be a problem if you have many main entities, because adversely you could load a gigantic set of entities which you don't even need. It could also be a problem if you have many entity types, because this will create as much queries as you'll get when doing the standard "IsLoaded/Load" (and even worse because of the amount of entities you load for no use).

    Second option is to load all the entities in one-shot, using a big stored procedure that returns multiple result-sets, this is possible using simple Ado.Net, but because you intend to use EF, you'll have a problem doing so, because calling such a stored procedure will require you to parse the returned results youself which will make EF's ORM irrelevant.

    Third option is to navigate through the relations, as you suggested, this will create as many queries as your amount of entities, but at least the queries will run quickly, because they'll have a where clause that will tell them which specific entities to return. This is done in EF using the standard "IsLoaded/Load" code - you can also use transparent lazy load, see the following code (again, with Jaroslaw's help):
    http://blogs.msdn.com/jkowalski/archive/2008/05/12/transparent-lazy-loading-for-entity-framework-part-1.aspx

    Fourth option, is to look at the entities design and check if the queries can be splitted to multiple queries, each bringing an entity with some of the relations, using the "Include" method, if the design allows it you can use a couple of queries, each with some includes, to cover the whole entities graph you need. This is of course depends on how your entities are related - usually works better if you don't have a very wide graph, but a balanced graph, where entity has a couple of related entities, those entities have some of their own and so on, it won't work good if you have one entity type which relates to 20 other entity types (very wide). As for loading relations, you'll have to write some code to create a "select where id in ()" query (you can do it using entity sql).

    I you'll publish your entities model, perhaps we can find the most suitable solution.

    Best of luck,
    Ido.
    Monday, July 6, 2009 6:47 PM