Entity Framework - loading child collection, sorted by property of child

Question

I am working on an application that has several bugs related to a List collection being loaded with objects in the incorrect order. I would like to modify the DataMapper to sort the List (Statements) by their child property StatementNumber (int).

My grasp on EF is not 100%, but I found this similar question, where I think the second solution is the answer I need. I am just having a hard time implementing it. Entity Framework loading child collection with sort order

Existing code in the Data Mapper class before I modified it:

public async Task<CoLab> GetCoLabAsync(int id)
{
    return await (from d in _sdContext.CoLabs
                .Include(d => d.Project)
                .Include(d => d.CoLabType)
                .Include(d => d.Clusters)
                .Include(d => d.Clusters.Select(c => c.Statements))
                .Include(d => d.Statements)
                .Include(d => d.TriggeringQuestion)
                .Include(d => d.CoLabLocations)
                .Include(d => d.Statements.Select(s => s.Clusters))
                .Include(d => d.Statements.Select(s => s.Votes))
            where d.Id == id
            select d).SingleOrDefaultAsync();
}

My failed attempt at implementing sorting solution:

public async Task<CoLab> GetCoLabAsync(int id)
{
    return await (from d in _sdContext.CoLabs
                .Include(d => d.Project)
                .Include(d => d.CoLabType)
                .Include(d => d.Clusters)
                .Include(d => d.Clusters.Select(c => c.Statements))
                .Include(d => new
                    {
                        D = d.Statements,
                        C = d.Statements.OrderBy(c => c.StatementNumber)
                    })
                .Include(d => d.TriggeringQuestion)
                .Include(d => d.CoLabLocations)
                .Include(d => d.Statements.Select(s => s.Clusters))
                .Include(d => d.Statements.Select(s => s.Votes))
            where d.Id == id
            select d).SingleOrDefaultAsync();
}

What am I doing wrong?

if you are trying to sort, shouldn't you have an `order by` clause? — Cam Bruce, Dec 22 '16 at 20:50
Do not put the `OrderBy` in your `.Include(...)`. Put it after your `where`. — Maxime, Dec 22 '16 at 20:53

score 3 · Accepted Answer · edited Jun 14 '18 at 13:31

In the default constructor of your entities, initialize collection fields with a sorted set instead of hashset or any other collection type.

You can provide a custom comparer. https://msdn.microsoft.com/en-us/library/dd412070(v=vs.110).aspx

This way your collections are always sorted and your object model might be improved.

That being said, if you don't want to change your entities, as stated in the response you linked, make one query please per collection using Load() method.

Entity Framework - loading child collection, sorted by property of child

1 Answers1