Why Include and Reference().Load() don't work in Entity Framework Core

Question

In my ASP.NET Core MVC application I try to select orders and I need to show customer:

var orders = await _context.Orders
                           .Where(o => o.DeletedAt.HasValue == false)
                           .Include(o => o.TheCustomer)
                           .OrderByDescending(o => o.OrderDateTime)
                           .ToListAsync();

<td>
    @Html.DisplayFor( modelItem => item.TheCustomer.FullName )
</td>

But customer is empty (not null).

This doesn't work either:

foreach (var order in orders)
{
    _context.Entry(order).Reference(o => o.TheCustomer).Load();
}

Here is Order class:

public class Order : SoftDeletableEntity
{
    public DateTime OrderDateTime { get; set; }
    
    [Required]
    public virtual Customer TheCustomer { get; set; } = new();
    public virtual OrderStatus? TheOrderStatus { get; set; } = new();

    [Required]
    public virtual DeliveryType TheDeliveryType { get; set; } = new();
    // ...
}

What's wrong? Please help

I checked using Microsoft.EntityFrameworkCore; if it's not using System.Data.Entity.

I tried to use IQueryable.

Nothing helped.

I need to load customer to show name.

Answer 1

+1 you should not = new() on singular navigation properties, only on collections. For a test, add a .AsNoTracking() to your Orders query.

What I suspect may be happening is that earlier code has loaded the order(s) without the eager loading, where those orders would have been initialized with the blank Customer reference due to the = new(). Later, when you try to load them with the Include EF is executing a query, but when it retrieves each Order from the tracking cache it is finding that they already have a Customer assigned so it does not overwrite with the values loaded from the database, it effectively thinks you have set them to new, blank customers. Removing the = new() in this case should solve the issue. and is the most likely solution. Adding an .AsNoTracking() to the query would force EF to load the orders and customers from the database and would confirm the behaviour.

I don't recommend sending entities to the view engine to serve as models for MVC views, rather projecting to view models which can help avoid issues like this as neither the projections not the entities referenced are added to the tracking cache. If you do continue with binding to entities then I recommend using AsNoTracking() queries on read-only queries like this to have EF skip the steps of reading from, and adding the entities to the tracking cache.