创建LINQ到动态实体排序依据前pression [英] Create LINQ to entities OrderBy expression on the fly

问题描述

我尝试添加动态的排序依据前pression。但是，在执行下面的查询时，我得到以下异常：

I'm trying to add the orderby expression on the fly. But when the query below is executed I get the following exception:

System.NotSupportedException：无法
  创建类型的恒定值
  闭合式。只有原始类型
  （'如的Int32，字符串和GUID'）
  在这种情况下得到支持。

System.NotSupportedException: Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

但奇怪的是，我查询这些人恰恰是原始类型只。

The strange thing is, I am query exactly those primitive types only.

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 =
   (from posting in be.buskerPosting
    where posting.buskerAccount.cmsMember.nodeId == m.Id
    orderby orderByProperty
    //orderby posting.Created 
    select new PostingListItemDto { Set = posting }).Skip<PostingListItemDto>((page -   1) * pageSize).Take<PostingListItemDto>(pageSize);

希望有人能提供一些线索这光！

Hope somebody can shed some light on this!

推荐答案

您基本上无法使用查询前pressions这样，由于他们翻译的方式。然而，你的 的可扩展方法做到这一点明确：

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

棘手的位得到正确的前pression树型 - 那将进来编辑：）

The tricky bit is getting the right expression tree type - that'll come in an edit :)

编辑：编辑将会有所以各种理由拖延。基本上你可能需要调用使用反射泛型方法，如 Queryable.OrderBy 需要一个通用的防爆pression＆LT; Func键＆LT; TSource，TKEY的＆GT; ＆GT; ，虽然它看起来像你知道的源的类型在编译时，你可能不知道的密钥类型。如果你的做的知道它会永远由（比如说）一个int下订单，你可以使用：

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

编辑：好的，它看起来像我毕竟有时间。下面是调用的一个简短的例子排序依据使用反射：

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

您可以轻松地重构 CallOrderBy 成一个扩展方法（例如 OrderByProperty ）是这样的：

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

您原来的code就变成了：

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

这篇关于创建LINQ到动态实体排序依据前pression的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！