linq 通过字符串[重复]生成EF orderby表达式

qojgxg4l  于 2023-05-20  发布在  其他
关注(0)|答案(6)|浏览(136)

此问题已在此处有答案

Dynamic LINQ OrderBy on IEnumerable / IQueryable(24回答)
去年关闭。
我想通过字符串参数生成表达式,一些代码如下:

private Expression<Func<Task, T>> Generate(string orderby)
{
    switch (orderby)
    {
        case "Time":  
            return t => t.Time;
        case "Money":
            return t => t.RewardMoney;
        default:
            return t => t.Id;
    }
}

然后称之为:

_context.Items.OrderBy(Generate("Money"));

但它不能编译!我把T改为object。

private Expression<Func<Task, object>> Generate(string orderby)

然后它可以编译,但它不工作。
System.NotSupportedException:无法将类型“System.Int32”强制转换为类型“System. Object”。LINQ to Entities仅支持转换EDM基元或枚举类型。

b4qexyjb

b4qexyjb1#

使用reflectionexpression-trees可以提供参数然后调用OrderBy函数,而不是返回Expression<Func<Task, T>>然后调用OrderBy
请注意,OrderBy是一个扩展方法,并且已经在System.Linq.EnumarableSystem.Linq.Queryable类中实现。第一个用于linq-to-objects,第二个用于linq-to-entitiesentity-framework需要查询的表达式树,以便将其转换为SQL命令。所以我们使用Queryable实现。
它可以通过扩展方法来完成(解释作为注解添加):

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

现在可以像调用OrderBy的其他重载一样调用它。
例如:

var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

翻译过来就是:

SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

这种方法可用于定义OrderByOrderByDescending方法的所有重载,使其具有string属性选择器。

dy1byipe

dy1byipe2#

对于那些正在寻找EF Core解决方案的人:
Microsoft.EntityFrameworkCore.EF中有一组函数可用于动态访问和查询编译。您可以使用EF.Property方法按属性名称甚至shadow属性对可查询对象进行排序。示例:

bool descending = false;
if (sortOrder.EndsWith("_desc"))
{
    sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
    descending = true;
}

if (descending)
{
    students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
}
else
{
    students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
}

来源:教程:了解高级方案-使用EF Core的ASP.NET MVC

mm5n2pyu

mm5n2pyu3#

您可以尝试将Generate方法转换为泛型方法:

private Expression<Func<Task, TResult>> Generate<TResult>(string orderby)
{
     switch (orderby)
     {
        case "Time":  
          return t => t.Time;
        case "Money":
          return t => t.RewardMoney;
        default:
         return t => t.Id;
     }
}

因此,如果调用此方法,则需要指定要排序的属性的类型:

_context.Items.OrderBy(Generate<decimal>("Money"));

现在请记住,TResult只能是原始类型或枚举类型。

p1tboqfb

p1tboqfb4#

我参考了CodePlex中旧的System.Linq.Dynamic codebase,并从实现和调用的Angular 创建了一个非常简单的版本。当然,它是IQueryable<T>上的一个扩展方法

/*
using System;
using System.Linq;
using System.Linq.Expressions;
*/

public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string orderByExpression)
{
    if (string.IsNullOrEmpty(orderByExpression))
        return query;

    string propertyName, orderByMethod;
    string[] strs = orderByExpression.Split(' ');
    propertyName = strs[0];

    if (strs.Length == 1)
        orderByMethod = "OrderBy";
    else
        orderByMethod = strs[1].Equals("DESC", StringComparison.OrdinalIgnoreCase) ? "OrderByDescending" : "OrderBy";

    ParameterExpression pe = Expression.Parameter(query.ElementType);
    MemberExpression me = Expression.Property(pe, propertyName);

    MethodCallExpression orderByCall = Expression.Call(typeof(Queryable), orderByMethod, new Type[] { query.ElementType, me.Type }, query.Expression
        , Expression.Quote(Expression.Lambda(me, pe)));

    return query.Provider.CreateQuery(orderByCall) as IQueryable<T>;
}

以下是如何使用它的示例,针对Entity Framework Core 3进行了测试:

IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName"); // ORDER BY FirstName
IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName ASC"); // ORDER BY FirstName
IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName DESC"); // ORDER BY FirstName DESC
mxg2im7a

mxg2im7a5#

使用泛型方法。因为lambda表达式只能被赋值给强类型的委托或表达式,所以我们必须使用一个相应的临时变量。然后我们可以将这个temp赋值给一个类型为object的变量。最后,我们可以通过转换为结果类型来返回结果。

public Expression<Func<Task, TResult>> Generate<TResult>(string orderby)
{
    object result;
    switch (orderby) {
        case "Time":
            Expression<Func<Task, DateTime>> temp1 = t => t.Time;
            result = temp1;
            break;
        case "Money":
            Expression<Func<Task, decimal>> temp2 = t => t.RewardMoney;
            result = temp2;
            break;
        default:
            Expression<Func<Task, int>> temp3 = t => t.Id;
            result = temp3;
            break;
    }
    return (Expression<Func<Task, TResult>>)result;
}
0ve6wy6x

0ve6wy6x6#

public static IQueryable<T> OrderByHelper<T>(this IQueryable<T> source, string propertyName, string sortDirection)
    {

        try
        {
            if (source == null)
            {
                return source;
            }
            if (propertyName == null)
            {
                return source;
            }

            propertyName = propertyName.First().ToString().ToUpper(new CultureInfo("en-US", false)) + propertyName.Substring(1);
            var type = typeof(T);
            var arg = Expression.Parameter(type, "x");

            var propertyInfo = type.GetProperty(propertyName);
            var mExpr = Expression.Property(arg, propertyInfo);
            type = propertyInfo.PropertyType;

            var delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            var lambda = Expression.Lambda(delegateType, mExpr, arg);

            var methodName = !string.IsNullOrEmpty(sortDirection) && sortDirection.ToLower(new CultureInfo("en-US", false)) == "desc" ? "OrderByDescending" : "OrderBy";
            var orderedSource = typeof(Queryable).GetMethods().Single(
                method => method.Name == methodName
                        && method.IsGenericMethodDefinition
                        && method.GetGenericArguments().Length == 2
                        && method.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(T), type)
                .Invoke(null, new object[] { source, lambda });

            return (IQueryable<T>)orderedSource;
        }
        catch (Exception)
        {

            return source;
        }
    }

相关问题