Generate EF orderby expression by string
Using reflection and expression-trees you can provide the parameters and then call OrderBy
function, Instead of returning Expression<Func<Task, T>>
and then calling OrderBy
.
Note that OrderBy
is an extension method and has implemented in both System.Linq.Enumarable
and System.Linq.Queryable
classes. The first one is for linq-to-objects and the latter is for linq-to-entities. entity-framework needs the expression tree of the query in order to translate it to SQL commands. So we use the Queryable
implementation.
It can be done by an extension method(explanations added as comments):
public static IOrderedQueryable<TSource> OrderBy<TSource>(
this IQueryable<TSource> query, string propertyName)
{
var entityType = typeof(TSource);
//Create x=>x.PropName
var propertyInfo = entityType.GetProperty(propertyName);
ParameterExpression arg = Expression.Parameter(entityType, "x");
MemberExpression property = Expression.Property(arg, propertyName);
var selector = Expression.Lambda(property, new ParameterExpression[] { arg });
//Get System.Linq.Queryable.OrderBy() method.
var enumarableType = typeof(System.Linq.Queryable);
var method = enumarableType.GetMethods()
.Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
.Where(m =>
{
var parameters = m.GetParameters().ToList();
//Put more restriction here to ensure selecting the right overload
return parameters.Count == 2;//overload that has 2 parameters
}).Single();
//The linq's OrderBy<TSource, TKey> has two generic types, which provided here
MethodInfo genericMethod = method
.MakeGenericMethod(entityType, propertyInfo.PropertyType);
/*Call query.OrderBy(selector), with query and selector: x=> x.PropName
Note that we pass the selector as Expression to the method and we don't compile it.
By doing so EF can extract "order by" columns and generate SQL for it.*/
var newQuery = (IOrderedQueryable<TSource>)genericMethod
.Invoke(genericMethod, new object[] { query, selector });
return newQuery;
}
Now you can call this overload of OrderBy
like any other overload of it.
For example:
var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();
Which translates to:
SELECT TOP (10) {coulmn names} FROM [dbo].[Items] AS [Extent1]
ORDER BY [Extent1].[Money] ASC
This approach can be used to define all overloads of OrderBy
and OrderByDescending
methods to have string
property selector.
How to use a string to create a EF order by expression?
Creating an expression is not hard, but the tricky part is how to bind it to the corresponding OrderBy(Descending)
/ ThenBy(Descendig)
methods when you don't know the type of the property (hence the type of the selector expression result).
Here is all that encapsulated in a custom extension method:
public static partial class QueryableExtensions
{
public static IOrderedQueryable<T> OrderByMember<T>(this IQueryable<T> source, string memberPath)
{
return source.OrderByMemberUsing(memberPath, "OrderBy");
}
public static IOrderedQueryable<T> OrderByMemberDescending<T>(this IQueryable<T> source, string memberPath)
{
return source.OrderByMemberUsing(memberPath, "OrderByDescending");
}
public static IOrderedQueryable<T> ThenByMember<T>(this IOrderedQueryable<T> source, string memberPath)
{
return source.OrderByMemberUsing(memberPath, "ThenBy");
}
public static IOrderedQueryable<T> ThenByMemberDescending<T>(this IOrderedQueryable<T> source, string memberPath)
{
return source.OrderByMemberUsing(memberPath, "ThenByDescending");
}
private static IOrderedQueryable<T> OrderByMemberUsing<T>(this IQueryable<T> source, string memberPath, string method)
{
var parameter = Expression.Parameter(typeof(T), "item");
var member = memberPath.Split('.')
.Aggregate((Expression)parameter, Expression.PropertyOrField);
var keySelector = Expression.Lambda(member, parameter);
var methodCall = Expression.Call(
typeof(Queryable), method, new[] { parameter.Type, member.Type },
source.Expression, Expression.Quote(keySelector));
return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
}
Call OrderBy() with a field name as a string
Thanks to all. Rob, your solution was pretty close to what I ended up with.
Based on your insights I did some more searching and came across Marc Gravel's answer here Dynamic LINQ OrderBy on IEnumerable<T> (second post).
It added dynamic's as an additional bonus.
How to construct Order By Expression dynamically in Entity Framework?
I found a solution with the help of Jon Skeet's old answer.
public static class QueryHelper
{
private static readonly MethodInfo OrderByMethod =
typeof (Queryable).GetMethods().Single(method =>
method.Name == "OrderBy" && method.GetParameters().Length == 2);
private static readonly MethodInfo OrderByDescendingMethod =
typeof (Queryable).GetMethods().Single(method =>
method.Name == "OrderByDescending" && method.GetParameters().Length == 2);
public static bool PropertyExists<T>(this IQueryable<T> source, string propertyName)
{
return typeof(T).GetProperty(propertyName, BindingFlags.IgnoreCase |
BindingFlags.Public | BindingFlags.Instance) != null;
}
public static IQueryable<T> OrderByProperty<T>(
this IQueryable<T> source, string propertyName)
{
if (typeof (T).GetProperty(propertyName, BindingFlags.IgnoreCase |
BindingFlags.Public | BindingFlags.Instance) == null)
{
return null;
}
ParameterExpression paramterExpression = Expression.Parameter(typeof (T));
Expression orderByProperty = Expression.Property(paramterExpression, propertyName);
LambdaExpression lambda = Expression.Lambda(orderByProperty, paramterExpression);
MethodInfo genericMethod =
OrderByMethod.MakeGenericMethod(typeof (T), orderByProperty.Type);
object ret = genericMethod.Invoke(null, new object[] {source, lambda});
return (IQueryable<T>) ret;
}
public static IQueryable<T> OrderByPropertyDescending<T>(
this IQueryable<T> source, string propertyName)
{
if (typeof (T).GetProperty(propertyName, BindingFlags.IgnoreCase |
BindingFlags.Public | BindingFlags.Instance) == null)
{
return null;
}
ParameterExpression paramterExpression = Expression.Parameter(typeof (T));
Expression orderByProperty = Expression.Property(paramterExpression, propertyName);
LambdaExpression lambda = Expression.Lambda(orderByProperty, paramterExpression);
MethodInfo genericMethod =
OrderByDescendingMethod.MakeGenericMethod(typeof (T), orderByProperty.Type);
object ret = genericMethod.Invoke(null, new object[] {source, lambda});
return (IQueryable<T>) ret;
}
}
Usage
string orderBy = "Name";
if (query.PropertyExists(orderBy))
{
query = query.OrderByProperty(orderBy);
- OR -
query = query.OrderByPropertyDescending(orderBy);
}
Create LINQ to entities OrderBy expression on the fly
You basically can't use query expressions like this, due to the way they're translated. However, you can do it explicitly with extension methods:
string sortBy = HttpContext.Current.Request.QueryString["sidx"];
ParameterExpression prm = Expression.Parameter(typeof(buskerPosting), "posting");
Expression orderByProperty = Expression.Property(prm, sortBy);
// get the paged records
IQueryable<PostingListItemDto> query = be.buskerPosting
.Where(posting => posting.buskerAccount.cmsMember.nodeId == m.Id)
.OrderBy(orderByExpression)
.Select(posting => new PostingListItemDto { Set = posting })
.Skip<PostingListItemDto>((page - 1) * pageSize)
.Take<PostingListItemDto>(pageSize);
The tricky bit is getting the right expression tree type - that'll come in an edit :)
EDIT: The edit will be somewhat delayed for various reasons. Basically you may need to call a generic method using reflection, as Queryable.OrderBy
needs a generic Expression<Func<TSource, TKey>>
and although it looks like you know the source type at compile-time, you may not know the key type. If you do know it'll always be ordering by (say) an int, you can use:
Expression orderByProperty = Expression.Property(prm, sortBy);
var orderByExpression = Expression.Lambda<Func<buskerPosting, int>>
(orderByProperty, new[] { prm });
EDIT: Okay, it looks like I had time after all. Here's a short example of calling OrderBy
using reflection:
using System;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
public class Test
{
static void Main()
{
string[] names = { "Jon", "Holly", "Tom", "Robin", "Will" };
var query = names.AsQueryable();
query = CallOrderBy(query, "Length");
foreach (var name in query)
{
Console.WriteLine(name);
}
}
private static readonly MethodInfo OrderByMethod =
typeof(Queryable).GetMethods()
.Where(method => method.Name == "OrderBy")
.Where(method => method.GetParameters().Length == 2)
.Single();
public static IQueryable<TSource> CallOrderBy<TSource>
(IQueryable<TSource> source, string propertyName)
{
ParameterExpression parameter = Expression.Parameter(typeof(TSource), "posting");
Expression orderByProperty = Expression.Property(parameter, propertyName);
LambdaExpression lambda = Expression.Lambda(orderByProperty, new[] { parameter });
Console.WriteLine(lambda);
MethodInfo genericMethod = OrderByMethod.MakeGenericMethod
(new[] { typeof(TSource), orderByProperty.Type });
object ret = genericMethod.Invoke(null, new object[] {source, lambda});
return (IQueryable<TSource>) ret;
}
}
You could easily refactor CallOrderBy
into an extension method (e.g. OrderByProperty
) like this:
public static class ReflectionQueryable
{
private static readonly MethodInfo OrderByMethod =
typeof(Queryable).GetMethods()
.Where(method => method.Name == "OrderBy")
.Where(method => method.GetParameters().Length == 2)
.Single();
public static IQueryable<TSource> OrderByProperty<TSource>
(this IQueryable<TSource> source, string propertyName)
{
ParameterExpression parameter = Expression.Parameter(typeof(TSource), "posting");
Expression orderByProperty = Expression.Property(parameter, propertyName);
LambdaExpression lambda = Expression.Lambda(orderByProperty, new[] { parameter });
Console.WriteLine(lambda);
MethodInfo genericMethod = OrderByMethod.MakeGenericMethod
(new[] { typeof(TSource), orderByProperty.Type });
object ret = genericMethod.Invoke(null, new object[] {source, lambda});
return (IQueryable<TSource>) ret;
}
}
Your original code then becomes:
string sortBy = HttpContext.Current.Request.QueryString["sidx"];
// get the paged records
IQueryable<PostingListItemDto> query = be.buskerPosting
.Where(posting => posting.buskerAccount.cmsMember.nodeId == m.Id)
.OrderByProperty(sortBy)
.Select(posting => new PostingListItemDto { Set = posting })
.Skip<PostingListItemDto>((page - 1) * pageSize)
.Take<PostingListItemDto>(pageSize);
(Apologies for the formatting involving horizontal scrollbars... I'll reformat later if anyone cares. Or you could do it for me if you have enough rep ;)
how to order by a dynamic column name in EntityFramework?
Try this
string filterString = "dated";
bool isAscSorting = false;
Func<dynamic, dynamic> orderingFunction = i =>
filterString == "dated" ? i.dated :
filterString == "something" ? i.columnx : "";
records.Content = (isAscSorting) ?
db.areas
.Where(x => x.Name.Contains(filter)))
.OrderBy(orderingFunction)
.ToList()
:
db.areas
.Where(x => x.Name.Contains(filter)))
.OrderByDescending(orderingFunction)
.ToList();
How to sorting dynamic in lambda entity framework?
How about use Dynamic Linq ?
It can generate query from string.
using (var db = new ABCEntities()){
var columnExp = "columnName";
var query = db.LOCATIONs;
if(sort_order = "ASC")
{
query = query.OrderBy(columnExp).Tolist();
}
else
{
query = query.OrderByDescending(columnExp).Tolist();
}
}
.NET Core 3 InvalidOperationException on OrderBy with dynamic field name
Your problem is that you are using reflection inside of order by, while probably you should use sorting string.
One of the options
Install-Package System.Linq.Dynamic
using System.Linq.Dynamic;
then you can sort
query.OrderBy("item.item_id DESC")
Other option without any library in case you dont have many sort options would be:
switch(sOrdenar){
case "Field1"
clientes = nSentido == -1 ? clientes.OrderBy(entity=> entity.Field1) : clientes.OrderByDescending(entity=> entity.Field1);
break;
case "OtherField"
clientes = nSentido == -1 ? clientes.OrderBy(entity=> entity.OtherField) : clientes.OrderByDescending(entity=> entity.OtherField);
break;
}
Personally I prefer second option better, because then I can be sure that user is able to sort only on allowed fields otherwise you can have performance issues in case you have large tables and users start sorting on wrong fields (Never trust your users :) ).
OrderBy Expression Tree in Net Core Linq for Extension Method
Check the sample code for the solution:
void Main()
{
var queryableRecords = Product.FetchQueryableProducts();
Expression expression = queryableRecords.OrderBy("Name");
var func = Expression.Lambda<Func<IQueryable<Product>>>(expression)
.Compile();
func().Dump();
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public static IQueryable<Product> FetchQueryableProducts()
{
List<Product> productList = new List<Product>()
{
new Product {Id=1, Name = "A"},
new Product {Id=1, Name = "B"},
new Product {Id=1, Name = "A"},
new Product {Id=2, Name = "C"},
new Product {Id=2, Name = "B"},
new Product {Id=2, Name = "C"},
};
return productList.AsQueryable();
}
}
public static class ExpressionTreesExtesion
{
public static Expression OrderBy(this IQueryable queryable, string propertyName)
{
var propInfo = queryable.ElementType.GetProperty(propertyName);
var collectionType = queryable.ElementType;
var parameterExpression = Expression.Parameter(collectionType, "g");
var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
var orderLambda = Expression.Lambda(propertyAccess, parameterExpression);
return Expression.Call(typeof(Queryable),
"OrderBy",
new Type[] { collectionType, propInfo.PropertyType },
queryable.Expression,
Expression.Quote(orderLambda));
}
}
Result
How it Works:
Created an expression using extension method on the Queryable
type, which internally calls OrderBy
method of the Queryable
type, expecting IQueryable
to be the Input, along with the field name and thus runs the ordering function and Ordered collection is the final Output
Option 2:
This may fit your use case better, here instead of calling OrderBy
method, we are creating the Expression<Func<T,string>>
as an extension method to the IEnumerable<T>
, which can then be compiled and supplied to the OrderBy Call, as shown in the example and is thus much more intuitive and simple solution:
Creating Expression:
public static class ExpressionTreesExtesion
{
public static Expression<Func<T,string>> OrderByExpression<T>(this IEnumerable<T> enumerable, string propertyName)
{
var propInfo = typeof(T).GetProperty(propertyName);
var collectionType = typeof(T);
var parameterExpression = Expression.Parameter(collectionType, "x");
var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
var orderExpression = Expression.Lambda<Func<T,string>>(propertyAccess, parameterExpression);
return orderExpression;
}
}
How to Call:
var ProductExpression = records.OrderByExpression("Name");
var result = records.OrderBy(ProductExpression.Compile());
ProductExpression.Compile()
above will compile into x => x.Name
, where column name is supplied at the run-time
Please note in case the ordering field can be other types beside string data type, then make that also generic and supply it while calling extension method, only condition being property being called shall have the same type as supplied value, else it will be a run-time exception, while creating Expression
Edit 1, how to make the OrderType field also generic
public static Expression<Func<T, TField>> OrderByFunc<T,TField>(this IEnumerable<T> enumerable, string propertyName)
{
var propInfo = typeof(T).GetProperty(propertyName);
var collectionType = typeof(T);
var parameterExpression = Expression.Parameter(collectionType, "x");
var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
var orderExpression = Expression.Lambda<Func<T, TField>>(propertyAccess, parameterExpression);
return orderExpression;
}
How to call:
Now both the types need to be explicitly supplied, earlier were using generic type inference from IEnumerable<T>
:
// For Integer Id field
var ProductExpression = records.OrderByFunc<Product,int>("Id");
// For string name field
var ProductExpression = records.OrderByFunc<Product,string>("Name");
IQueryable.OrderBy is not working with IComparer in EF Core
That is already explained to this case here Is it possible to use IComparable to compare entities in Entity Framework?
The short Answer is No, you cant do that.
EF requires all operations to be able to execute 100% server-side. Supporting IComparable or IEquatable would require EF to be able to translate arbitrary IL into SQL, which it can't yet do.
Related Topics
Relative Path to Absolute Path in C#
Wcf Httptransport: Streamed VS Buffered Transfermode
How to Get All Cookies of a Cookiecontainer
Entity Framework Ef.Functions.Like VS String.Contains
Why Is Foreach Loop Read-Only in C#
A Reproducible Example of Volatile Usage
How to Programmatically Convert Vb6 Formatting Strings to .Net Formatting Strings
Ssis Task for Inconsistent Column Count Import
Using Custom Colored Cursors in a C# Windows Application
Return Only Digits 0-9 from a String
Fastest Way to Add New Node to End of an Xml
Should I Transform Entity (Persistent) Objects to Dto Objects
JSON.Net: Specify Converter for Dictionary Keys
How to Check If Ioexception Is Not-Enough-Disk-Space-Exception Type