Entity Framework (.NET Full Framework) Ordering Includes

I am trying to get something like the following to work:

_dbmsParentSections = FactoryTools.Factory.PdfSections
                        .Include(x => x.Children.OrderBy(y => y.Order).ToList())
                        .Include(x => x.Hint).Include(x => x.Fields)
                        .Where(x => x.FormId == FormId && x.Parent == null)
                        .OrderBy(o => o.Order)
                        .ToList();

The part that causes the exception is:

.Include(x => x.Children.OrderBy(y => y.Order).ToList())

EDIT:

Upon further observation,

_dbmsParentSections.ForEach(x => x.Children = x.Children.OrderBy(y => y.Order).ToList());

did the job for me (after the initial Factory call and without the Children.OrderBy).

It seems you cannot sort the children collection in your query. Either sort after the query or load the children in a second query.

Similar question and answer here

According to this documentation, starting with EF Core 5.0, you can sort by a property of your Included Entity:

 await context.Parents
    .OrderBy(parent => parent.Order)
    .Include(parent => parent.Children.OrderBy(child => child.Order))
    .ToListAsync();

The above example sorts Parent entities by their Order, as well as their Children entities by the Children entities' Order property.

The extension method Includeis a mere wrapper around DbQuery.Include. Internally it does not execute the expressions but only parses them, i.e. it takes their member expressions and converts them to a path as string. The path is used as input for DbQuery.Include.

It has been requested before to enhance the functionality of Include, e.g. to allow partly loaded collections by including a Where clause. Ordering could be another change request. But as you see, because of the internal working of Include the whole mechanism will have to be re-engineered to implement such enhancements. I don't see it on the current road map so it may take a while...

Depending on the use case you might not need to load in separate query or sort afterwards.

In my case I needed them ordered for when looping in the view so I just ordered there

@foreach (var subObject in Object.SubObjects.OrderBy(x=>x.Order))

I use this code por order the include, using a select and a function to order the collection. Is not the best but work fine if subcollection is small

   // GET: api/Tareas
    [HttpGet]
    public IEnumerable<Tarea> GetTareas()
    {
        var result = _context.Tareas
            .Include(p => p.SubTareas)
            .Select(p => SortInclude(p));
        return result;
    }

    private Tarea SortInclude(Tarea p)
    {
        p.SubTareas = (p.SubTareas as HashSet<SubTarea>)?
            .OrderBy(s => s.Position)
            .ToHashSet<SubTarea>();
        return p;
    }

This will never gona work. EF include is try to understand and translate everything to SQL, but you want to much from this. Load all entities without sorting and .ToList()-ing, and write an extension method for IEnumerable to get an ordered result.

Generally if you're using a bunch of includes, it's because you need to access child properties in a view. What I do is order the child collection when I need to access it in a view.

For example, I might build some Include statements for a master/detail form. There's no sense ordering this at the initial EF query. Instead, why not order these child records at the view level when you're actually accessing them?

I might have a survey with multiple survey questions. If I want to present the questions in a particular order at do it at the partial view level when I'm passing the model child collection to the partial view.

@Html.Partial("_ResponsesPartial",Model.SurveyResponses.OrderBy(x => 
x.QuestionId))

You should not convert an IQueryable type to IEnumerable and call Include because Include is not supported by IEnumerable type.

In short, never call Include after ToList

IQueryable = server side call (SQL)
IEnumerable = client side (loaded in memory)