LINQ orderby FK using a left join

Question

Hi, I have the following code:

from _categories in context.SCT_Categories
join _categoryOrders in context.SCT_CategoryOrders
on _categories.ID equals _categoryOrders.CategoryID into joinedData
from _categoryOrders in joinedData.DefaultIfEmpty()
orderby _categoryOrders.OrderIndex descending 
select _categories

Which does a left join on categories and categoryOrders

For every catgoryOrder there is a category.

This works well, except that when I want to order by OrderIndex (Can be null to 999) it places all empty (i.e. null returned relationships where a category has no categoryOrder) at the top of the query.

How do I change this to put empty values at the end of the list? Prefereably without an iteration after the query to change empty values to 999.

Thanks,

JD

Answer 1

I haven't tried this (the IQueryProvider might not like it)

let orderIndex = _categoryOrders.OrderIndex ?? int.MaxValue

Right before your orderby, and order on orderIndex instead.

Answer 2

You could try sorting those with non-null values first, and addind the null values last:

var categories = from _categories in context.SCT_Categories
    join _categoryOrders in context.SCT_CategoryOrders
    on _categories.ID equals _categoryOrders.CategoryID into joinedData
    from _categoryOrders in joinedData.DefaultIfEmpty()
    select _categories;


var sortedCategories = categories.Where(c=>c.OrderIndex != null).OrderBy(c=>c.OrderIndex)
    .union(categories.Where(c=>c.OrderIndex == null));