Explicit Loading in Entity Framework Core

Back to: ASP.NET Core Web API Tutorials

Explicit Loading in Entity Framework Core

In a real-world data-driven application such as an E-Commerce System, we often have multiple related entities connected through relationships, for example, an Order belongs to a Customer, has multiple OrderItems, and references ShippingAddress and BillingAddress. Please read our previous articles on Eager Loading and Lazy Loading in EF Core.

Sometimes, loading all related entities upfront (Eager Loading) or automatically when accessed (Lazy Loading) may not be ideal. We may want more control over when and what related data to load; that’s where Explicit Loading comes in.

Explicit Loading allows us to manually trigger the loading of related entities after the main entity has been retrieved. It gives us precision and performance control, especially when dealing with large datasets or conditional loading requirements.

What Is Explicit Loading in Entity Framework Core?

Explicit Loading is a technique in EF Core where the developer manually loads related entities only when they are needed, using methods such as:

  • Entry(entity).Reference(…).LoadAsync() — for single related entities (one-to-one or many-to-one)
  • Entry(entity).Collection(…).LoadAsync() — for related collections (one-to-many or many-to-many)

This approach gives developers the ability to load relationships selectively, rather than fetching all related data automatically. It is particularly useful in scenarios where the data requirements depend on business logic or runtime conditions.

Real-World Analogy

Imagine you are viewing an Order in an admin dashboard. Initially, you might only want to see basic order details (order date, total amount, and status). Later, if the admin clicks “View Customer Details,” you can explicitly load the Customer data only at that point. Similarly, if the admin clicks “View Items,” you load OrderItems and their Products.

Explicit Loading works like an on-demand information system; you decide exactly what to fetch and when, instead of letting EF Core do it automatically.

How to Implement Explicit Loading in Entity Framework Core

EF Core provides methods through the DbContext.Entry() API to explicitly load related entities. These methods are asynchronous and can be used selectively to fetch either reference navigation properties or collection navigation properties.

Step 1: Load Reference Navigation Property

Used when we need to load a single related entity (like Order → Customer or Order → ShippingAddress):

await _context.Entry(order)
              .Reference(o => o.Customer)
              .LoadAsync();
Step 2: Load Collection Navigation Property

Used when we need to load a collection of related entities (like Order → OrderItems):

await _context.Entry(order)
              .Collection(o => o.OrderItems)
              .LoadAsync();
Step 3: Load Nested Relationships Conditionally

You can also combine Explicit Loading with conditions:

await _context.Entry(order)
              .Collection(o => o.OrderItems)
              .Query()
              .Where(i => i.Quantity > 1)
              .LoadAsync();

This approach allows filtering before the data is loaded, for example, loading only items with a quantity greater than one.

Example: Demonstrating Explicit Loading in the Order Controller

Let’s create a simple demonstration of Explicit Loading using the OrderController. So, please modify the OrderController as follows. The following example code is self-explanatory; please read the comment lines for better understanding.

