none
How to eager load entire database with EF

    Question

  • My database consists of 5 tables with ~10.000 rows combined. It takes ~1Mb in SQL Server CE which is on shared folder. The database itself is hierarchical Country-Region-City-Street-Building. I am using Entity Framework 4.

    Because the database is small users are able to explore and edit all 2.000 Cities in a WPF ListView. But with every approach I tried so far the GUI is sluggish (because of many database round-trips to shared folder, with local database GUI is lightfast). How can I load entire database into memory with one or few database round-trips?

    I tried multiple Include() but I noted great performance penalty as described here

    Should I write my own ORM-light? I could also use plain ascii CSV files instead of database for backend but it would obviously exclude concurrency.

    Using other database which runs as a service like SQL Server Express is not a solution! I need both read and write to database.

    Saturday, February 05, 2011 6:51 PM

Answers

  • On 2/6/2011 9:45 AM, Mikhail Poda wrote:
    > @darnold92: Thank you for you answers and guidance! I have a couple of
    > questions regarding your ESQL technique.
    >
    > * can I use it for an object tree with more then 2 levels (my model
    > is 5 levels deep: Country-Region-City-Street-Building)?
     
    You would have a List<T> for each object graph within the parent object.
    Or there world be 4 objects in the parent object, if only getting a
    single object from each level.
     
    > * can I use context to put new entities into database?
     
    You would need to create a new context by getting the parent and
    children to do CRUD operations with any objects that needed to CRU --
    Created, Updated, Deleted.
     
    > * what is the advantage of using ESQL vs. Linq in my case?
    >
     
    You do it yourself and query the model, instead of using Linq and Linq
    build the T-SQL query. The use of ESQ is faster than using Linq.
     
    • Marked as answer by Mickey Mousoff Tuesday, February 08, 2011 4:45 PM
    Tuesday, February 08, 2011 3:58 AM

