Push queries on lazy navigation properties to database (a.k.a extra lazy loading)

[NotMyself]

Hi Team,

I am a former NHibernate user that is switching over to using Entity Framework. I wanted to share with you a scenario that surprised me when I first encountered it. You can see my write up and sample code here. The full source of the project is located here.

TLDR: My Account entity has a Transactions collection. Attempting to pull data out of the transactions collection loads all the transactions from the database, even though I have constrained all transaction access in the model to limit the number of them coming back.

You all may not even be focus on this right now, but it's a use case that is needed. Keep it in your back pocket. 👍

[rowanmiller]

We agree this would be a good thing to enable.

[NotMyself]

[joelmdev]

I'm a surprised this doesn't get more requests/attention- possibly another duplicate issue somewhere in github? There are questions in other forums of this same nature going back several years. I've had to find workarounds for this very problem more than a couple of times, and they usually result in robbing an Entity of functionality in order to avoid loading a large collection of related Entities. Would love to see this added.

[Cooksauce]

Have there been any decisions/discussions around this? What are the team's current thoughts? My use case revolves heavily around entities with navigation properties containing 10k - 500k items.

In this scenario, it would be much cleaner to give EF a property or method to override than having DbContext.Entry() calls everywhere.

Wrapping that in an extension is an option:
IQueryable<Employee> employeeQuery = department.CollectionQuery(x => x.Employees, dbcontext);

... but that's not as nice as something like:
IQueryable<Employee> employeeQuery = department.NavigationQuery(x => x.Employees);

... or even further:
IQueryable<Employee> employeeQuery = department.EmployeesQuery;

Defining The Entity
From an entity definition perspective, you could give EF a method/property to override. This could look like:

public class Department
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string SomeOtherData { get; set; }

    public virtual IList<Employee> Employees { get; set; }
    
    // This could be nice since it could function to access the querys for every navigation property. You could also move this into a base 'Entity' class and have everything inherit from that.
    public virtual IQueryable<TNavigation> NavigationQuery<TNavigation>(Expression<Func<Department, TNavigation>> selector) => Employees.AsQueryable();
}

public class Employee
{
    public int Id { get; set; }

    public int DisplayOrder { get; set; }

    public int DepartmentId { get; set; }

    public virtual Department Department { get; set; }
}

OR

public class Department
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string SomeOtherData { get; set; }

    public virtual IList<Employee> Employees { get; set; }

    // Notice getter only to since this would reference the underlying Employees list above
    public virtual IQueryable<Employee> EmployeesQuery { get; }
}

public class Employee
{
    public int Id { get; set; }

    public int DisplayOrder { get; set; }

    public int DepartmentId { get; set; }

    public virtual Department Department { get; set; }
}

// Could be set through the fluent api via:
builder.Entity<Department>().Property(x => x.Employees).HasQuery(x => x.EmployeesQuery);

What are everyone's thoughts? How else can EF effectively abstract a large navigation property? All of the current workarounds inhibit EF's ability to allow consumers to interact with these properties as if they were in memory.

Couldn't these all simply be implemented as wrappers around the EntityEntry<>.Collection().Query() method? @ajcvickers

[ajcvickers]

@Cooksauce I think we would want to do this without requiring the use of dynamic proxies. The implementation probably isn't too different from regular lazy loading in that respect.

[Cooksauce]

@ajcvickers What are the team's thoughts on implementing this? This issue is over 4 years old, have there been any recent discussions about it?

[ajcvickers]

@Cooksauce It's not high on our priority list, and the issue only has one vote, so I don't expect it's something we will get to any time soon.

[jimmyp]

It would be really great to see this implemented. I have exactly the same issue

[LucStr]

Is there an update to this?
Personally I think it would be great if EF would keep it's current implementation as is, but if the Navigation Property is of Type IQueryable, it would automatically wrap the code in

_dbContext
                .Entry(entity)
                .Collection(x => x.Collection)
                .Query()

instead of throwing an InvalidOperationException