Entity Framework Eager Loading and Lazy Loading Produce Same Queries

If you are using Entity Framework with Lazy Loading or Eager Loading you may realize that both work the same way. With Lazy Loading, you have to access the property and Entity Framework load the property on-demand. On the other side, with Eager Loading, we need to specify the property to load before accessing it. Lazy Loading has the disadvantage that you must have virtual property and has some overhead code that is executed to remember if it must load from the database or from the context, while Eager Loading lets you handle everything so you can optimized your database calls.

In a scenario where you have a one to one relationship, for example a Person that has an House.

var person = new Person {Id = 1, Name = "SeededPerson", BirthDate = new DateTime(1900, 1, 1), Friends = new Collection<Person> {person2, person3}};
 person.Residence = new House {Id = 1, Address = new Address{City="Montreal", Number = 123, Street = "Owl"}, Price = 350000};

You can load the Residence by using Eager Loading this way:

private static void LazyLoadingAndEagerLoadingSameResult()
    using (var context = new YourContext())
        Console.WriteLine("No Lazy Loading, Eager Loading");
        var person = context.Persons.Find(1);
        Console.WriteLine("City is " + person.Residence.Address.City);

If we kick in the Microsoft SQL Profiler, we see two queries. One that load the Person and one that load the Residence object.

    [Limit1].[Id] AS [Id], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[BirthDate] AS [BirthDate], 
    [Limit1].[Residence_Id] AS [Residence_Id], 
    [Limit1].[House_Id] AS [House_Id]
    FROM ( SELECT TOP (2) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[BirthDate] AS [BirthDate], 
        [Extent1].[Residence_Id] AS [Residence_Id], 
        [Extent1].[House_Id] AS [House_Id]
        FROM [dbo].[People] AS [Extent1]
        WHERE [Extent1].[Id] = @p0
    )  AS [Limit1]

    [Extent2].[Id] AS [Id], 
    [Extent2].[Price] AS [Price], 
    [Extent2].[Address_Street] AS [Address_Street], 
    [Extent2].[Address_Number] AS [Address_Number], 
    [Extent2].[Address_City] AS [Address_City], 
    [Extent2].[Owner_Id] AS [Owner_Id]
    FROM  [dbo].[People] AS [Extent1]
    INNER JOIN [dbo].[Houses] AS [Extent2] ON [Extent1].[Residence_Id] = [Extent2].[Id]
    WHERE ([Extent1].[Residence_Id] IS NOT NULL) AND ([Extent1].[Id] = @EntityKeyValue1)

The Lazy Loading version is similar but without the Reference/Load line.

using (var context = new YourContext())
    context.Configuration.LazyLoadingEnabled = true;
    Console.WriteLine("Lazy Loading, No Eager Loading");
    var person = context.Persons.Find(1);
    Console.WriteLine("City is " + person.Residence.Address.City);

It also require you to change the Person class to have the Residence to be virtual.

public class Person
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }

    public ICollection<Person> Friends { get; set; }

    public virtual House Residence { set; get; }

If you forget to set the virtual, you will not receive an error message about the virtual but about a NullReferenceException. Unfortunately, the exception does not speak for itself! You have to remember to change the property to virtual.

You can find the source code of this article on GitHub or inside this Zip File.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.