Create Order by programming Linq/Lambda

Question

I have this code :

public void CreateOrdering(string field, string direction)
{
    //direction : ASC/DESC
    var result = context.MyTable
        .Where(x => x.Code > 5)
        .OrderBy()
        .Skip(10)
        .Take(5)
        .ToList<MyTable>();
}

I rephrase, I have a method, this method receive as string field name for ordering and the direction ("ASC", "DESC")

I'd like create a Order with the field and the direction received in argument. I have to be able to :

1. I'd like in this Query be able to do an ascending and descending
2. Set the ordering field by programming, here Code may be later Id or other ...
3. The ordering must be done on the SQL Server side not on the list returned

Thanks,

Answer 1

You may use reflection in an extension method which allows for linq syntax:

public static IQueryable<TSource> OrderBy<TSource>(this IQueryable<TSource> source, string field, string direction)
{
    string orderByMethod = (direction == "ASC") ? "OrderBy" : (direction == "DESC" ? "OrderByDescending" : null);
    if(orderByMethod == null) throw new ArgumentException();

    var propertyInfo = typeof (TSource).GetProperty(field);
    var entityParam = Expression.Parameter(typeof(TSource), "e");
    Expression columnExpr = Expression.Property(entityParam, propertyInfo);
    LambdaExpression columnLambda = Expression.Lambda(columnExpr, entityParam);

    MethodInfo orderByGeneric =  typeof (Queryable).GetMethods().Single(m => m.Name == orderByMethod
                                                    && m.GetParameters().Count() == 2
                                                    && m.GetParameters()[0].ParameterType.GetGenericTypeDefinition() == typeof(IQueryable<>)
                                                    && m.GetParameters()[1].ParameterType.GetGenericTypeDefinition() == typeof(Expression<>));

    MethodInfo orderBy = orderByGeneric.MakeGenericMethod(new [] {typeof(TSource), propertyInfo.PropertyType});

    return (IQueryable<TSource>) orderBy.Invoke(null, new object[] { source, columnLambda });
}

Sample use:

internal class SomeType
{
    public string StringValue { get; set; }
}

IQueryable<SomeType> l = new List<SomeType>
    {
        new SomeType {StringValue = "bbbbb"},
        new SomeType {StringValue = "cccc"},
        new SomeType {StringValue = "aaaa"},
        new SomeType {StringValue = "eeee"},
    }.AsQueryable();

var asc = l.OrderBy("StringValue", "ASC");
var desc = l.OrderBy("StringValue", "DESC");

Or for your example:

context.MyTable
        .Where(x => x.Code > 5)
        .OrderBy(field, direction)
        .Skip(10)
        .Take(5)
        .ToList<MyTable>();

Answer 2

I may have misunderstood your question, but can't you just do:

Ascending

.OrderBy(x => x.Property)

Descending

.OrderByDescending(x => x.Property)

Update

What you need is Dynamic LINQ. However, what you are trying to do it could get quite complicated. As a simple workaround you could do something like:

var result = context.MyTable
                    .Where(x => x.Code > 15);

if (direction == "ASC")
{
    result = result.OrderBy(field);
}
else
{
    result = result.OrderByDescending(field);
}

result = result.Skip(10)
               .Take(5)
               .ToList<MyTable>();

Answer 3

void Main() {
    // Ascending by some other property
    CreateOrdering(item => item.SomeProperty, SortDirection.Ascending).Dump("Ascending order for SomeClass.SomeProperty");
    // Descending by some other property
    CreateOrdering(item => item.SomeProperty, SortDirection.Descending).Dump("Descending order for SomeClass.SomeProperty");
    // Ascending by the Code property
    CreateOrdering(item => item.Code, SortDirection.Ascending).Dump("Ascending order for SomeClass.Code");
    // Descending by the Code property
    CreateOrdering(item => item.Code, SortDirection.Descending).Dump("Descending order for SomeClass.Code");
}

// I reccomend not using bare strings, and instead use an enum
public enum SortDirection {
     Ascending = 0,
     Descending = 1
}
// Define other methods and classes here
public List<SomeClass> CreateOrdering<T>(Expression<Func<SomeClass, T>> field, SortDirection direction) {
    // query does not get executed yet, because we have not enumerated it.
    var query = context.MyTable
        .Where(x => x.Code > 5);

    if (direction.Equals(SortDirection.Ascending)) {
        query = query.OrderBy (field);
    } else {
        query = query.OrderByDescending (field);
    }

    // query gets executed when the call ToList is made.
    return query.Skip(10)
                .Take(5)
                .ToList();
}

public static class context {
    private static List<SomeClass> _MyTable = new List<SomeClass>() {
        new SomeClass("A", 4), new SomeClass("B", 5), new SomeClass("C", 6),
        new SomeClass("D", 7), new SomeClass("E", 8), new SomeClass("F", 9),
        new SomeClass("G", 10), new SomeClass("H", 11), new SomeClass("I", 12),
        new SomeClass("J", 13), new SomeClass("K", 14), new SomeClass("L", 15),
        new SomeClass("M", 16), new SomeClass("N", 17), new SomeClass("O", 18)
    };

    public static IQueryable<SomeClass> MyTable {
        get {
            return _MyTable.AsQueryable();
        }
    }
}

public class SomeClass {
    public SomeClass(string property, int code) {
        this.SomeProperty = property;
        this.Code = code;
    }

    public string SomeProperty { get; set; }

    public int Code { get; set; }
}

Answer 4

normally you would do this:

.OrderBy(x => x.yourField)

or

.OrderByDescending(x => x.yourField)

if you need your field to be dynamic, check this answer

Answer 5

If the field is passed as a string (for instance when using an ObjectDataSource), you can map it using a switch:

var qry = context
  .MyTable
  .Where(x => x.Code > 5);

switch(orderBy) {
  case "MyField": qry = qry.OrderBy(r => r.MyField); break;
  case "MyField DESC": qry = qry.OrderByDescending(r => r.MyField); break;
}

// By the way, ToList can infer the generic type if you don't
// want to state it explicity
var result = qry.Skip(10).Take(5).ToList();

The query is not executed before the ToList, and at least with EF it is executed on the SQL Server. I admit the switch is quite a lot of boilerplate, but it did turn out to be quite reliable and fast.