All replies

  • On 2/5/2011 1:51 PM, Mikhail Poda wrote:
    > My database consists of 5 tables with ~10.000 rows combined. It takes
    > ~1Mb in SQL Server CE which is on shared folder. The database itself is
    > hierarchical Country-Region-City-Street-Building. I am using Entity
    > Framework 4.
    >
    > Because the database is small users are able to explore and edit all
    > 2.000 Cities in a WPF ListView. But with every approach I tried so far
    > the GUI is sluggish (because of many database round-trips to shared
    > folder, with local database GUI is lightfast). How can I load entire
    > database into memory with one or few database round-trips?
    >
    > I tried multiple|Include()|but I noted great performance penaltyas
    > described here
    > <http://www.mssoftwareconsulting.com/msswc/blog/post/Entity-Framework-Performance-Penalty-of-Include.aspx>
    >
    > Should Iwrite my own
    > <http://ayende.com/Blog/archive/2006/05/12/25ReasonsNotToWriteYourOwnObjectRelationalMapper.aspx>ORM-light?
    > I could also use plain ascii CSV files instead of database for backend
    > but it would obviously exclude concurrency.
    >
    > Using other database which runs as a service like SQL Server Express is
    > not a solution! I need both read and write to database.
    >
     
    You use the ToList().
     
    <http://blogs.msdn.com/b/adonet/archive/2008/10/07/migrating-from-linq-to-sql-to-entity-framework-eager-loading.aspx>
     
    Saturday, February 05, 2011 9:10 PM
  • My database model is hierarchical (each with one-to-many) Country-Region-City-Street-Building. Using ToList() may work (I can not test it right now) but only for single level. I could use Include() but with many navigation properties it takes forever. Should I use dummy Countries.Include(...).Include(...).ToList() on start to preload database?

    My question is how to load ENTIRE database, not eager loading of a single entry.

    Saturday, February 05, 2011 10:04 PM
  • On 2/5/2011 5:04 PM, Mikhail Poda wrote:
    > My database model is hierarchical (each with one-to-many)
    > Country-Region-City-Street-Building. Using ToList() may work (I can not
    > test it right now) but only for single level. I could use Include() but
    > with many navigation properties it takes forever. Should I use dummy
    > Countries.Include(...).Include(...).ToList() on start to preload database?
    >
    > My question is how to load ENTIRE database, not eager loading of a
    > single entry.
    >
     
    I believe the Tolist(s) 'includes' children to a parent on eager loading.
     
    <http://geekswithblogs.net/rgupta/archive/2010/04/01/entity-framework-version-1---brief-synopsis-and-tips-ndash.aspx>
     
    However, if I was concerned with this, I would take control myself by
    using ESQL with a dataread, instantiate a new Entity off the model,
    populate the entity loading it into a List<entity> and returning the
    List<Entity>. You could have a custom object that has the parent entity
    and the children.
     
    http://msdn.microsoft.com/en-us/library/bb387145.aspx
    http://msdn.microsoft.com/en-us/library/bb387118.aspx
    http://www.informit.com/articles/article.aspx?p=1273361&seqNum=3
     
    Sunday, February 06, 2011 12:43 AM
  • @darnold92: Thank you for you answers and guidance! I have a couple of questions regarding your ESQL technique.

    • can I use it for an object tree with more then 2 levels (my model is 5 levels deep: Country-Region-City-Street-Building)?
    • can I use context to put new entities into database?
    • what is the advantage of using ESQL vs. Linq in my case?
    Sunday, February 06, 2011 2:45 PM
  • Hi Mikhail Poda,

    Can I use it for an object tree with more then 2 levels (my model is 5 levels deep: Country-Region-City-Street-Building)?

    Yes, please refer below link:

    http://msdn.microsoft.com/en-us/library/bb896272.aspx

     For example,

    var contacts = (from contact in context.Contacts

          .Include("SalesOrderHeaders.SalesOrderDetails")

                           select contact).FirstOrDefault();

    You can use the Include keywork and includes your all related entities. In above example, SalesOrderHeaders and SalesOrderDetails is include. In same way, you can include your five related entities.

    Can I use context to put new entities into database?

    Yes you can create the Empty Model ,

    Please refer below links:

    http://msdn.microsoft.com/en-us/library/cc716703.aspx

     

    What is the advantage of using ESQL vs. Linq in my case?

    http://stackoverflow.com/questions/38647/linq-to-entities-vs-esql

    • Edited by Paras Sanghani Sunday, February 06, 2011 4:09 PM Formating
    Sunday, February 06, 2011 4:06 PM
  • @Paras Sanghani: are you describing the same technique as darnold92 in his answer?

    My model is 5 levels deep: Country-Region-City-Street-Building. I could use Include() for each single query, but this is not what I want. I want to

     

    • read only once from database on application start
    • do all subsequent queries from local data, not database (for performance)
    • write to both context and database each time an entity is being added or deleted.

     

    Do you mean I should use dummy Countries.Include(...).Include(...).Include(...).Include(...).ToList() on start to preload database?

    @darnold92 you describes how to make a single List<entity> if I understand you correctly. But I have a tree so I will need thousants custom List<entity>. Do you mean that I should make a custom copy of my data tree using ESQL?

    Sunday, February 06, 2011 4:38 PM
  • On 2/6/2011 9:45 AM, Mikhail Poda wrote:
    > @darnold92: Thank you for you answers and guidance! I have a couple of
    > questions regarding your ESQL technique.
    >
    > * can I use it for an object tree with more then 2 levels (my model
    > is 5 levels deep: Country-Region-City-Street-Building)?
     
    You would have a List<T> for each object graph within the parent object.
    Or there world be 4 objects in the parent object, if only getting a
    single object from each level.
     
    > * can I use context to put new entities into database?
     
    You would need to create a new context by getting the parent and
    children to do CRUD operations with any objects that needed to CRU --
    Created, Updated, Deleted.
     
    > * what is the advantage of using ESQL vs. Linq in my case?
    >
     
    You do it yourself and query the model, instead of using Linq and Linq
    build the T-SQL query. The use of ESQ is faster than using Linq.
     
    • Marked as answer by Mickey Mousoff Tuesday, February 08, 2011 4:45 PM
    Tuesday, February 08, 2011 3:58 AM
  • Hi All,

    Thanks for your participate and selfness sharing.

    I agree with the esql is a better way.

    The tutorial for ESQL:

    http://innocraft.spaces.live.com/blog/cns!919A8CAC315ADF82!239.entry

    Have a good day.



    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 08, 2011 5:33 AM