I am trying to make a LINQ request in which I can change the field (one) by which I want to sort. Using a predicate variable allows this

public void GetMyData(string sortFieldName) { Func<MyEntity, object> orderPredicate = null; switch (sortFieldName) { case "FIELD_STRING": orderPredicate = x => x.FIELD_STRING; case "FIELD_INT": orderPredicate = x => x.FIELD_INT; case "FIELD_DATE": orderPredicate = x => x.FIELD_DATE; default: orderPredicate = x => x.FIELD_DATE; } var queryResult = db.MyEntity.OrderBy(orderPredicate); //...some logic }

But dramatically (tenfold), the performance drops as compared with the direct assignment of the sort condition. As far as I understand, Expression should help. I change the type of variable

 Expression<Func<MyEntity, object>> orderPredicate = null;

But in rantayma gives an exception

Failed to cast type "System.DateTime" to type "System.Object". LINQ to Entities only supports the rendering of EDM primitive types or enumeration types.

How to be? After all, if I use the direct task conditions

 public void GetMyData(string sortFieldName) { var query = db.MyEntity; var orderedQuery = query.OrderBy(x => x.FIELD_DATE); switch (sortFieldName) { case "FIELD_STRING": orderedQuery = query.OrderBy(x => x.FIELD_STRING); break; case "FIELD_INT": orderedQuery = query.OrderBy(x => x.FIELD_INT); break; case "FIELD_DATE": orderedQuery = query.OrderBy(x => x.FIELD_DATE); break; } //...some logic }

, it will be difficult for me to complicate the request with additional Where , Include , etc. instructions.

  • And if you write something like this: pastebin.com/53quNm76 - Andrey NOP
  • @Andrey, well, I’m writing about this, that this way (the direct task of sorting) works quickly, and with the predicate variable it is much slower. - 4per
  • one
    Naturally it will be slower, so when using IEnumerable sorting will occur on the client - Andrey NOP
  • @Andrew, can you find any compromise solution between flexibility and performance? - 4per
  • 2
    There is no complication compared to your method. Just take orderedQuery and add any of your Where and Include : orderedQuery.Where(x => x.IntProp == 1) - Andrey NOP

1 answer 1

The problem is that when you write this:

 Expression<Func<MyEntity, object>> orderPredicate; orderPredicate = x => x.FIELD_DATE;

The compiler does this:

 var _param = Expression.Parameter(typeof(MyEntity), "x"); orderPredicate = Expression.Lambda<Func<MyEntity, object>>( Expression.Convert( Expression.Property(_param, "FIELD_DATE"), typeof(object) ), _param );

Here on this Expression.Convert EF also swears. In order to avoid conversion, the delegate type returned must match the property type, which in turn means that you cannot use the common variable orderPredicate .

Now how to build such queries. And they are built very simply - through the accumulation of IQueryable :

 IQueryable<MyEntity> q = db.MyEntity; switch (sortFieldName) { case "FIELD_STRING": q = q.OrderBy(x => x.FIELD_STRING); case "FIELD_INT": q = q.OrderBy(x => x.FIELD_INT); case "FIELD_DATE": q = q.OrderBy(x => x.FIELD_DATE); default: q = q.OrderBy(x => x.FIELD_DATE); }

There is no complication, the received request can also be supplemented with other conditions:

 q = q.Where(x => x.Foo > 42);

PS Since you have the name of the property in the sortFieldName parameter, the construction of the expression can be simplified a little more. Yes, the Queryable class does not allow us to call OrderBy with the property name unknown in advance - but no one bothers to "open" this method and work directly with IQueryable :

 IQueryable<MyEntity> q = db.MyEntity; var entityParam = Expression.Parameter(typeof(MyEntity)); var propExpr = Expression.Property(entityParam, sortFieldName) q = q.Provider.CreateQuery<MyEntity>( Expression.Call(typeof(Queryable), "OrderBy", new [] { entityParam.Type, propExpr.Type }, // Типы-параметры для метода Queryable.OrderBy<,> q.Expression, Expression.Quote(Expression.Lambda(propExpr, entityParam)) ) );

Also, instead of doing it manually, you can use the System.Linq.Dynamic library by downloading it from nuget:

 q = q.OrderBy(sortFieldName);
  • And if the typing is correct, then there will be no conversion? - Monk
  • @Monk yes, it won't - Pavel Mayorov