Feature request: IQueryable navigation properties

[Neme12]

It would be nice if an entity could have an IQueryable<T> navigation property for related entities of type T in addition to basic list/collection types, so that we can avoid having to go back to the DbSet and filtering entites T based on their navigation property in order to avoid loading all the related entities from the database when we need to do some additional filtering and/or ordering of those related entities.

For those who consider entity types to be an integral part of their application architecture or even pass their entity objects directly into views without using view models, this may seem a little odd. Why would you pass an IQueryable into the view? Isn't an entity supposed to be an abstraction?

Maybe. It's a very limiting abstraction because the entity type has to be a simple POCO with public mutable properties. That often won't do in order to make these the actual business logic objects. If I'm using entity types just as an access to the database and always build some other classes and data structures out of them, having an IQueryable navigation would not only be useful but makes a lot of sense.

[smitpatel]

Can you give some code snippets example?

[Neme12]

Sure.

Here is our data model:

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; }
}

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

    public int DisplayOrder { get; set; }

    public int DepartmentId { get; set; }

    public virtual Department Department { get; set; }
}

Then I'm creating a view model from the database entity for passing into the view. Only some properties will be transfered. Some properties might need to be converted. The Employees property will need to be ordered. Also notice that the constructor is internal - I'm not exposing any database entities into the view:

public sealed class DepartmentViewModel
{
    internal DepartmentViewModel(Department department, ILocalizationService localizationService)
    {
        Name = localizationService.Localize(department.Name);

        Employees = department.Employees
            .OrderBy(employee => employee.DisplayOrder)
            .Select(employee => new EmployeeViewModel(employee, localizationService))
            .ToList();
    }

    public string Name { get; }

    public IReadOnlyList<EmployeeViewModel> Employees { get; }
}

This works fine but the OrderBy is done after-the-fact on the server as opposed to in the database. The workaround would be to not use the Employees property at all in this case and instead get the employees belonging to a particular department the "manual" way:

public sealed class DepartmentViewModel
{
    internal DepartmentViewModel(Department department, DbSet<Employee> employees, ILocalizationService localizationService)
    {
        Name = localizationService.Localize(department.Name);

        Employees = employees.Where(employee => employee.DepartmentId == department.Id)
            .OrderBy(employee => employee.DisplayOrder)
            .Select(employee => new EmployeeViewModel(employee, localizationService))
            .ToList();
    }

    public string Name { get; }

    public IReadOnlyList<EmployeeViewModel> Employees { get; }
}

It would be nice to keep the same code as in the first example but have Employees be an IQueryable<Employee> so that the OrderBy is part of the query.

[Neme12]

In addition to OrderBy, you can imagine adding filtering too: .Where(employee => employee.IsActive) in order to show in the view. It would be nice if department.Employees just returned employees.Where(employee => employee.DepartmentId == department.Id) where employees is the DbSet<Employee>. The property would of course have to be virtual in order for entity framework to be able to do this. It seems though like it could be a lightweight wrapper of the query.

[smitpatel]

In a way you are asking for Ordered/Filtered includes. While instead of using Include you are using lazy loading to load the navigation property but the issue remains the same about tracking which has been discussed here in detail #1833. A brief recap would be, if you are applying filter then what are going to do with the navigation's status and detecting changes in the navigation. In your particular case, you don't encounter that issue exactly since you are just assigning the results (with projection to view model type) in your VM. At that point, what you want to use is not part of materializing entity (since EF cannot do any change tracking of it anyway), rather just a short-hand to be used. You can define your IQueryable property on your EntityType - mark it as NotMapped and write getter code in the property which does the "manual" trick. You can inject DbContext in the constructor of entity through materialization already.

[ajcvickers]

Notes from triage: there are a couple of existing concepts touched on here:

• The general mechanism for getting an IQueryable for a navigation property already exists--see code snippet below
• The idea that this is made available via navigation properties is a variation on the extra-lazy-loading pattern covered by Push queries on lazy navigation properties to database (a.k.a extra lazy loading) #1204. We should consider this when addressing that issue, although it's not clear that just exposing navigations as IQueryable properties is the correct approach.
• Closing this as a duplicate of Push queries on lazy navigation properties to database (a.k.a extra lazy loading) #1204 since this is where it would fit in if we chose to do something like this.

var department = context.Set<Department>().Single();

var employees = context
    .Entry(department)
    .Collection(e => e.Employees)
    .Query()
    .OrderBy(employee => employee.DisplayOrder)
    .ToList();