In LINQ, how can I do .OrderBy () for the data retrieved from my .Include ()?

Question

That's what I'm doing:

List<Category> categories = db.Categories.Include("SubCategories").OrderBy(c => c.Order).ToList(); 

I have a column in my category table called "Order" that simply contains an integer that gives the table some sort of sort order.

I have the same column in my "SubCategories" table ...

I want to find out the simplest solution to add sorting to a table of subcategories ... something like:

 List<Category> categories = db.Categories.Include("SubCategories").OrderBy(c => c.Order) .ThenBy(c => c.SubCategories as x => x.Order).ToList(); 

I would like to save it in this kind of LINQ format ... (method format) ...

Keep in mind, I work in MVC and should return it in the form as a model. I had problems with errors due to AnonymousTypes ...

Answer 1

You can split one request into 2 requests that just fill the context:

 IQueryable<Category> categoryQuery = db.Categories.Where(c=> /*if needed*/); List<Category> categories = categoryQuery.OrderBy(c => c.Order).ToList(); categoryQuery.SelectMany(c => c.SubCategories) .OrderBy(sub => sub.Order) .AsEnumerable().Count(); // will just iterate (and add to context) all results 

You don’t even need a line with the error "SubCategories".

Answer 2

If Category.SubCategories is a collection in itself, you cannot order it using existing extension methods (and c => c.SubCategories as x => x.Order translates into almost nothing, basically saying that SubCategories is Func<SubCategory, bool> )

If you are satisfied that the sorting is done in memory (which should not be a problem, since you still get them from the database if you don’t have thousands of things), you can implement your own IComparer<Category> , which requests the SubCategories each Category to determine whether one Category should be placed above or below another Category in the sort operation.

Your expression will be as follows:

 var categories = db.Categories.Include("SubCategories").OrderBy(x => x, new CategorySubCategoryComparer()) 

Answer 3

I'm not sure if this is supported, but here's how to do it:

 List<Category> categories = db.Categories.Include(c => c.SubCategories.OrderBy(s => s.Order)).OrderBy(c => c.Order) 

Now the Include method supports expressions like this, but I'm not sure if it also supports order.

You might be better off sorting subcategories when you use them, perhaps in your opinion.

For instance:

 @for (var cat in Model.Categories) { @cat.Name @for (var sub in cat.SubCategories.OrderBy(c => c.Order) { @sub.Name } }