My Technical Notes

Wednesday, 2 June 2010

Linq to Sql: Order By Nulls Last

In SQL Server if you use "ORDER BY", you will get null values first. However this is not the intention of the end user who wants to see items with values in those columns.

Especially in LINQ to SQL, there is no extension method where we can easily do this. The only way is a cumbersome


  Objects.OrderBy(x => x.Property == null)
         .ThenBy(x => x.Property);

Which gets cumbersome after a point, especially when you forget to do it and then have to hunt for the code which does the ordering.

The solution is surprisingly simple. We want to be able to call it as:

  Objects.OrderByNullsLast(x => x.Property);

I have defined two extension methods which do so, one called NullsLast which makes sure that those objects with null values will appear last, and the other OrderByNullsLast which orders the collection and which puts the null values at the end:


public static IQueryable<TSource> OrderByNullsLast<TSource, TKey>(this IQueryable<TSource> query, Expression<Func<TSource, TKey>> keySelector)
{
    return query.OrderBy(keySelector).NullsLast(keySelector);
}

public static IQueryable<TSource> NullsLast<TSource, TKey>(this IQueryable<TSource> query, Expression<Func<TSource, TKey>> keySelector)
{
    var nullExpr = Expression.Constant(null, typeof(TKey));
    var equalExpr = Expression.Equal(keySelector.Body, nullExpr);
    var lambda = Expression.Lambda<Func<TSource, bool>>(equalExpr, keySelector.Parameters);
    return query.OrderBy(lambda);
}

2 comments:

Steve said...

I suspect that this should be returning an object of type IOrderedQueryable rather than IQueryable.

Steve said...

Also, I think you need to reorder the calls to NullsLast and OrderBy/ThenBy:

This should work better and more as expected:

query.NullsLast(keySelector).ThenBy(keySelector);