C# Linq OrderBy filtering null or empty values to be last

Question

I try to make my custom orderby extension method, i successfully worked my code but in addition i want to list null or empty or zero values last in result, anyone can help me about that issue ?

Here is my extension method to orderby

    public static IQueryable<T> OrderBy<T>(this IQueryable<T> q, string SortField, bool isAsc)
    {
        //var nullExpr = Expression.Constant(null, typeof(T));
        var param = Expression.Parameter(typeof(T), "p");
        var prop = Expression.Property(param, SortField);
        var exp = Expression.Lambda(prop, param);
        string method = isAsc ? "OrderBy" : "OrderByDescending";
        Type[] types = new Type[] { q.ElementType, exp.Body.Type };
        var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
        return q.Provider.CreateQuery<T>(mce);
    }

Thanks in advance

Sanguinary

Answer 1

Without using an extension method....

Create a custom IComparer<string> to check the empty values before using the default String.Compare. The first checks will return -1 instead of 1 or 1 instead of -1, if using the standard string comparison.

/// <summary>
/// Returns -1 instead of 1 if y is IsNullOrEmpty when x is Not.
/// </summary>
public class EmptyStringsAreLast : IComparer<string>
{
    public int Compare(string x, string y)
        {
            if (String.IsNullOrEmpty(y) && !String.IsNullOrEmpty(x))
            {
                return -1;
            }
            else if (!String.IsNullOrEmpty(y) && String.IsNullOrEmpty(x))
            {
                return 1;
            }
            else
            {
                return String.Compare(x, y);
            }
        }
 }

Pass your EmptyStringsAreLast comparer into the OrderBy of Lambda expression. In this solution teams who have entered the race should appear alphabetical order, but the unaffiliated race entries should appear at then end.

var entries = repository.Race.Where(e => e.EventId == id)
                          .OrderBy(e => e.TeamName, new EmptyStringsAreLast())
                          .ThenBy(e => e.LastName)
                          .ThenBy(e => e.FirstName);

Knowing

Answer 2

The simplest way is to use

OrderBy(e => String.IsNullOrEmpty(e.TeamName)

This doesn't require any extension method or custom IComparer implementation etc.

var entries = repository.Race.Where(e => e.EventId == id)
                      .OrderBy(e => String.IsNullOrEmpty(e.TeamName))
                      .ThenBy(e => e.LastName)
                      .ThenBy(e => e.FirstName);

Clericals

But for this you have to add .ThenBy(e => e.TeamName), too. .OrderBy(e => String.IsNullOrEmpty(e.TeamName)) will only sort by the boolean result and not by the content itself. – Amp

Not sure why this is not the accepted answer! Looks perfect! Thanks – Blain

Answer 3

This answer is perhaps what you were originally looking for - using your generic extension method:

    public static IQueryable<T> OrderByFieldNullsLast<T>(this IQueryable<T> q, string SortField, bool Ascending)
    {
        //We are rebuilding .OrderByDescending(p => p.SortField.HasValue).ThenBy(p => p.SortField)
        //i.e. sort first by whether sortfield has a value, then by sortfield asc or sortfield desc

        //create the expression tree that represents the generic parameter to the predicate
        var param = Expression.Parameter(typeof(T), "p");

        //create an expression tree that represents the expression p=>p.SortField.HasValue 
        var prop = Expression.Property(param, SortField);
        var hasValue = Expression.Property(prop, "HasValue");
        var exp = Expression.Lambda(hasValue, param);

        string method = "OrderByDescending";
        Type[] types = new Type[] { q.ElementType, exp.Body.Type };
        var orderByCallExpression = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);

        //now do the ThenBy bit,sending in the above expression to the Expression.Call
        exp = Expression.Lambda(prop, param);
        types = new Type[] { q.ElementType, exp.Body.Type };
        method = Ascending ? "ThenBy" : "ThenByDescending";
        var ThenByCallExpression = Expression.Call(typeof(Queryable), method, types,orderByCallExpression, exp);


        return q.Provider.CreateQuery<T>(ThenByCallExpression);
    }

Tensible

Answer 4

Building on Dave Anson's answer, you can user Comparer.Create() to create the Comparer from a lambda. Here's an example that sorts unsorted by its myString string fields, with null or empty strings appearing last.

var sorted = unsorted.OrderBy(x => x.myString, Comparer<string>.Create((x, y) => { 
             if ( string.IsNullOrEmpty(y) && !string.IsNullOrEmpty(x)) return -1;
        else if (!string.IsNullOrEmpty(y) &&  string.IsNullOrEmpty(x)) return +1;
        else return string.Compare(x, y);
    }))

(To put them first, switch the signs on the 1 constants)

Fancier

Answer 5

it works for me:

    private static IQueryable<T> GetOrderQuery<T>(this IQueryable<T> q, BaseFilterCollection filter)
    {
        q = q.OrderBy(GetExpression<T>(filter.SortField));

        var param = Expression.Parameter(typeof(T), "p");
        var prop = Expression.Property(param, filter.SortField);
        var exp = Expression.Lambda(prop, param);
        string method = filter.SortDirection == SortDirectionType.Asc ? "ThenBy" : "ThenByDescending";
        Type[] types = { q.ElementType, exp.Body.Type };
        var rs = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
        return q.Provider.CreateQuery<T>(rs);
    }

    private static Expression<Func<T, bool>> GetExpression<T>(string sortField)
    {
        ParameterExpression param = Expression.Parameter(typeof(T), "p");
        Expression prop = Expression.Property(param, sortField);

        var info = typeof(T).GetProperty(sortField, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
        Expression exp = Expression.Equal(prop, info.PropertyType.IsValueType 
            ? Expression.Constant(Activator.CreateInstance(info.PropertyType)) 
            : Expression.Constant(null));

        return Expression.Lambda<Func<T, bool>>(exp, param);
    }

Phrasing

Answer 6

You dont need to complicate, the easiest way is to do something like this:

YourList.OrderByDescending(x => string.IsNullOrEmpty(x.value)

Use OrderByDescending or OrderBy depending on if you want to see empty strings in the beginning or last.

Regards

Fenestration