How to order the items in a nested LINQ-provided collection

Question

I've got a (SQL Server) database table called Category. And another database table called SubCategory.

SubCategory has a foreign key relationship to Category. Because of this, thanks to LINQ, each Cateogory has a property called SubCategories and LINQ is nice enough to return all the SubCategories associated with my Category when I grab it.

If I want to sort the Categories alphabetically, I can just do:

return db.Categories.OrderBy(c => c.Name);

However, I have no idea how to order the SubCategories collection inside each Category.

My goal is to return a collection of Categories, where all of the SubCategory collections inside of them are ordered alphabetically by Name.

Answer 1

You need to use a Group By on the categories, and then order by subcategory.

Answer 2

You could do it by converting the linq objects into lists.

e.g.

var categoryList = db.Categories
    .OrderBy(c=> c.Name)
    .ToList();
categoryList.ForEach(c => c.SubCategory.ToList().Sort((x, y) => x.Name.CompareTo(y.Name)));
return categoryList;

Making the categories into a list enables you to use the Linq ForEach extension method. Converting the SubCategory property into a list gives you the inline sort method.

Answer 3

In cases like this, I prefer to use extension methods - as they do a great job of encapsulating this logic for reuse - and a good name can also help make the logic easily discoverable.

For example, in projects where I've used L2S in MVC applications, I just create a folder in my Models folder called Extensions - then I drop 'extension' methods in there per model/L2S class/etc (just to keep things organized).

Then, for this case I'd create something like so:

public static class CategoryExtensions {

public static List<SubCategory> GetAlphabetizedSubCategories(this Category category)
{
    return category.SubCategories
        .OrderBy(sc => sc.Name)
        .ToList();
}

}

And then, from within my MVC view, for example, if I'm passing in the Category as the Model, I could just do something as simple as:

<% foreach(SubCategory subCat in Model.GetAlphabetizedSubCategories()) { %>

Stuff goes here...

<% } %>

That's an MVC example - but you could apply that pretty-much anywhere as the idea is that you're 'decorating' your object with a method that explicitly does what you're after, and you're not making additional copies/etc.

Answer 4

Here is one of probably many ways of doing this:

In your dbml designer, select the association (the dotted line) between Category and Subcategory.

Open the properties pane, and expand "Child Property."

Change the name from SubCategories to _subCategories and the access from Public to Internal.

Click on the design surface and press F7, or right-click and choose "View Code."

Add this property:

public partial class Category
{
    public IOrderedEnumerable<SubCategory> SubCategories
    {
        get { return _subCategories.OrderBy(s => s.Name); }
    }
}

Now you have the same property, SubCategories, which is truly ordered.