Sorting Multiple Levels of Child Collections with LINQ to Entities

Question

Consider the following model:

public class Form
{
    public Guid Id
    {
        get;

        set;
    }

    public List<Section> Sections
    {
        get;

        set;
    }
}

public class Section
{
    public Guid Id
    {
        get;

        set;
    }

    public List<Question> Questions
    {
        get;

        set;
    }

    public int SortOrder
    {
        get;

        set;
    }
}

public class Question
{
    public Guid Id
    {
        get;

        set;
    }

    public int SortOrder
    {
        get;

        set;
    }
}

When I retrieve one or more Form objects using LINQ to Entities, I would like to have the associated collection of Section objects sorted by the SortOrder property. Further, within each of these Section objects, I would like to have the associated collection of Question objects sorted in the same fashion.

I don't recall where I read it, but I have been able to get the first-level sort to work using a LINQ query similar to the following:

var query =
    from
        f in context.Form
    where
        f.Id == *some form id*
    select
        new
        {
            Root = f,
            Sections = f.Sections.OrderBy(s => s.SortOrder)
        };

From there, I could get the actual Form object by using something like:

var form = query.ToList().Select(q => q.Root).FirstOrDefault();

What I cannot figure out is how to write the LINQ query to extend this behavior down to the second-level collection (the collection of Question objects within each Section object.

* UPDATE *

See my comment to Ivan below which explains how this question is not a duplicate.

Answer 1

To achieve what you need you can use Eager Loading:

var query= from f in context.Form.Include(f=>f.Sections.Select(s=>s.Questions))
           where f.Id == *some form id*
           select
                  new
                     {
                        Root = f,
                        Sections = f.Sections
                                    .OrderBy(s => s.SortOrder)
                                    .Select(s=> new{s.Id, s.SortOrder, Questions=s.Questions.OrderBy(q=>q.SortOrder)})
                     };

Include extension method allows you include as part of your query related entities, even deep levels (check the Remarks section in the link I quoted above).

A second solution could be using Lazy Loading, if you haven't disabled this feature, which is enable by default, you need to meet some requirements to be used, for example, your navigation properties must be virtual.

Update

You can also sort your navigation properties in memory like the solution in the post that was quoted by @IvanStoev, but if you want to bring the related entities in some order, filtered, among other operations, you could consider use Explicit Loading:

foreach f in context.Form
{
   context.Entry(f).Collection(r => r.Sections)
          .Query().OrderBy(s=>s.SortOrder)
          .Load();
}

But IMHO the best solution is create custom classes (also called DTOs) to project the desired result, to load only the data that you need in one round trip

Answer 2

Having in mind you are directly querying the whole set of tables, then you have no need for Eager Loading with the method .Include().

Here is a lambda expression way of solving this with explictly mapping properties/columns.

// TODO: replace the null value with a real context DbSet<Form>
        IQueryable<Form> forms = null;

        var form = forms.Select(x => new Form()
        {
            Id = x.Id,
            Sections = x.Sections.OrderBy(s => s.SortOrder).Select(s => new Section()
            {
                Id = s.Id,
                SortOrder = s.SortOrder,
                Questions = s.Questions.OrderBy(q => q.SortOrder).Select(q => new Question()
                {
                    Id = q.Id,
                    SortOrder = q.SortOrder
                }).ToList()
            }).ToList()
        }).FirstOrDefault();