# Tahir Hassan's Blog

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