Entity Framework (.NET Full Framework) Ordering Includes

Question

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

Thanks for providing the answer you found worked for me aswell — Ronan, Aug 31 '16 at 14:42
.Include(x => x.Children.OrderBy(y => y.Order)) Works for me! — Joe Hakooz, Feb 13 '21 at 18:51

score 62 · Answer 1 · answered Apr 07 '21 at 23:17

62

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.

answered Apr 07 '21 at 23:17

Nata

986
9
4

Works great for me! – puerile Mar 19 '22 at 13:23

score 54 · Accepted Answer · edited May 23 '17 at 12:18

54

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

edited May 23 '17 at 12:18

Community

1
1

answered Mar 13 '13 at 06:17

Olav Nybø

11,454
8
42
34

I've given you the credit because you were the first to answer and all three answers pretty much say the same thing – Serj Sagan Mar 14 '13 at 01:11
Is this still true with EF version 6.0 ? – Brad8118 Oct 13 '14 at 17:41

score 13 · Answer 3 · answered Mar 13 '13 at 15:17

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

score 6 · Answer 4 · answered Jan 24 '19 at 13:39

6

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

answered Jan 24 '19 at 13:39

Иво Недев

1,570
1
20
33

score 2 · Answer 5 · answered Nov 12 '18 at 00:10

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

score 1 · Answer 6 · answered Mar 13 '13 at 06:18

1

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.

answered Mar 13 '13 at 06:18

Peter Kiss

9,309
2
23
38

score 1 · Answer 7 · answered May 09 '18 at 21:48

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

score -4 · Answer 8 · edited May 10 '16 at 09:28

-4

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)

edited May 10 '16 at 09:28

Dovydas Šopa

2,282
8
26
34

answered May 10 '16 at 08:41

user6314169

5

Your point is true, but it has nothing to do with the question that is asked. I'm trying to do the `Include` before the `ToList` – Serj Sagan May 10 '16 at 15:56

Entity Framework (.NET Full Framework) Ordering Includes

8 Answers8

Linked

Related