通过生成字符串EF排序依据防爆pression [英] generate EF orderby Expression by string

问题描述

我想通过生成字符串参数前pression，一些code，如：

 私人防爆pression＆LT;＆Func键LT;任务，T＆GT;＆GT;生成（字符串的OrderBy）
    {
        开关（排序依据）
        {
            案时间：
                返回T =＆GT; t.Time;
            案金钱：
                返回T =＆GT; t.RewardMoney;
            默认：
                返回T =＆GT; t.Id;
        }
    }
 

然后调用它：

  _context.Items.OrderBy（生成（货币））;
 

但它不能编译！我变化T对象。

 私人防爆pression＆LT;＆Func键LT，任务，对象＆gt;＆GT;生成（字符串的OrderBy）
 

然后就可以编译，但它不能正常工作。

  

System.NotSupportedException：无法转换类型'System.Int32'输入'System.Object的。 LINQ到实体仅支持铸造EDM原始或枚举类型。

解决方案

使用的反射和的前pression树您可以提供的参数，然后调用排序依据的功能，而不是返回的防爆pression＆LT; Func键＆LT;任务，T＆GT;＆GT; ，然后调用排序依据

注意排序依据是一个扩展方法，并在这两个 System.Linq.Enumarable 和<$ C已实施$ C> System.Linq.Queryable 类。第一种是 linq-为对象，后者是的 LINQ到实体。 实体框架需要除权$为了将其翻译为SQL命令查询的对$ pssion树。因此，我们使用可查询实施

它可以通过一个扩展方法（解释加入评论）完成

 公共静态IOrderedQueryable＆LT; TSource＆GT;排序依据＆LT; TSource＆GT;（
       这IEnumerable的＆LT; TSource＆GT;查询字符串propertyName的）
{
    VAR的EntityType = typeof运算（TSource）;    //创建X =＆GT; x.PropName
    VAR的PropertyInfo = entityType.GetProperty（propertyName的）;
    ParameterEx pression精氨酸=实施例pression.Parameter（的EntityType，×）;
    MemberEx pression财产=前pression.Property（阿根廷，propertyName的）;
    VAR选择=前pression.Lambda（物业，新ParameterEx pression [] {ARG}）;    //获取System.Linq.Queryable.OrderBy（）方法。
    VAR enumarableType = typeof运算（System.Linq.Queryable）;
    VaR方法= enumarableType.GetMethods（）
         。凡（M = GT; m.Name ==排序依据＆放大器;＆安培; m.IsGenericMethodDefinition）
         。凡（M =＆GT;
         {
            VAR参数= m.GetParameters（）了ToList（）。
            //把更多的限制，在这里，以确保选择正确的过载
            返回parameters.Count == 2; //有两个参数超载
         }）。单（）;
    //该LINQ的排序依据＆LT; TSource，TKEY的＆GT;有两个泛型类型，在这里提供
    MethodInfo的genericMethod =方法
         .MakeGenericMethod（的EntityType，propertyInfo.PropertyType）;    / *调用query.OrderBy（选择），具有查询和选择：X =＆GT; x.PropName
      请注意，我们通过选择为Ex pression的方法，我们不对其进行编译。
      这样做可以EF的命令列提取并为其生成SQL * /
    VAR newQuery =（IOrderedQueryable＆LT; TSource＆GT;）genericMethod
         .Invoke（genericMethod，新的对象[] {查询，选择}）;
    返回newQuery;
}
 

现在可以调用此重载排序依据的像任何其他超载。结果
例如：

  VAR cheapestItems = _context.Items.OrderBy（钱）采取（10）.ToList（）;
 

翻译过来就是：

  SELECT TOP（10）{coulmn名} FROM [DBO]。[项目] AS [Extent1]
       ORDER BY [Extent1] [钱] ASC
 

这种方法可以用来定义排序依据所有重载和 OrderByDescending 方法有字符串属性选择器。

I want to generate expression by string parameter,some code like:

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

then call it:

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

But It can't compile! I change T to object.

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

Then It can compile, but It doesn't work.

System.NotSupportedException: Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

解决方案

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 IEnumerable<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.

这篇关于通过生成字符串EF排序依据防爆pression的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！