Sort Collection by First Item of Sub List in Entity Framework

Sort collection by first item of sub list in Entity Framework

The answer to your concrete question (after clarification that The authors should be ordered by name, and the books collection by previously ordered list of authors) is like this:

var query = db.Books
    .OrderBy(b => b.Authors.OrderBy(a => a.Name).Select(a => a.Name).FirstOrDefault());

which would generate a single SQL query like this:

SELECT
    [Project2].[Id] AS [Id],
    [Project2].[Title] AS [Title]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[Title] AS [Title],
        (SELECT TOP (1) [Project1].[Name] AS [Name]
            FROM ( SELECT
                [Extent2].[Name] AS [Name]
                FROM [dbo].[Author] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[BookId]
            )  AS [Project1]
            ORDER BY [Project1].[Name] ASC) AS [C1]
        FROM [dbo].[Book] AS [Extent1]
    )  AS [Project2]
    ORDER BY [Project2].[C1] ASC

Note that the books w/o author will be the first in the order.

Another way is to use Min function:

var query = db.Books
    .OrderBy(b => b.Authors.Min(a => a.Name));

with SQL translation:

SELECT
    [Project1].[Id] AS [Id],
    [Project1].[Title] AS [Title]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[Title] AS [Title],
        (SELECT
            MIN([Extent2].[Name]) AS [A1]
            FROM [dbo].[Author] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[BookId]) AS [C1]
        FROM [dbo].[Book] AS [Extent1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC

Sort Main-List by property of Sublist in LINQ

Obviously, you need some value from Materials. And OrderBy won't return it.
I would recommend you to get a particular value for sorting StoreElements based on what logic you need: Min, Max or Average for example.

var myOrderdStorageLocationsByDeliveryDateOfMaterialsSublist = this.MyDatabaseContext.StorageLocations
.Include(x=>x.Materials)
.OrderBy(x=>x.Materials.Min(y=>y.DeliveryDate))
.ToList();

How to order child collections of entities in EF

You could load the data and sort in memory after loading it.

IEnumerable<Team> teams = _ctx.Teams
            .Include(x => x.TeamMembers)
            .Include(x => x.TeamMembers.Select(u => u.User))
            .Where(x => x.UserId == ownerUserId)
            .OrderBy(x => x.Name).ToList();

foreach (var team in teams)
{
    team.TeamMembers = team.TeamMembers.OrderBy(m => m.Name);
    foreach (var teamMember in team.TeamMembers)
    {
        teamMember.Users = teamMember.Users.OrderBy(u => u.Name);
    }
}

Or you could use Projections and use the Change Tracking of EF to sort your collection. Here is an example of filtering an Include but the same works for Ordering.

Linq OrderBy Sub List

You can use Enumerable.Min() to pick out the slot with the earliest date, like so:

        var query = deliveryMethods
            .OrderBy(x => x.Slots.Min(s => s.ExpectedDeliveryDate).Year)
            .ThenBy(x => x.Slots.Min(s => s.ExpectedDeliveryDate).Month)
            .ThenBy(x => x.Slots.Min(s => s.ExpectedDeliveryDate).Date)
            .ToList();

Or, just

        var query = deliveryMethods
            .OrderBy(x => x.Slots.Min(s => s.ExpectedDeliveryDate.Date))
            .ToList();

Do be aware that Min() will throw an exception when the input sequence is empty and the type being minimized is a value type. If you want to avoid the exception, you could do this:

        var query2 = deliveryMethods
            .OrderBy(x => x.Slots.Min(s => (DateTime?)(s.ExpectedDeliveryDate.Date)))
            .ToList();

By converting the DateTime to a nullable, Min() will return a null for an empty sequence, and Method objects with empty slot list will get sorted to the beginning.

OrderBy in Include child using EF Core

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.

Entity Framework (.NET Full Framework) Ordering Includes

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

How to sort inner list that is returned by entity framework?

var sortedList = from x in entities.Xs
                 orderby x.Field
                 select new {
                   Field = x.Field,
                   y = (select y in x.Ys
                        orderby y.Field
                        select y)
                 };

Edited:
If you don't want anonymous types then do this:

var sortedList = from x in entities.Xs
                 orderby x.Field
                 select new X {
                   Field = x.Field,
                   y = (select y in x.Ys
                        orderby y.Field
                        select y)
                 };

Entity Framework loading child collection with sort order

You cannot achieve it directly because neither eager or lazy loading in EF supports ordering or filtering.

Your options are:

• Sort data in your application after you load them from database
• Execute separate query to load child records. Once you use separate query you can use OrderBy

The second option can be used with explicit loading:

var parent = context.Parents.First(...);
var entry = context.Entry(parent);
entry.Collection(e => e.Children)
     .Query()
     .OrderBy(c => c.SortOrder)
     .Load();

LINQ sort a flat list based on childorder

  public lEnumerable<TempTable> GetList( int? parentID = null){

     foreach ( var item in Context.TempTables
        .Where( x => x.ParentID == parentID )
        .OrderBy( x=> x.SortOrder)
        .ToList() {

        yield return item;

        foreach( var child in GetList( item.ID))
        {
            yield return child;
        }

     }
  }

  var sortedList = GetList();

It is similar to your method but it is smaller & recursive. And works for many depth levels. I prefer calling ToList because it will close resultset before querying next query.

There is no way to do this in single query as of now.

With Single Query as Requested

Entity Framework will automatically fill all children.

 public IEnumerable<TempTable> PrepareList(IEnumerable<TempTable> list){
     list = list.OrderBy( x=> x.SortOrder);
     foreach(var item in list){
         yield return item;
         foreach(var child in PrepareList(item.ChildTempTables)){
             yield return child;
         }
     }
 }

 // since EF will automatically fill each children on fetch
 // all we need is just a top level nodes
 // which we will pass to PrepareList method
 var list = Context.TempTables.ToList().Where(x=> x.ParentID == null);
 var sortedList = PrepareList(list).ToList();

 // it is good to create list at the end if you are going to 
 // iterate it many times and logic will not change.

---

Related Topics