CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-5
Serj Sagan
Serj Sagan

Reputation: 30178

Entity Framework (.NET Full Framework, NOT EF Core) 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).

EDIT: This is different from the way that EF Core handles this, since EF Core allows this: https://stackoverflow.com/a/66176285/550975

Upvotes: 86

Views: 78250

Answers (8)

Nata
Nata

Reputation: 1164

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.

Upvotes: 78

Иво Недев
Иво Недев

Reputation: 1590

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))

Upvotes: 6

FRL
FRL

Reputation: 776

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

Upvotes: 2

Charles Owen
Charles Owen

Reputation: 2842

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))

Upvotes: 1

user6314169
user6314169

Reputation: 5

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)

Upvotes: -4

Gert Arnold
Gert Arnold

Reputation: 109081

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...

Upvotes: 13

Peter Kiss
Peter Kiss

Reputation: 9319

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.

Upvotes: 1

Olav Nyb&#248;
Olav Nyb&#248;

Reputation: 11568

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

Upvotes: 53

Related Questions