using ECommerceApp.Data;
using ECommerceApp.DTOs;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace ECommerceApp.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class OrderController : ControllerBase
    {
        private readonly AppDbContext _context;

        public OrderController(AppDbContext context)
        {
            _context = context;
        }

        // ======================================================
        // GET: api/order/explicit/{id}
        // PURPOSE:
        //     Demonstrates Explicit Loading in EF Core.
        //     Loads related entities manually, only when needed.
        // ======================================================
        [HttpGet("explicit/{id:int}")]
        public async Task<IActionResult> GetOrderWithExplicitLoading(int id)
        {
            // STEP 1: Load only the main entity first.
            var order = await _context.Orders
                .AsNoTracking()
                .FirstOrDefaultAsync(o => o.OrderId == id);

            if (order == null)
                return NotFound($"Order with ID {id} not found.");

            // STEP 2: Load related entities explicitly when required.

            // Load Customer reference
            await _context.Entry(order).Reference(o => o.Customer).LoadAsync();

            // Load OrderItems collection
            await _context.Entry(order).Collection(o => o.OrderItems).LoadAsync();

            // Load OrderStatus (single related entity)
            await _context.Entry(order).Reference(o => o.OrderStatus).LoadAsync();

            // Load nested relationship: OrderItems → Product → Category
            foreach (var item in order.OrderItems)
            {
                await _context.Entry(item).Reference(i => i.Product).LoadAsync();
                await _context.Entry(item.Product).Reference(p => p.Category).LoadAsync();
            }

            // STEP 3: Construct DTO manually (avoiding circular references)
            var response = new
            {
                order.OrderId,
                order.OrderDate,
                order.TotalAmount,
                Status = order.OrderStatus?.Name,
                Customer = order.Customer == null ? null : new
                {
                    order.Customer.CustomerId,
                    order.Customer.Name,
                    order.Customer.Email,
                    order.Customer.Phone
                },
                Items = order.OrderItems?.Select(i => new
                {
                    i.ProductId,
                    ProductName = i.Product?.Name,
                    Category = i.Product?.Category?.Name,
                    i.Quantity,
                    i.UnitPrice
                })
            };

            return Ok(response);
        }
    }
}
Code Explanation
  1. Step 1 – Load Main Entity: EF Core fetches only the Order table data.
  2. Step 2 – Load Related Data Explicitly: Using the Entry() API, EF Core loads related entities (Customer, OrderItems, OrderStatus, etc.) only when explicitly instructed.
  3. Step 3 – Load Nested Entities: The loop demonstrates loading multi-level relationships, such as OrderItems → Product → Category.
  4. Step 4 – Projection into DTO: The response is shaped into an anonymous object, ensuring clean JSON serialization and avoiding circular references.

How Explicit Loading Works Internally in EF Core

When we call methods like Load() or LoadAsync(), EF Core performs the following steps internally:

  1. Context Lookup: EF Core checks whether the related data is already loaded and tracked in the current DbContext.
  2. SQL Query Generation: If not, EF Core dynamically generates a separate SQL query for the specified relationship.
  3. ChangeTracker Update: The fetched entities are added to the current DbContext’s ChangeTracker, linking them to the primary entity.
  4. In-Memory Association: The relationship is established between the main and related entities in memory. Any subsequent access to the same navigation property will now use the in-memory data without issuing a new query.

Difference Between Lazy Loading and Explicit Loading

Both Lazy Loading and Explicit Loading delay the retrieval of related entities, meaning that when you query the primary entity (for example, Order), EF Core doesn’t automatically include its related data (like Customer, OrderItems, or Product). However, the real difference lies in who decides when and how the related data is loaded.

Lazy Loading – Automatic

With Lazy Loading, EF Core automatically loads related data on demand, that is, the moment you access a navigation property in your code.

You don’t explicitly tell EF to load related data. Instead, EF Core creates proxy objects for your entities, and when your code touches a navigation property like order.Customer or order.OrderItems, EF Core silently runs a new SQL query behind the scenes to fetch that data.

This behavior is convenient for quick prototypes or small datasets, but it’s often inefficient and unpredictable in real-world applications.

Explicit Loading – Manual

With Explicit Loading, you take complete control. You decide exactly when EF Core should query the database and which related entities it should load.

EF Core doesn’t automatically load anything; you must explicitly call .Load() or .LoadAsync() on the navigation property. This approach is ideal for performance-critical applications because you can:

  • Fetch only the data you truly need.
  • Apply SQL-level filters before the query runs.
  • Avoid the N+1 query problem that happens with Lazy Loading.

The Two Major Technical Differences

  1. Conditional filtering: Lazy Loading cannot apply conditions in SQL. It always loads all related entities. Explicit Loading, however, allows you to use .Query().Where(…) to load only the required subset of data directly from the database.
  2. N+1 Query Problem: Lazy Loading executes one query for the primary entity and an additional query for every related entity accessed (1 + N queries total). Explicit Loading executes only the queries you explicitly call, which can be optimized, batched, and filtered.
