OrderBy in Include child using EF Core

Question

In my .NET Core / EF Core application I have a model with a nested list of child objects. When I retrieve an instance, I need the nested list to be ordered by one of the child's properties.

What is the correct way to sort this list so that it always returns sorted correctly from my database?

Right now I do it after loading from database:

public async Task<Parent> GetParent(int id)
{
    var result = await context.Parents
        .Include(p => p.Children)
        .SingleOrDefaultAsync(p => p.Id == id);

    result.Children = result.Children.OrderBy(c => c.Sequence).ToList();

    return result;
}

.Include(p => p.Children.OrderBy(c => c.Sequence)) you can simply add it in the include statement (as said @Kolazomai), this will retrieve the children already ordered as you wish without the need to apply a new condition — Feras, Dec 07 '21 at 15:03

score 25 · Accepted Answer · answered Feb 12 '21 at 17:20

25

Starting with Entity Framework Core 5.0, you can sort (OrderBy) and filter (Where) directly in the Include statement (with some restrictions). See the Microsoft Documentation.

Your statement can therefore be simplified like this:

    public async Task<Parent> GetParent(int id)
    {
        return await context.Parents
            .Include(p => p.Children.OrderBy(c => c.Sequence))
            .SingleOrDefaultAsync(p => p.Id == id);
    }

This is a nice step forward for EF Core in my opinion.

answered Feb 12 '21 at 17:20

Kolazomai

806
7
6

2

This is a massive step forward for EF Core - I've lost count of how many times have I wanted this over the years – Martin Hansen Lennox Apr 27 '21 at 22:00
2021 and still broken, this is also not a duplicate. The OP is asking about OrderBY, which is not the same as the DUPLICATE answer. So this is the Correct answer!! FOR OTHERS LOOKING - If you do OrderBy inside ThenInclude, EF core will use the PK first then the orderby field, so your sort will not have the expected outcome. The first OrderBy will work, then others will not work inside ThenInclude. See https://github.com/dotnet/efcore/issues/9067#issuecomment-843671773 – David B Oct 14 '21 at 09:01
@DavidB the issue case is now closed. Can you confirm the accepted answer does work now? – Superman.Lopez Mar 01 '22 at 02:12
1

@Superman.Lopez Well sort of, the underlying issue was if you use .AsSplitQuery() or have it in your startup.cs or elsewhere in the config then the OrderBy gets broken because there are multiple queries generated and things get messy under the hood. So the answer is to turn off Query Splitting if you are doing deep Ordering with Include and ThenInclude - not sure if there is fix for this since the sql generated are in multiple sql requests and not sure if there is way to track the split queries and manage ordering in the final results!! – David B May 17 '22 at 05:56

score 9 · Answer 2 · answered Feb 25 '19 at 03:33

9

The result you are trying to return is the ordered list of children. That's not what you want. Instead sort the children then return the parent:

public async Task<Parent> GetParent(int id)
{
    var parent = context.Parents
        .Include(p => p.Children)
        .SingleOrDefaultAsync(p => p.Id == id);

    parent.Result.Children = parent.Result.Children.OrderBy(c => c.Sequence).ToList();

    return await parent;
}

answered Feb 25 '19 at 03:33

see sharper

11,505
8
46
65

Thank you! Not only did it solve my issue, it also makes me understand better how to use Task<>.Result. Could I ask you for your opinion: would this be the most obvious way of sorting the nested list in my repository? or would there be a more obvious/better design? – Superman.Lopez Feb 25 '19 at 03:40
1

@Superman.Lopez - yeah it's not so elegant is it? But there's not anything stunningly better. You can map your results to an anonymous type - see this answer: https://stackoverflow.com/questions/8447384/how-to-order-child-collections-of-entities-in-ef?rq=1, which is a little neater, especially when you are returning more than one. – see sharper Feb 25 '19 at 05:20
1

@Superman.Lopez, I would suggest "await context.Parents.Include(--).SingleOrDefaultAsync(--)" instead of parent.Result. The problem with Result is that it blocks the thread until the task completes, which can cause deadlock (and also uses up threads, which are a limited resource). Using await will allow the thread to continue and run other tasks. – sjb-sjb Jul 12 '21 at 22:22

score -3 · Answer 3 · edited Sep 09 '19 at 07:43

-3

var result = loadedInMemory.Result.Children.OrderBy(c => c.Sequence).ToList();

You need to add ToList() at the end

edited Sep 09 '19 at 07:43

Günter Zöchbauer

623,577
216
2,003
1,567

answered Feb 25 '19 at 03:36

Achilles

30
1

OrderBy in Include child using EF Core

3 Answers3

Linked

Related