Efcore: OrderBy doesn't work if ThenInclude uses (or how to order nested collection)

[neooleg]

Steps to reproduce

There is no docs found about ordering nested collection (ThenInclude() should be used), but found some fresh samples which should work (based on discussion).

Hence using the same approach the following code should sort nested items, but it doesn't (no exception thrown):

c# var res = _db.Set<ApplicationUser>() .OrderBy(u => u.Blogs.OrderBy(b => b.Posts.OrderBy(p => p.PostOrder).First().PostOrder)) .Include(u => u.Blogs) .ThenInclude(b => b.Posts) .Single(u => u.Id == userId);

Further technical details

EF Core version: 1.1.2
Database Provider: Doesn't work on both Microsoft.EntityFrameworkCore.SqlServer and Npgsql.EntityFrameworkCore.PostgreSQL (this is the main one)
Operating system: Windows 7 / Mac OS X 10.12.6
IDE: VS Code / Visual Studio 2017

[maumar]

Include only determines which navigations are going to be loaded in the final projection. For your scenario you can try ordering on the client:

var res = _db.Set<ApplicationUser>()
    .Include(u => u.Blogs)
    .ThenInclude(b => b.Posts)
    .Where(u => u.Id == userId)
        .ToList()
        .Select(u => u.Blogs.OrderBy(b => b.Posts.OrderBy(p => p.PostOrder).First().PostOrder))
        .Single();

If you want to sort on the server, then you would have to construct the projection manually (i.e. Include can't be used for this) - and this currently would produce a separate query to fetch Posts for each Blog (which is potentially very slow)

[neooleg]

@maumar thanks for your response.

In other words, it means that for hierarchical domain model structure it's much better to have _separate DbSet for each layer_ (i.e. DbSet\

Is this efficient and idiomatic for EF?

[smitpatel]

Given the ResultOperator Single doing ordering on client is really bad perf. Need a better query which achieves ordering on server side. Also ordering could be translatable on server side (at least in the absence of Includes.

@neooleg - Can you share ApplicationUser, Blog & Post classes?

[smitpatel]

@neooleg - Also

the following code should sort nested items, but it doesn't

Include does not sort records in collection at present. If you want to sort records in a collection navigation property, you need to do it in memory. Given you have retrieved all the related data from server already, the sorting in memory is not huge perf hit.

[neooleg]

@neooleg - Can you share ApplicationUser, Blog & Post classes?

@smitpatel, sure. The main idea is that _domain model is hierarchical_.

public class ApplicationUser : IdentityUser<int>
{
  public ICollection<Blog> Blogs { get; private set; }
}

public class Blog
{
    public int Id { get; private set; }
    public ICollection<Post> Posts { get; private set; }
    // Blog fields ...
}

public class Post
{
    public int Id { get; private set; }
    public ICollection<Comment> Comments { get; private set; }
    // Post fields ...
}

// Comment looks similar

Hence, for hierarchical structure -- is it make sense to have such context which should allow to query and sort on each level on server? Then combine data on transport/protocol layer.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, int>
{   
    public ApplicationDbContext(DbContextOptions dbOptions)
        : base(dbOptions)
    {
    }

    public DbSet<Blog> Blogs { get; private set; }
    public DbSet<Post> Posts { get; private set; }
    public DbSet<Comment> Comments { get; private set; }
}

Thanks!

[smitpatel]

@neooleg - Thanks for info on classes. After running variety of queries with this domain,

1. Include/ThenInclude does not order the items. The navigation property may or may not have ordering (like HashSet) so at present there is no guarantee of ordering while loading. See https://github.com/aspnet/EntityFrameworkCore/issues/9067 & https://github.com/aspnet/EntityFrameworkCore/issues/7450 which talks more about ordering of entities and may help out in what you are trying to achieve.

2. The first query you posted is calling OrderBy but based on the lambda inside of it, it seems like the ordering is just for include. Since you are trying to order users by Blogs, it will be evaluated on server. In this case, assuming UserId is PK, it would be fine but using Single without OrderBy may give different result based on database. Also First is always evaluated in memory because it throws exception for empty sequence and we need to evaluate in memory to throw exception. If you want to translate it to server fully, consider using FirstOrDefault

3. I had re-opened this issue because I was expecting to see if the order by is getting translated to server. But as ordering is by blog it does not work. If blogs are ordered by blogorder then we indeed translate orderby to server.

```C#
// Query
var query = db.Set()
.OrderBy(u => u.Blogs.OrderBy(b => b.Posts.OrderBy(p => p.PostOrder).FirstOrDefault().PostOrder).FirstOrDefault().BlogOrder)
.SingleOrDefault(u => u.Id == userId);

// SQL
SELECT TOP(2) [u].[Id]
FROM [Users] AS [u]
WHERE [u].[Id] = @__userId_0
ORDER BY (
SELECT TOP(1) [b].[BlogOrder]
FROM [Blogs] AS [b]
WHERE [u].[Id] = [b].[ApplicationUserId]
ORDER BY (
SELECT TOP(1) [p].[PostOrder]
FROM [Posts] AS [p]
WHERE [b].[Id] = [p].[BlogId]
ORDER BY [p].[PostOrder]
)
)
```

[smitpatel]

closing as
Duplicate of #9067

[neooleg]

@smitpatel thanks for detailed answer!