Real-World Scenario – Get High-Value Orders with Active Products Only

We want an admin endpoint that needs to fetch all orders placed in the last 30 days, where:

  • Each order that has at least one OrderItem with Quantity ≥ 3.
  • The related Product is Active.
  • Only Active Customers should be included.

Challenge: We don’t want to load all customers or order items, only those that meet these conditions. This is where Lazy Loading and Explicit Loading behave very differently.

Lazy Loading Example – Automatic but Blind

In this example, EF Core loads related entities automatically, but it has no way to apply filters before doing so. It ends up fetching everything, even unwanted data.

using ECommerceApp.Data;
using Microsoft.AspNetCore.Mvc;

namespace ECommerceApp.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class OrderController : ControllerBase
    {
        private readonly AppDbContext _context;
        public OrderController(AppDbContext context)
        {
            _context = context;
        }

        // ==========================================================
        // GET: api/order/lazy-highvalue
        // PURPOSE:
        //     Demonstrates Lazy Loading’s automatic behavior and its key limitation —
        //     inability to apply conditional filters before data is fetched.
        //     This example also highlights how the N+1 Query Problem occurs in practice.
        // ==========================================================
        [HttpGet("lazy-highvalue")]
        public IActionResult GetHighValueOrdersLazy()
        {
            // ======================================================
            // STEP 1: Load all active orders placed in the last 30 days.
            // This executes a SINGLE SQL query to fetch Orders only.
            // Related entities (Customer, OrderItems, Product) are NOT loaded yet.
            // =====================================================
            var orders = _context.Orders
                                 .Where(o => o.IsActive && o.OrderDate >= DateTime.Now.AddDays(-30))
                                 .ToList();

            var result = new List<object>();

            // =====================================================
            // STEP 2: Iterate through each Order and access navigation properties.
            // IMPORTANT: At this point, Lazy Loading kicks in automatically.
            // EF Core dynamically generates "proxy" objects for these entities.
            // When you access a navigation property (like order.Customer or order.OrderItems),
            // EF Core INTERCEPTS the property access and issues a NEW SQL query behind the scenes.
            //
            // This is where the "N+1 Query Problem" occurs:
            //     - 1 query to load all Orders
            //     - +N queries for each Order’s Customer
            //     - +N more queries for each Order’s OrderItems
            //     - +extra queries if OrderItems reference Products
            //   Resulting in dozens or hundreds of hidden queries.
            // =====================================================
            foreach (var order in orders)
            {
                // Accessing this property fires an automatic SELECT query like:
                // SELECT * FROM Customers WHERE CustomerId = @Order.CustomerId
                var customer = order.Customer; // Triggers a separate SQL query per order.

                // Accessing this property also triggers a new SELECT query like:
                // SELECT * FROM OrderItems WHERE OrderId = @Order.OrderId
                // (and additional queries if Lazy Loading is also enabled for Product)
                var items = order.OrderItems; // Another separate SQL query per order.

                // We want to find only:
                //   - Orders with Active Customers
                //   - OrderItems with Quantity ≥ 3 and Product.IsActive = true
                // But Lazy Loading already fetched ALL data for every order.
                // So the filtering below happens in MEMORY, not in SQL.
                // This means unnecessary rows are fetched from the database.
                var validItems = items?
                    .Where(i => i.Quantity >= 3 && i.Product.IsActive)
                    .ToList(); // Filter applied in memory, not at DB level.

                // Even though we check here for active customers,
                // the Customer entity was already fully loaded by EF Core earlier.
                // There’s no way to prevent EF from loading all customers upfront.
                if (customer != null && customer.IsActive && validItems?.Any() == true)
                {
                    result.Add(new
                    {
                        order.OrderId,
                        CustomerName = customer.Name,
                        FilteredItemCount = validItems.Count,
                        TotalAmount = order.TotalAmount
                    });
                }
            }

            // ======================================================
            // STEP 3: Return the results.
            // The API returns only filtered data, but the real issue is PERFORMANCE:
            //   - EF Core already fired multiple hidden queries.
            //   - Filtering was done in memory instead of in SQL.
            //   - This approach does not scale for large datasets.
            // ======================================================
            return Ok(new
            {
                Message = "Lazy Loading fetched *all* related data, even those not needed. Filtering happened in memory.",
                TotalOrders = result.Count,
                SampleResult = result.Take(3)
            });
        }
    }
}
What’s Wrong Here

