Entity Framework and Lazy Loading

August 20, 2008

I’ve received a couple of request to write some of my previous posts in English so that all other 99.7% of the developers community in the world which finds these subjects interesting can understand what I’m writing.

The following post is a translation to English of this Hebrew post.

When Entity Framework (EF for short) was designed, Microsoft decided the loading of entities will be during run-time in a JIT like mechanism. They achieved this by using the lazy loading technique – access the database and load the entity for the first time only when someone asks for it.

There is some logic to this design – the wish to spare unnecessary work with the database. Still, there are some disadvantages to this design:

  1. In order to perform the lazy load, one must invoke the Load method of the RelatedEnd object (either EntityCollection or EntityReference) explicitly.
    This means that you must remember to put a load command before accessing the navigation property, otherwise you will get a NullReferenceException or worse – A bug in your application’s logic
  2. Every call to Load causes a call to the DB, so you must remember while code reviewing to check that prior to each Load operation you have a “If (!navig.IsLoaded)” condition (the IsLoaded is a property of the RelatedEnd object).
  3. In case you’ve built a code that iterates on a collection of entities and performs some operation on each of them, and that entity contains navigable properties, you will find yourself calling Load on each navigation property, for each entity in the collection, which means your DB will be accessed total of EntityCount X NavigablePropertiesCount – not very wise performance wise.

The first and second section is quite annoying to address, because you have to repeat these 2 rows each time you want to navigate a property. There is a solution for this problem by using Transparent Lazy Loading, but it’s not part of EF yet (the guys at MS says that they are considering it for V2).

As for the third section, there is a solution in EF for the iterated load, by using the Include method which you can call from your ObjectQuery (for each EntityType you want to query). You can pass to the method a string parameter that holds the name of the navigated property we want to pre-load, and we can even call this method a couple of time, with different navigable properties to pre-load a couple of entities.

What goes on in the DB when you activate the Include method? the query which is executed in the DB contains not only the “select” query for the main entity but also other queries to return the navigable ends. For example, given the following model:

And the following DB table structure:

And given a code that looks like this:

TestModel.TestEntities model = new TestModel.TestEntities();

var all = from a in model.Person.Include("Pets").Include("Address")
          select a;

foreach (var person in all.ToList())
{
    if (!person.Pets.IsLoaded)
        person.Pets.Load();
    if (!person.Address.IsLoaded)
        person.Address.Load();
    
    Console.WriteLine(
        string.Format ("{0} {1}\n{2}\n{3}",
            person.FirstName, 
            person.LastName,
            String.Join("\n", (from ad in person.Address
             select string.Format("{0} {1} {2}", ad.City, ad.Street, ad.House)).ToArray()),
             String.Join("\n", (from p in person.Pets
             select string.Format("{0} {1}", p.Name, p.Species)).ToArray())));
    Console.WriteLine();
}
 
When the ToList method will be invoked, the following query will be executed:
 
SELECT [UnionAll1].[Id] AS [C1], 
[UnionAll1].[FirstName] AS [C2], 
[UnionAll1].[LastName] AS [C3], 
[UnionAll1].[C2] AS [C4], 
[UnionAll1].[C1] AS [C5], [UnionAll1].[Id1] AS [C6], 
[UnionAll1].[Name] AS [C7], 
[UnionAll1].[Species] AS [C8], 
[UnionAll1].[PersonId] AS [C9], 
[UnionAll1].[C3] AS [C10], 
[UnionAll1].[C4] AS [C11], 
[UnionAll1].[C5] AS [C12], 
[UnionAll1].[C6] AS [C13], 
[UnionAll1].[C7] AS [C14]
FROM  (SELECT 
    CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    1 AS [C2], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[Species] AS [Species], 
    [Extent2].[PersonId] AS [PersonId], 
    CAST(NULL AS int) AS [C3], 
    CAST(NULL AS varchar(1)) AS [C4], 
    CAST(NULL AS varchar(1)) AS [C5], 
    CAST(NULL AS varchar(1)) AS [C6], 
    CAST(NULL AS int) AS [C7]
    FROM  [dbo].[Person] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Pets] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PersonId]
UNION ALL
    SELECT 
    2 AS [C1], 
    [Extent3].[Id] AS [Id], 
    [Extent3].[FirstName] AS [FirstName], 
    [Extent3].[LastName] AS [LastName], 
    1 AS [C2], 
    CAST(NULL AS int) AS [C3], 
    CAST(NULL AS varchar(1)) AS [C4], 
    CAST(NULL AS varchar(1)) AS [C5], 
    CAST(NULL AS int) AS [C6], 
    [Extent4].[Id] AS [Id1], 
    [Extent4].[City] AS [City], 
    [Extent4].[Street] AS [Street], 
    [Extent4].[House] AS [House], 
    [Extent4].[PersonId] AS [PersonId]
    FROM  [dbo].[Person] AS [Extent3]
    INNER JOIN [dbo].[Address] AS [Extent4] 
    ON [Extent3].[Id] = [Extent4].[PersonId]) AS [UnionAll1]
 
Long isn’t it? but for this table structure, the query is quite efficient.
 
