SQL requests in C# Entity Framework depending on lazy or eager loading

Question

I have a simple query with join, connecting two tables (one category has many products):

using (ProdContext db = new ProdContext())
{
    var query = from category in db.Categories
                join product in db.Products
                     on category.CategoryID equals product.CategoryID into productList
                select new
                       {
                           categoryName = category.Name,
                           products = productList
                       };

    foreach (var c in query)
    {
        Console.WriteLine("* {0}", c.categoryName);

        foreach (var p in c.products)
        {
             Console.WriteLine("   - {0}", p.Name);
        }
    };
}

For classes:

class Category
{
    public int CategoryID { get; set; }
    public String Name { get; set; }
    public List<Product> Products { get; set; }
}

class Product
{
    public int ProductID { get; set; }
    public String Name { get; set; }
    public int UnitsInStock { get; set; }
    public int CategoryID { get; set; }
}

class ProdContext : DbContext
{
    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }
}

and generally everything works fine, but I got confused when I started experimenting with eager and lazy loading, because no matter if I add .ToList() at the end of a query or not my SQL request always look like that:

SELECT 
    [Project1].[CategoryID] AS [CategoryID], 
    [Project1].[Name] AS [Name], 
    [Project1].[C1] AS [C1], 
    [Project1].[ProductID] AS [ProductID], 
    [Project1].[Name1] AS [Name1], 
    [Project1].[UnitsInStock] AS [UnitsInStock], 
    [Project1].[CategoryID1] AS [CategoryID1], 
FROM 
    (SELECT 
         [Extent1].[CategoryID] AS [CategoryID], 
         [Extent1].[Name] AS [Name], 
         [Extent2].[ProductID] AS [ProductID], 
         [Extent2].[Name] AS [Name1], 
         [Extent2].[UnitsInStock] AS [UnitsInStock], 
         [Extent2].[CategoryID] AS [CategoryID1], 
         CASE WHEN ([Extent2].[ProductID] IS NULL) 
            THEN CAST(NULL AS int) ELSE 1 END AS [C1]
     FROM  
         [dbo].[Categories] AS [Extent1]
     LEFT OUTER JOIN  
         [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID])  AS [Project1]
ORDER BY 
    [Project1].[CategoryID] ASC, [Project1].[C1] ASC

and as far as I understand it when I use .ToList() (eager loading) it should look like this, but when I use (default) lazy loading it should send many sql requests asking about all element of foreach loop separately. My question is - why there is no difference and always only one SQL is sent?

Gert Arnold Gert Arnold · Accepted Answer · 2019-11-14T20:00:07

... when I use .ToList() (eager loading)
... when I use (default) lazy loading

You're mixing up two different concepts. Using ToList() is not the same as eager loading, with lazy loading as the opposite. It is forced execution, with deferred execution as its counterpart.

Therefore, using ToList() or not never determines the number of SQL queries EF will generate when the LINQ query runs. Entity Framework 6 (your version) always tries to translate a LINQ query into one SQL statement. You have one LINQ statement and, hence, you'll get one SQL statement.

"Loading" in the various loading strategies always pertains to populating navigation properties. Eager loading is carried out by the Include method. For example:

var query = 
    from category in db.Categories
        .Include(c => c.Products)
    select category;

This returns categories with their Products navigation properties loaded.

Lazy loading is triggered by accessing navigation properties in an already executed LINQ query result. for example:

var query = db.Categories.ToList();

foreach (var c in query)
{
    Console.WriteLine("* {0}", c.categoryName);

    foreach (var p in c.Products) // <= new query triggered here.
    {
         Console.WriteLine("   - {0}", p.Name);
    }
};

For lazy loading to occur the navigation property should be defined as virtual:

class Category
{
    public int CategoryID { get; set; }
    public String Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

Usually though, eager loading is preferred over lazy loading because it is less "chatty" to the database.

SQL requests in C# Entity Framework depending on lazy or eager loading

1 Answers