Even though the final output looks correct, EF Core executed dozens (or hundreds) of hidden SQL queries:

  • One for the base Orders
  • One per Order.Customer
  • One per Order.OrderItems
  • And possibly one per OrderItem.Product

That’s the N+1 problem: 1 query for orders + N queries for each navigation property. Worse, all filtering occurs in memory after everything has been loaded.

Filtering Problem

Even though we want only:

  • Active Customers
  • Products with Quantity ≥ 3

EF Core still fetched everything and filtered later. This wastes:

  • Database I/O — fetching unnecessary rows.
  • Application memory and CPU — filtering large data sets.
  • Network bandwidth — transferring too much data between the DB and the app.

Lazy Loading = convenient but uncontrollable.

Explicit Loading Example – Manual, Conditional, and Efficient

Now let’s address the same requirement using Explicit Loading, where you control what to load and when.

using ECommerceApp.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace ECommerceApp.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class OrderController : ControllerBase
    {
        private readonly AppDbContext _context;

        public OrderController(AppDbContext context)
        {
            _context = context;
        }

        // ========================================================
        // GET: api/order/explicit-highvalue
        // PURPOSE:
        //     Demonstrates Explicit Loading with conditional filters.
        //     This approach gives complete control over:
        //         What related data to fetch
        //         When to fetch it
        //         How to apply SQL-level filters
        //     Unlike Lazy Loading, EF Core does NOT fire automatic queries behind the scenes.
        // ========================================================
        [HttpGet("explicit-highvalue")]
        public async Task<IActionResult> GetHighValueOrdersExplicit()
        {
            // =====================================================
            // STEP 1: Load the base entities (Orders)
            // This executes ONE SQL query that fetches all active orders placed in the last 30 days.
            // Note:
            //   - No related data (Customer, OrderItems, Product) is loaded yet.
            //   - We use AsNoTracking() for performance since we’re only reading data.
            var orders = await _context.Orders
                                       .Where(o => o.IsActive && o.OrderDate >= DateTime.Now.AddDays(-30))
                                       .AsNoTracking()
                                       .ToListAsync();

            if (!orders.Any())
            {
                // If no orders found, return early to avoid extra database queries.
                return NotFound("No active orders found in the last 30 days.");
            }

            // =======================================================
            // STEP 2: Load only ACTIVE customers for these orders
            // Lazy Loading would have fetched *every* customer (active or inactive) per order automatically.
            // Here, we explicitly select only customers who are active, using a single optimized SQL query.
            var activeCustomerIds = orders
                                    .Select(o => o.CustomerId)
                                    .Distinct()
                                    .ToList();

            var activeCustomers = await _context.Customers
                                                .Where(c => activeCustomerIds.Contains(c.CustomerId) && c.IsActive)
                                                .AsNoTracking()
                                                .ToListAsync();

            // ========================================================
            // STEP 3: Manually attach the loaded Customer objects to each Order
            // This step binds the customer from our manually loaded list to the corresponding Order.
            // Since we filtered customers at the SQL level, we know every Customer here is already active.
            //
            // Note:
            //   - The null-forgiving operator (!) is used because we know each Order has a valid CustomerId.
            //   - Unlike Lazy Loading, no hidden queries are triggered here — it's a pure in-memory assignment.
            foreach (var order in orders)
            {
                order.Customer = activeCustomers.FirstOrDefault(c => c.CustomerId == order.CustomerId)!;
            }

            // ========================================================
            // STEP 4: Explicitly load only required OrderItems
            // Instead of EF Core automatically fetching ALL OrderItems (like Lazy Loading does),
            // we can apply conditional SQL filters directly here using the .Query() method.
            //
            // Filters:
            //   - Include only items where Quantity ≥ 3
            //   - Include only items whose Product is Active
            //
            // The .Include(i => i.Product) ensures that the Product entity is eagerly loaded together
            // for each OrderItem in the same query.
            //
            // This ensures that only the relevant OrderItems and Products are fetched from the database.
            // Each LoadAsync() here executes one precise, optimized SQL query per order.
            // =========================================================
            foreach (var order in orders)
            {
                await _context.Entry(order)
                    .Collection(o => o.OrderItems)   // Access the OrderItems navigation property
                    .Query()                         // Begin building a query for this collection
                    .Where(i => i.Quantity >= 3 && i.Product.IsActive)  // Apply conditional SQL filter
                    .Include(i => i.Product)          // Include related Product data
                    .LoadAsync();                     // Execute query explicitly
            }

            // ==========================================================
            // STEP 5: Build the final filtered result for output
            // Now that we’ve manually controlled what was loaded:
            //   - Each Order contains only Active Customers.
            //   - Each Order’s OrderItems collection contains only products meeting our filter criteria.
            //
            // The .Where() here ensures we only project orders that meet both relational conditions.
            var filteredOrders = orders
                .Where(o => o.Customer != null && o.OrderItems.Any()) // Keep only valid, filtered orders
                .Select(o => new
                {
                    o.OrderId,
                    CustomerName = o.Customer!.Name,
                    FilteredItemCount = o.OrderItems.Count,
                    TotalAmount = o.TotalAmount
                })
                .ToList();

            // ==========================================================
            // STEP 6: Handle case where no orders matched after filtering
            // If no Order met the conditions (active customer, min quantity, active products),
            // return an appropriate response message.
            if (!filteredOrders.Any())
            {
                return Ok(new
                {
                    Message = "No matching orders found after applying conditional filters.",
                    TotalOrders = 0
                });
            }

            // =========================================================
            // STEP 7: Return the final result
            // This approach ensures:
            //   - Minimal and controlled SQL queries
            //   - SQL-level filtering (not in-memory)
            //   - No N+1 problem
            //   - Full transparency and performance predictability
            //
            // Summary of executed queries:
            //   1️ Orders (base query)
            //   2️ Customers (filtered by active)
            //   3️ OrderItems (filtered by Quantity & Product status, per order)
            return Ok(new
            {
                Message = "Explicit Loading with precise filters. Only Active Customers and Products (Quantity ≥ 3) fetched.",
                TotalOrders = filteredOrders.Count,
                SampleResult = filteredOrders.Take(3)
            });
        }
    }
}

With Explicit Loading, we:

  • Avoid the N+1 query problem.
  • Fetch only the data we need.
  • Apply SQL-level conditions for performance.
  • Keep our data access predictable and maintainable.
Comparison with Eager and Lazy Loading

Here’s how they conceptually differ:

  • Eager Loading – loads everything upfront using joins.
  • Lazy Loading – loads data automatically when a navigation property is accessed.
  • Explicit Loading – loads data manually and selectively, giving you the highest level of control.

Explicit Loading in Entity Framework Core is like having a manual gear system in your data access; you decide what to fetch, when, and how much. It provides more control and predictable performance, especially in complex or large-scale applications where not all relationships are always needed.

While Eager Loading focuses on completeness and Lazy Loading on convenience, Explicit Loading is about precision, fetching exactly what your application requires, no more and no less.

Leave a Reply

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