Entity Framework orders include

Question

I am trying to find something like the following:

_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 throws the exception:

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

EDIT:

After further observation

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

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

Answer 1

It seems you cannot sort the collection of children in your query. Any of them are sorted after the request or load the child elements in the second request.

Similar question and answer here

Answer 2

The Include extension method is a simple DbQuery.Include . Internally, he does not execute expressions, but only analyzes them, i.e. Accepts member expressions and converts them to a path as a string. The path is used as input for DbQuery.Include .

Earlier it was proposed to improve the functionality of Include , for example. to partially download collections, including the Where clause. An order may be another change request. But, as you can see, due to the internal work of Include whole mechanism must be redesigned to implement such improvements. I do not see it in the current roadmap , so this may take some time ...

Answer 3

It will never work. EF include is an attempt to understand and translate everything into SQL, but you want a lot from it. Download all entities without sorting and .ToList () - and write an extension method for IEnumerable to get an ordered result.

Answer 4

Typically, if you use a bunch of inclusions, this is because you need to access the child properties in the view. What I do is order a child collection when I need to access it in a view.

For example, I can create some Include statements for the master / detail form. There is no point in ordering this on an initial EF request. Instead, why not arrange these child records at the presentation level when you really access them?

I may have a survey with several survey questions. If I want to present questions in a specific order, do it at the partial presentation level when I pass the child collection of the model to a partial view.

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

Answer 5

I use this code to order inclusion, using select and a function to organize the collection. Not the best, but it works fine if a small fee

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

Answer 6

Depending on the use case, you may not need to download in a separate request or sort afterwards.

In my case, I needed to order them when looping in the view, so I just ordered there

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

Answer 7

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

In short, never call Include after ToList

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