在哪里可以找到使用LINQ和放大器的一个很好的例子; lambda表达式生成动态的地方和排序依据SQL？ [英] where can I find a good example of using linq & lambda expressions to generate dynamic where and orderby sql?
问题描述
我在哪里可以找到使用LINQ和放大器的一个很好的例子; lambda表达式生成动态SQL？
为例，我需要一种方法把这些参数
where can I find a good example of using linq & lambda expressions to generate dynamic sql? For example, I need a method to take these parameters
GoupOperator ('And' or 'OR')
A list of objects each with the following parameters:
SearchColumn.
SearchValue.
SearchOperator (equals, contains, does not equal ...)
和其他方法排序依据任何特定列升序或降序
And another method to orderby any particular column ascending or descending
如果这个问题已被正确回答过了，我会很乐意将其删除 - 没有以前的答案看见的Ive是新的一个人不够全面到LINQ表达式插入到与小麻烦--thanks
If this question has been properly answered before , I will gladly delete it - none of previous answers Ive seen are comprehensive enough for a person new to linq expressions to plug into an existing application with little trouble --thanks
推荐答案
我发现一对夫妇的LINQ扩展方法，现有应用程序（通用在哪里，排序依据的方法写的伊利亚Builuk是采取在CodeProject上的这里，显示如何做到这一点使用asp.net mvc的。
的方法构建一个动态表达式树 - 非常优雅的解决方案。
既然我已经使用传统的ASMX Web服务开始，我用他的助手在我的项目，只是做了一些改动让它运行的这里 -
I found a couple of linq extension methods (generic Where and OrderBy methods written by Ilya Builuk that take the columnname, search value and search operations plus a grouping operator) on codeproject here that shows how to do this using asp.net mvc. the methods construct a dynamic expression tree -very elegant solution. Since I had started using a traditional asmx web service, I used his helpers in my project and just made a few changes to get it running here -
下面有2种方法
public static class LinqExtensions
{
/// <summary>Orders the sequence by specific column and direction.</summary>
/// <param name="query">The query.</param>
/// <param name="sortColumn">The sort column.</param>
/// <param name="ascending">if set to true [ascending].</param>
public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
{
string methodName = string.Format("OrderBy{0}",
direction.ToLower() == "asc" ? "" : "descending");
ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
MemberExpression memberAccess = null;
foreach (var property in sortColumn.Split('.'))
memberAccess = MemberExpression.Property
(memberAccess ?? (parameter as Expression), property);
LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);
MethodCallExpression result = Expression.Call(
typeof(Queryable),
methodName,
new[] { query.ElementType, memberAccess.Type },
query.Expression,
Expression.Quote(orderByLambda));
return query.Provider.CreateQuery<T>(result);
}
public static IQueryable<T> Where<T>(this IQueryable<T> query,
string column, object value, WhereOperation operation)
{
if (string.IsNullOrEmpty(column))
return query;
ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
MemberExpression memberAccess = null;
foreach (var property in column.Split('.'))
memberAccess = MemberExpression.Property
(memberAccess ?? (parameter as Expression), property);
//change param value type
//necessary to getting bool from string
ConstantExpression filter = Expression.Constant
(
Convert.ChangeType(value, memberAccess.Type)
);
//switch operation
Expression condition = null;
LambdaExpression lambda = null;
switch (operation)
{
//equal ==
case WhereOperation.Equal:
condition = Expression.Equal(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//not equal !=
case WhereOperation.NotEqual:
condition = Expression.NotEqual(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//string.Contains()
case WhereOperation.Contains:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("Contains"),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break;
}
MethodCallExpression result = Expression.Call(
typeof(Queryable), "Where",
new[] { query.ElementType },
query.Expression,
lambda);
return query.Provider.CreateQuery<T>(result);
}
}
下面是我如何使用这些方法时，返回的对象仅仅是数据提供给客户端电网
Below is how I used these methods, the return object is simply a custom object that supplies data to a client side grid
public class Service1 : System.Web.Services.WebService
{
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public JgGrid SearchGrid(int rows, int page, string sidx, string sord,string filters)
{
AdvWorksDataContext dc = new AdvWorksDataContext();
JavaScriptSerializer serializer = new JavaScriptSerializer();
filters f = serializer.Deserialize<filters>(filters);
var p = dc.vProductAndDescriptions.AsQueryable();
if (f.groupOp == "AND")
foreach (var rule in f.rules)
p = p.Where<vProductAndDescription>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
);
else
{
//Or
var temp = (new List<vProductAndDescription>()).AsQueryable();
foreach (var rule in f.rules)
{
var t = p.Where<vProductAndDescription>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
);
temp = temp.Concat<vProductAndDescription>(t);
}
p = temp;
}
p = p.OrderBy<vProductAndDescription>(sidx, sord);
return new JgGrid(page, p, rows);
}
}
这篇关于在哪里可以找到使用LINQ和放大器的一个很好的例子; lambda表达式生成动态的地方和排序依据SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