Warning: beware of the number of includes you use and the number of levels you “walk” into your navigation tree – the more include methods you call, the bigger your query will become and the more inefficient it will become (have you ever tried to execute a left join on 4 large tables? not a pretty sight.
 
Another thing you should take into account – the include method tries to identify the navigated property and it’s mapping (in order to build the union part of the query) prior to executing the query, which means that if your entity is polymorphic (for example you want to load an EntitySet that holds both Person and Employee), you can only use the include method for the navigated properties of the base class (say Person), which means we don’t really have the option to perform an eager load – an implicit load of the entire entity structure, including all navigable properties, ours or our fathers (derived).
 
If you do wish to load a polymorphic entity, you’ll need to perform a couple of Include execution, every time with a different entity type from the hierarchy tree (and combine the results).
 
But it doesn’t end there – what happens if the both the entity and one of the navigated entity are of some base class (for example a Person that holds Cars and Cars is a polymorphic type)? you see where this is heading…
 
So for conclusion, lazy load is a bit problematic, we can use Include, but we have problems with it too. Eager loading isn’t yet supported but that too isn’t so simple to use – you’ll just have to find the suitable solution for your situation.
 
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

12 comments

  1. BertJuly 6, 2009 ב 4:30 pm

    I have serious problems with the approach EF has chosen here.

    The operation that I need to do is a calculation for a CAD drawing, based on the data.
    For positioning and calculating, I basically need all the data. Well, not all :) , but still
    a lot of data under one node.

    So, while you are using 3 tables, I need to address at least 20 to get all the data I need.
    Doing a big include here isn’t the right way to go, because, as you mention, the query gets HUGE.

    Doing lazy-loading the EF way (entity.PropertyReference.IsLoaded and entity.PropertyReference.Load()) is also not the right way to go, because of the many DB calls.

    I’m really bummed with this, and I can’t seem to figure out the right way to handle this problem with EF.

    Or I’m using a complete wrong design of code, but still, I wouldn’t know how to do it differently…

    Would you have any ideas or thoughts on this?

    Reply
  2. Ido FlatowJuly 6, 2009 ב 9:47 pm

    Hi Bert,
    I also saw what you’ve written in the EF forum and I’ve posted an answer there:
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3afc21e7-dcfb-493d-88af-73e45683d7f2/?prof=required

    Reply
  3. icons designsSeptember 24, 2012 ב 10:46 am

    It is removed (has mixed section)

    hpixel

    Reply
  4. SalisburyFebruary 1, 2013 ב 6:02 am

    Dealing with offset printing, I’ve noticed countless outlets for info that it’s consuming sometimes.
    Yet still I do appreciate your particular post at this site titled Entity Framework and Lazy Loading – Ido Flatow’s Blog Veni Vidi Scripsi.

    Reply
  5. SampleFebruary 9, 2013 ב 1:41 am

    With regard to large format printing, I have found too
    many articles for insight that it is overwhelming oftentimes.
    Yet still I do value your content here titled Entity Framework and Lazy Loading – Ido Flatow’s Blog Veni Vidi Scripsi.

    Reply
  6. GillisMarch 5, 2013 ב 1:22 pm

    That is a pretty good and easy post about banner printing and
    that’s quite refreshing.

    Reply
  7. AshbyMarch 25, 2013 ב 10:11 pm

    This is my first time pay a quick visit at here and i am truly
    impressed to read all at alone place.

    Reply
  8. ChampagneApril 21, 2013 ב 8:36 pm

    Thanks for finally talking about >Entity Framework and Lazy Loading – Ido Flatow’s Blog Veni Vidi Scripsi Reply

  9. CostaApril 21, 2013 ב 10:44 pm

    Overall the Saitek Obsidian Wireless mouse is a very good wireless solution for a mouse.
    it effectively pulls together a great design combining a comfortable ergonomic style, with a small and easily carried size.

    Using the wireless device is plug and play simple and
    it works very well at keeping the clutter of cables out of your way at the office
    or home.

    Reply
  10. SouthportAugust 14, 2013 ב 3:14 am

    MCM precious bat 2013 flower and summer a rugged advertising
    MCM, all products are made ??using the finest materials, workshop, purses using at most the most advanced materials, leather is lighten, persistent, waterproof and can acknowledge UV rays, MCM brand name was founded in 1976 in Munich, Germany, is the into to nothing of Hollywood superstar – Michael Cromer, the letters “MCM” role Lean, Birth, Munich. MCM industrialist supply lines to clothing, attire and leather-based. MCM pre-eminent started to round up leather goods, in the 1980s, the marque’s heyday, MCM forming, including jewelery, watches, perfumes, clothing, bags and beneath lifetime leather goods, etc., more than five hundred models of the product. It is chichi, sybaritic and useful products are truly popular.

    Reply
  11. ThomsonAugust 14, 2013 ב 5:31 am

    MCM carpet-bag 2013 blossom and summer a big advertising
    MCM, all products are made ??using the finest materials, innards, purses using at most the most advanced materials, leather is sincere, healthy, waterproof and can confront UV rays, MCM brand name was founded in 1976 in Munich, Germany, is the die of Hollywood superstar – Michael Cromer, the letters “MCM” walk off on Politesse, Base, Munich. MCM brand consequence lines to clothing, attire and leather-based. MCM first started to yield b set forth forth leather goods, in the 1980s, the emboss’s heyday, MCM origination, including jewelery, watches, perfumes, clothing, bags and auxiliary leather goods, etc., more than five hundred models of the product. It is fashion, splendid and judicious products are only popular.

    Reply
  12. ByattNovember 1, 2013 ב 5:32 pm

    Boa tarde,Como esta?,eu estava procurando umas
    ideias para prendas e dei de caras com o site.

    Fiquei fascinado com o seu texto e decidi adicionar o seu blog aos meus
    favoritos.

    irei ler os restantes artigos do seu blog pois contem muita informacao util.

    Desejo-lhe um resto de boa semana
    Ana Raquel

    Reply