如何将多个表达式传递给EF的OrderBy? [英] How to pass multiple Expressions to OrderBy for EF?
问题描述
我想传递一个 IQueryable< T>
和多个表达式< Func< TSource,TKey>
ThenBy 到 IQueryable< T>
。 b
$ b
我发现这个答案,并根据以下方法写下了这个方法:
public IQueryable< User> ; ApplyOrderBy(IQueryable< User> query,IEnumerable< Expression< Func< User,IComparable>>> orderBy)
{
if(orderBy == null)
{
return查询;
}
IOrderedQueryable< User> output = null;
foreach(orderBy中的var表达式)
{
if(output == null)
{
output = query.OrderBy(expression);
}
else
{
output = output.ThenBy(expression);
}
}
返回输出?查询;
}
只要我订购的属性是 string
s,但是当我尝试通过 int
属性排序时,我收到一个例外:
无法将类型System.Int32转换为键入System.IComparable。 LINQ to Entities只支持转换Entity Data Model原始类型。
有任何建议可以解决这个问题吗?我考虑传递一个 IEnumerable< Expression>
,但是需要弄清楚如何回传到特定类型(例如 Expression&FunC用户,int>
)调用 OrderBy
。
我无法解释为什么使用 Int32
不起作用,但使用字符串
。不是都是EDM原始类型,并且都不实现 IComparable
?我不明白不同的行为。
无论如何,似乎有必要通过具体类型传递集合中的每个表达式,以避免它们被排序失败的类型转换。换句话说,不是一个 IComparable
,而是一个 int
,一个字符串
, DateTime
等。
我已经成功地实现了这一点,这个答案:如何检查ObjectQuery中的OrderBy的存在< T>表达式树
定义不 的接口取决于要排序的类型,但仅依赖于实体类型。 (下面的示例被推广到任意实体,如果只需要用户
删除通用参数并替换 TEntity
在用户
的可查询内容中。)
public interface IOrderByExpression< TEntity>其中TEntity:class
{
IOrderedQueryable< TEntity> ApplyOrderBy(IQueryable< TEntity>查询);
IOrderedQueryable< TEntity> ApplyThenBy(IOrderedQueryable< TEntity>查询);
}
定义该接口的实现,该接口现在将类型排序为第二个通用参数:
public class OrderByExpression< TEntity,TOrderBy> :IOrderByExpression< TEntity>
其中TEntity:class
{
private Expression< Func< TEntity,TOrderBy>> _表达;
private bool _descending;
public OrderByExpression(Expression< Func< TEntity,TOrderBy>>表达式,
bool descending = false)
{
_expression = expression;
_descending = descending;
}
public IOrderedQueryable< TEntity> ApplyOrderBy(
IQueryable< TEntity>查询)
{
if(_descending)
return query.OrderByDescending(_expression);
else
return query.OrderBy(_expression);
}
public IOrderedQueryable< TEntity> ApplyThenBy(
IOrderedQueryable< TEntity>查询)
{
如果(_descending)
返回query.ThenByDescending(_expression);
else
return query.ThenBy(_expression);
}
}
然后 ApplyOrderBy
将如下所示:
public IQueryable< TEntity> ApplyOrderBy< TEntity>(IQueryable< TEntity>查询,
params IOrderByExpression< TEntity> [] orderByExpressions)
其中TEntity:class
{
if(orderByExpressions == null)
返回查询;
IOrderedQueryable< TEntity> output = null;
foreach(order orderByExpression in orderByExpressions)
{
if(output == null)
output = orderByExpression.ApplyOrderBy(query);
else
output = orderByExpression.ApplyThenBy(output);
}
返回输出?查询;
}
可以使用以下方法:
var query = context.Users ...;
var queryWithOrderBy = ApplyOrderBy(query,
new OrderByExpression< User,string>(u => u.UserName),//一个字符串,asc
new OrderByExpression< User ,int>(u => u.UserId,true)); //一个int,desc
var result = queryWithOrderBy.ToList(); //没有为我抛出一个异常
需要明确指定泛型类型参数 OrderByExpression
实例不是很好,但我找不到一种方法,以便编译器推断类型。 (我希望这样做,因为编译器从查询 c
ApplyOrderBy
方法,那么我预计它知道 TEntity
OrderByExpression
(等于 User
)所以lambda参数 u
应该是称为用户
,然后编译器可以从 UserName
中导出类型为 string
和
UserId
as int
但是这个理论显然是错误的,编译器抱怨并希望有明确的通用类型。)
I am using EF 4.2, but I expect this would apply to EF 4 and 4.1 as well.
I would like to pass an IQueryable<T>
and multiple Expression<Func<TSource, TKey>>
to a method and have the method apply OrderBy
and ThenBy
to the IQueryable<T>
as appropriate.
I found this answer, and wrote the method below based on that:
public IQueryable<User> ApplyOrderBy(IQueryable<User> query, IEnumerable<Expression<Func<User, IComparable>>> orderBy)
{
if (orderBy == null)
{
return query;
}
IOrderedQueryable<User> output = null;
foreach(var expression in orderBy)
{
if (output == null)
{
output = query.OrderBy(expression);
}
else
{
output = output.ThenBy(expression);
}
}
return output ?? query;
}
This works fine as long as the properties I order by are string
s, but when I try to order by an int
property, I get an exception:
Unable to cast the type 'System.Int32' to type 'System.IComparable'. LINQ to Entities only supports casting Entity Data Model primitive types.
Any suggestions to work around this, or for a different approach altogether? I considered passing in an IEnumerable<Expression>
, but then would need to figure out how to cast back to the specific type (e.g. Expression<Func<User, int>
) to call OrderBy
.
I cannot explain why using an Int32
does not work but using a string
. Aren't both EDM "primitive" types and do not both implement IComparable
? I don't understand the different behaviour.
Anyway, it seems to be necessary to pass in every expression in the collection with the concrete type it should be sorted by to avoid the failing type cast. In other words not an IComparable
, but instead an int
, a string
, a DateTime
, etc.
I had success to achieve this along the lines of the idea in this answer: How to check for the presence of an OrderBy in a ObjectQuery<T> expression tree
Define an interface which does not depend on the type to sort by but only the entity type. (The example below is generalized to arbitrary entities. If you only want that for User
remove the generic parameter and replace TEntity
in the queryables by User
.)
public interface IOrderByExpression<TEntity> where TEntity : class
{
IOrderedQueryable<TEntity> ApplyOrderBy(IQueryable<TEntity> query);
IOrderedQueryable<TEntity> ApplyThenBy(IOrderedQueryable<TEntity> query);
}
Define an implementation of that interface which now takes the type to sort by as a second generic parameter:
public class OrderByExpression<TEntity, TOrderBy> : IOrderByExpression<TEntity>
where TEntity : class
{
private Expression<Func<TEntity, TOrderBy>> _expression;
private bool _descending;
public OrderByExpression(Expression<Func<TEntity, TOrderBy>> expression,
bool descending = false)
{
_expression = expression;
_descending = descending;
}
public IOrderedQueryable<TEntity> ApplyOrderBy(
IQueryable<TEntity> query)
{
if (_descending)
return query.OrderByDescending(_expression);
else
return query.OrderBy(_expression);
}
public IOrderedQueryable<TEntity> ApplyThenBy(
IOrderedQueryable<TEntity> query)
{
if (_descending)
return query.ThenByDescending(_expression);
else
return query.ThenBy(_expression);
}
}
Then ApplyOrderBy
would look like this:
public IQueryable<TEntity> ApplyOrderBy<TEntity>(IQueryable<TEntity> query,
params IOrderByExpression<TEntity>[] orderByExpressions)
where TEntity : class
{
if (orderByExpressions == null)
return query;
IOrderedQueryable<TEntity> output = null;
foreach (var orderByExpression in orderByExpressions)
{
if (output == null)
output = orderByExpression.ApplyOrderBy(query);
else
output = orderByExpression.ApplyThenBy(output);
}
return output ?? query;
}
And it can be used the following way:
var query = context.Users ... ;
var queryWithOrderBy = ApplyOrderBy(query,
new OrderByExpression<User, string>(u => u.UserName), // a string, asc
new OrderByExpression<User, int>(u => u.UserId, true)); // an int, desc
var result = queryWithOrderBy.ToList(); // didn't throw an exception for me
The need to specify the generic type parameters explicitely in the OrderByExpression
instances is not nice but I couldn't find a way so that the compiler infers the types. (I was hoping it would, because the compiler infers the User
as TEntity
from query
for the ApplyOrderBy
method, then I expected that it knows the TEntity
of OrderByExpression
(equals User
as well). So the lambda parameter u
should be known as a User
and then the compiler could derive the type from UserName
as string
and from UserId
as int
. But this theory is apparently wrong. The compiler complains and wants to have the generic types explicitly.)
这篇关于如何将多个表达式传递给EF的OrderBy?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!