How avoid that EF Core 3.1 load related entities on where clause

Question

I'm using EF core 3.1 to get my data from DB SQL Server 2019 in an API used by Android/iOS apps. The users have been working fine, but some of the APIs are taking a long time. I'm checking and the issue seems to be because the where clause is doing a loading of the related entities. Here an example:

Business Layer I'm getting the user with the username and after it I'm getting all the historical orders for that user.

var account = await accountService.GetByEmailOrUsername(userName);
var orderHistory = (await dataService.GetByField<Order>(model => model.UserId == account.Id && model.TotalHours != 0)).ToList();

Data Layer This is my GetByField method, I'm using an expression for the where and some routes if I want to load explicit related entities for the object.

public Task<IEnumerable<T>> GetByField<T>(Expression<Func<T, bool>> where, params string[] routes) where T : class, new()
{
    IQueryable<T> inc = context.Set<T>();
    if (routes.Length > 0)
    {
        foreach (string route in routes)
        {
            inc = inc.Include(route);
        }
    }
    var results = inc.Where(where).AsEnumerable();
    return Task.FromResult(results);
}

The result for GetByField using the clause model => model.UserId == account.Id is the orders with User entity loaded and the internal info loaded also.

But if try without the userId in the where clause, like this await dataService.GetByField<Order>(model => model.TotalHours != 0) the User entity is not loaded.

Why is happening this? How can I disable this behavior? I was checking with Lazy loading but this is not changing the behavior at all, also I'm trying removing the virtual tag from all models, but this not working either.

Thanks

Answer 1

But if try without the userId in the where clause, like this await dataService.GetByField<Order>(model => model.TotalHours != 0)

My guess would be that you are not only deleting the clause from the GetByField call but also the following is removed:

var account = await accountService.GetByEmailOrUsername(userName);

EF Core will not load data referenced in the Where clause (check out the generated SQL) but it will perform relationship fix-up for already tracked entities. One thing you can try is to add explicit AsNoTracking call but this will prevent you from updating data after that (so it is usable only for readonly calls):

public Task<IEnumerable<T>> GetByField<T>(Expression<Func<T, bool>> where, params string[] routes) where T : class, new()
{
    IQueryable<T> inc = context.Set<T>()
        .AsNoTracking(); // <- here
    if (routes.Length > 0)
    {
        foreach (string route in routes)
        {
            inc = inc.Include(route);
        }
    }
    var results = inc.Where(where).AsEnumerable();
    return Task.FromResult(results);
}

Read more:

• Tracking vs. No-Tracking Queries

• Eager Loading of Related Data:

  Entity Framework Core will automatically fix-up navigation properties to any other entities that were previously loaded into the context instance. So even if you don't explicitly include the data for a navigation property, the property may still be populated if some or all of the related entities were previously loaded.