如何在实体框架中创建动态订单 [英] How to make a dynamic order in Entity Framework
问题描述
我有一个这样声明的字典：
private Dictionary< string，Expression< Func< Part，object>> > _orders = new Dictionary< string，Expression< Func< Part，object>>>>（）
{
{ Name，x => x.Name}，// string
{ Code，x => x.Code}，// string
{ EnterPrice，x => x.EnterPrice}，//小数
{ ExitPrice，x => x.ExitPrice}，//十进制
{ IsActive，x => （bool）x.Active}，// bool
{ Quantity，x => x.Quantity}，//十进制
{ Reserved，x => x.Reserved}，//十进制
};
我尝试使用以下代码携带数据：
NameValueCollection过滤器= HttpUtility.ParseQueryString（Request.RequestUri.Query）;
string sortField = filter [ sortField];
string sortOrder = filter [ sortOrder];
Func< IQueryable< Part>，IOrderedQueryable< Part>> orderBy = x => x.OrderBy（p => p.Id）;
if（！string.IsNullOrEmpty（sortField）&& _orders.ContainsKey（sortField））
{
bool sortMode =！string.IsNullOrEmpty（sortOrder）&& sortOrder！= desc;
if（sortMode）
{
orderBy = x => x.OrderBy（_orders [sortField]）;
}
其他
{
orderBy = x => x.OrderByDescending（_orders [sortField]）;
}
}
返回Ok（this.DbService.Query（null，filterQuery））;
而 Query 方法是：
public IQueryable< TEntity> Query（Expression< Func< TEntity，bool>>过滤器= null，
Func< IQueryable< TEntity> ;, IOrderedQueryable< TEntity> orderBy = null，bool noTracking = true）
{
IQueryable< TEntity>查询= DbContext.Set< TEntity>（）;
if（filter！= null）
{
query = query.Where（filter）;
}
if（orderBy！= null）query = orderBy（query）;
return noTracking吗？ query.AsNoTracking（）：查询；
}
但是当排序列不是 string 我收到以下异常
无法将类型 System.Boolean转换为类型 System.Object。 LINQ to Entities仅支持转换EDM基本类型或枚举类型。， ExceptionType： System.NotSupportedException， StackTrace：位于System.Web.Http.ApiController。< InvokeActionWithExceptionFilters> d__1.MoveNext（）\r \n--从上一个引发异常的位置开始的堆栈结束跟踪---在System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess（任务任务）处在System.Runtime .CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification（任务任务）位于System.Web.Http.Dispatcher.HttpControllerDispatcher。< SendAsync> d__0.MoveNext（）}
我认为字典的声明和/或初始化是错误的，因为如果我没有通过浏览器设置任何排序，则默认顺序为 x => x.Id （声明为内联），即使 Id long ，它也不会崩溃/ code>。我可以用其他方式声明字典来解决我的问题吗？
已解决问题
我删除了字典，并添加了以下扩展名，这些扩展名将字段名称和排序模式作为参数输入
公共静态类LinqExtension
{
public static IQueryable< T> OrderBy< T>（此IQueryable< T>源，字符串排序，布尔升序= true）
{
var type = typeof（T）;
var参数= Expression.Parameter（type， p）;
PropertyInfo属性；
Expression属性访问；
if（ordering.Contains（'。’））
{
//支持对子字段进行排序。
String [] childProperties = ordering.Split（’。’）;
属性= type.GetProperty（childProperties [0]）;
propertyAccess = Expression.MakeMemberAccess（参数，属性）;
for（int i = 1; i< childProperties.Length; i ++）
{
property = property.PropertyType.GetProperty（childProperties [i]）;
propertyAccess = Expression.MakeMemberAccess（propertyAccess，property）;
}
}
否则
{
属性= typeof（T）.GetProperty（ordering）;
propertyAccess = Expression.MakeMemberAccess（参数，属性）;
}
var orderByExp = Expression.Lambda（propertyAccess，parameter）;
MethodCallExpression resultExp = Expression.Call（typeof（Queryable），
升序？ OrderBy： OrderByDescending，
new [] {type，property.PropertyType}，source.Expression，
Expression.Quote（orderByExp））;
//返回source.OrderBy（x => orderByExp）;
返回source.Provider.CreateQuery< T>（resultExp）;
}
}
也由 Ivan Stoev提供的解决方案有效
字典定义是可以的-没有很好的方法来声明它具有不同类型的值。 / p>
问题是 Expression< Func< T，object>> 定义会生成其他 Expression.Convert 用于值类型属性。要使其与EF一起使用，必须删除转换表达式，并且必须动态调用相应的 Queryable 方法。可以将其封装在这样的自定义扩展方法中：
public静态类QueryableExtensions
{
public静态IQueryable< T> OrderBy< T>（此IQueryable< T>源，Expression< Func< T，object> keySelector，布尔递增）
{
var selectorBody = keySelector.Body;
//如果（selectorBody.NodeType == ExpressionType.Convert）
selectorBody =（（（UnaryExpression）selectorBody）.Operand;
//创建动态lambda表达式
var选择器= Expression.Lambda（selectorBody，keySelector.Parameters）;
//生成相应的Queryable方法调用
var queryBody = Expression.Call（typeof（Queryable），
升序？ OrderBy： OrderByDescending，
new Type [] {typeof（T），selectorBody.Type}，
source.Expression，Expression.Quote（selector））;
返回source.Provider.CreateQuery< T>（queryBody）;
}
}
，您的方案中的用法可能是这样的：
if（！string.IsNullOrEmpty（sortField）&_amp; _orders.ContainsKey（sortField））
orderBy = x => x.OrderBy（_orders [sortField]，sortOrder！= desc）;
I have a dictionary declared like this:
private Dictionary<string, Expression<Func<Part, object>>> _orders = new Dictionary<string, Expression<Func<Part, object>>>()
{
{"Name", x => x.Name}, //string
{"Code", x => x.Code}, //string
{"EnterPrice", x => x.EnterPrice}, //decimal
{"ExitPrice", x => x.ExitPrice}, //decimal
{"IsActive", x => (bool)x.Active }, //bool
{"Quantity", x => x.Quantity}, //decimal
{"Reserved", x => x.Reserved}, //decimal
};
I try to bring data using the following code:
NameValueCollection filter = HttpUtility.ParseQueryString(Request.RequestUri.Query);
string sortField = filter["sortField"];
string sortOrder = filter["sortOrder"];
Func<IQueryable<Part>, IOrderedQueryable<Part>> orderBy = x => x.OrderBy(p => p.Id);
if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
{
bool sortMode = !string.IsNullOrEmpty(sortOrder) && sortOrder != "desc";
if (sortMode)
{
orderBy = x => x.OrderBy(_orders[sortField]);
}
else
{
orderBy = x => x.OrderByDescending(_orders[sortField]);
}
}
return Ok(this.DbService.Query(null, filterQuery));
And Query method is:
public IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null, bool noTracking = true)
{
IQueryable<TEntity> query = DbContext.Set<TEntity>();
if (filter != null)
{
query = query.Where(filter);
}
if (orderBy != null) query = orderBy(query);
return noTracking ? query.AsNoTracking() : query;
}
But when the sort column is not string I obtain the following exception
"Unable to cast the type 'System.Boolean' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.","ExceptionType":"System.NotSupportedException","StackTrace":" at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()"}
I think that the dictionary declaration and/or initialization is wrong because if I do not have any sort set by browser then the default order will be x=>x.Id (which is declared inline) and it doesn't crash even if Id is long. Can I declare the dictionary in a different way to solve my problem?
PROBLEM SOLVED
I removed the dictionary and I added the following extension which receive the field name and sort mode as parametters
public static class LinqExtension
{
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, bool ascending = true)
{
var type = typeof(T);
var parameter = Expression.Parameter(type, "p");
PropertyInfo property;
Expression propertyAccess;
if (ordering.Contains('.'))
{
// support to be sorted on child fields.
String[] childProperties = ordering.Split('.');
property = type.GetProperty(childProperties[0]);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
for (int i = 1; i < childProperties.Length; i++)
{
property = property.PropertyType.GetProperty(childProperties[i]);
propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
}
}
else
{
property = typeof(T).GetProperty(ordering);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
}
var orderByExp = Expression.Lambda(propertyAccess, parameter);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable),
ascending ? "OrderBy" : "OrderByDescending",
new[] { type, property.PropertyType }, source.Expression,
Expression.Quote(orderByExp));
//return source.OrderBy(x => orderByExp);
return source.Provider.CreateQuery<T>(resultExp);
}
}
Also solution provided by Ivan Stoev works
The dictionary definition is ok - there is no good way to declare it to have values with different type.
The problem is that Expression<Func<T, object>> definition generates additional Expression.Convert for value type properties. To make it work with EF, the convert expression must be removed and corresponding Queryable method must be called dynamically. It can be encapsulated in a custom extension method like this:
public static class QueryableExtensions
{
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, Expression<Func<T, object>> keySelector, bool ascending)
{
var selectorBody = keySelector.Body;
// Strip the Convert expression
if (selectorBody.NodeType == ExpressionType.Convert)
selectorBody = ((UnaryExpression)selectorBody).Operand;
// Create dynamic lambda expression
var selector = Expression.Lambda(selectorBody, keySelector.Parameters);
// Generate the corresponding Queryable method call
var queryBody = Expression.Call(typeof(Queryable),
ascending ? "OrderBy" : "OrderByDescending",
new Type[] { typeof(T), selectorBody.Type },
source.Expression, Expression.Quote(selector));
return source.Provider.CreateQuery<T>(queryBody);
}
}
and the usage in your scenario could be like this:
if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
orderBy = x => x.OrderBy(_orders[sortField], sortOrder != "desc");
这篇关于如何在实体框架中创建动态订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
