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.

Possible duplicate of [How to order child collections of entities in EF](http://stackoverflow.com/questions/8447384/how-to-order-child-collections-of-entities-in-ef) — Ivan Stoev, Mar 15 '16 at 21:18
@IvanStoev - I read the question that you referenced. There are two issues here. First, this shows how to do a sort at the first level. I am attempting to not only sort at the first level but at the second level as well. Second, the answer for this question *could* work but it is brute force. I am trying to figure out how to do the sorting in the LINQ query and not after the objects are retrieved. If I were writing SQL, I could easily do this. If the brute force approach is all that works then so be it. — Jason Richmeier, Mar 15 '16 at 21:33

score 3 · Answer 1 · edited May 23 '17 at 11:50

3

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

edited May 23 '17 at 11:50

Community

1
1

answered Mar 15 '16 at 20:15

ocuenca

38,548
11
89
102

From my experience, you actually don't need to issue a call to Include as you have mentioned. The LINQ query that I posted above (without the call to Include) works the same. That said, as I mentioned above, I was able to get the first-level sort (sorting of `Section` objects) to work. I need to sort at the next level (`Question` objects within a given `Section` object). – Jason Richmeier Mar 15 '16 at 20:21
But could you explain a little more how is that you need to project the collection of Questions in the result of your query? Like a new property in the anonymous type? – ocuenca Mar 15 '16 at 20:30
Using the model I listed above, I want to get a single `Form` object that has its `Sections` collection sorted. Within each `Section` object in this collection, I want its `Questions` collection sorted. What I originally posted and what you posted will accomplish the first half of this requirement but not the second half. – Jason Richmeier Mar 15 '16 at 20:34
@JasonRichmeier, please, check my query with the new changes – ocuenca Mar 15 '16 at 20:43
Sorting child collections has nothing in common with neither eagier nor lazy loading. You either project to anonymous/custom type with `OrderBy` applied and get sorted output, or use eagier/lazy loading and get unsorted (better say, undefined order) collections. – Ivan Stoev Mar 15 '16 at 21:08
Agreed, the first time that I read the question I thought the second level was not loaded because his model doesn't meet all the requirements of lazy loading, that's why I suggest to use eager loading at first, but of course to get the result that the OP needs, he can use either of the two variants to load the related entities and sort the result in the projection. – ocuenca Mar 15 '16 at 21:16
Although looks like there are some tricks with eager loading and fake projections (pointed in accepted answer of the duplicate question) :) – Ivan Stoev Mar 15 '16 at 21:21
3

Yes, the `Include` has no effect. The projection: 1. makes EF ignore it, and 2. in itself defines the child entities that are loaded. Nevertheless, this is a way to achieve what the OP wants, esp. when the projection is converted into `Form` objects (after applying `AEnumerable`). It's always a bit of a hassle to get these things done with EF. – Gert Arnold Mar 15 '16 at 21:59

score 2 · Answer 2 · answered Mar 15 '16 at 20:32

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();

You can't project a query in L2E using a entity type, that will throw an exception. — ocuenca, Mar 15 '16 at 20:38

Sorting Multiple Levels of Child Collections with LINQ to Entities

2 Answers2

Update