CODEWITHSUNDEEP
c#.netlinqsql-order-by
Michael J. Gray
Michael J. Gray

Reputation: 9916

Conditionally sorting elements by multiple properties in multiple tables with LINQ

I have recently needed to sort a list of pages and navigation menu entries, which are each associated with the other.

Each Navigation has a Page property. Each Page has a Navigation property. They are foreign key references in my database.

I have a list of Navigation items as well as a list of every Page item. The problem is that regardless of a Page being associated with a Navigation, it is stored in the list of Page items.

I want to produce a sorted list of Page items like so: Items with a non-null Navigation are sorted by the Page.Navigation.Index property. Items with a null Navigation are sorted by the Page.Title property and then the Page.ID property.

Below is what we currently do and it works for the most part, with a few exceptions. The problem I have with this is it does not handle duplicated titles for pages without a navigation associated to them.

List<Page> page1 = db.Navigations.OrderBy(n => n.Index).Select(n => n.Page).ToList();

List<Page> page2 = db.Pages.Where(p => !db.Navigations.Contains(p.Navigation)).ToList();

model.Pages = page1.Concat(page2).ToList();

Here's some example data and expected results

Pages Table (PageID, Title, Content)
0, "Home", "<html>This is a home page</html>"
3, "Some Page", "<html>This is some page.</html>"
2, "Some hidden page", "<html>This is some hidden page.</html>"
4, "Products", "<html>We've got products!</html>"
5, "aaaaa", "<html>This should be sorted to the top of pages with no nav</html>"

Navigations Table (PageID, Index)
0, 0
3, 2
4, 1

Output (PageID, Title, Content)
0, "Home", "<html>This is a home page</html>"
4, "Products", "<html>We've got products!</html>"
3, "Some Page", "<html>This is some page</html>"
5, "aaaaa", "<html>This should be sorted to the top of pages with no nav</html>"
2, "Some hidden page", "<html>This is some hidden page.</html"

I'm curious if this is possible to do in a nicer looking way and also in the query syntax instead of the procedural syntax.

Upvotes: 3

Views: 1323

Answers (1)

Adrian Iftode
Adrian Iftode

Reputation: 15683

I guess this would fix the issue:

model.Pages = db.Pages
  .OrderBy(p=>p.Navigation != null ? p.Navigation.Index : Int32.MaxValue)
  .ThenBy (p=>p.Title)
  .ThenBy (p=>p.PageID)
  .ToList();

Or if you like this syntax

var query = from p in db.Pages
            orderby p.Navigation != null ? p.Navigation.Index : Int32.MaxValue,
                       p.Title, 
                       p.PageID
            select p;

model.Pages = query.ToList();

The pages are ordered by Navigation.Index when this exists and the ones without a Navigation.Index would appear after these ones (they would have actually Int32.MaxValue as a Navigation.Index). Because the ones with no Navigation.Index have now a unique value (Int32.MaxValue), these ones are ordered again by Title and then by PageId.

Upvotes: 3

Related Questions