Geek asks and answers

Conditionally sort items by multiple properties in multiple tables with LINQ

I recently needed to sort the list of pages and navigation menu entries that are related to each other.

Everyone Navigationhas a property Page. Everyone Pagehas a property Navigation. These are links to foreign keys in my database.

I have a list of items Navigationas well as a list of each item Page. The problem is that no matter what Pageis associated with Navigation, it is saved in the list of items Page.

I want to create a sorted list of items Page, for example: Items with non-zero Navigationare sorted by property Page.Navigation.Index. Items with zero Navigationare sorted by property Page.Title, and then by property Page.ID.

Below is what we are doing now, and it works for the most part, with a few exceptions. The problem with this is that it does not handle duplicate headings for pages without being bound to them by navigation.

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 are some examples of 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 am curious if this can be done better, as well as in the query syntax instead of the procedural syntax.

+3
source share
1 answer

I think this will fix the problem:

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

Navigation.Index, , , Navigation.Index ( Int32.MaxValue Navigation.Index). , Navigation.Index, (Int32.MaxValue), , PageId.

+3

More articles:


All Articles