LINQ orderby FK using left join

Question

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 

What makes left joining categories and categoryOrders

There is a category for each catgoryOrder.

This works well, except that when I want to order OrderIndex (maybe from NULL to 999), it puts all empty (i.e. null returned relationships in which the category does not have categoryOrder) at the top of the request.

How to change this to empty values ​​at the end of the list? Preferably without iteration after the query to change the null values ​​to 999.

Thanks,

Jd

Answer 1

I have not tried this (IQueryProvider may not like it)

 let orderIndex = _categoryOrders.OrderIndex ?? int.MaxValue 

Right in front of your order and order an Index order instead.

Answer 2

First, you can first sort the files with non-zero values ​​and add the latest values:

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