Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>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 <a href="http://www.codeproject.com/Articles/58357/Using-jqGrid-s-search-toolbar-with-multiple-filter" rel="noreferrer">here</a> 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 <a href="http://kodesnippetsnstuff.wordpress.com/2012/03/23/jqgrid-traditional-asmx-webservice-multiple-search-sorting-using-custom-linq-extension-methods/" rel="noreferrer">here</a> - </p> <p>Here are the 2 methods</p> <pre><code>public static class LinqExtensions { /// &lt;summary&gt;Orders the sequence by specific column and direction.&lt;/summary&gt; /// &lt;param name="query"&gt;The query.&lt;/param&gt; /// &lt;param name="sortColumn"&gt;The sort column.&lt;/param&gt; /// &lt;param name="ascending"&gt;if set to true [ascending].&lt;/param&gt; public static IQueryable&lt;T&gt; OrderBy&lt;T&gt;(this IQueryable&lt;T&gt; 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&lt;T&gt;(result); } public static IQueryable&lt;T&gt; Where&lt;T&gt;(this IQueryable&lt;T&gt; 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&lt;T&gt;(result); } } </code></pre> <p>Below is how I used these methods, the return object is simply a custom object that supplies data to a client side grid</p> <pre><code>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&lt;filters&gt;(filters); var p = dc.vProductAndDescriptions.AsQueryable(); if (f.groupOp == "AND") foreach (var rule in f.rules) p = p.Where&lt;vProductAndDescription&gt;( rule.field, rule.data, (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op) ); else { //Or var temp = (new List&lt;vProductAndDescription&gt;()).AsQueryable(); foreach (var rule in f.rules) { var t = p.Where&lt;vProductAndDescription&gt;( rule.field, rule.data, (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op) ); temp = temp.Concat&lt;vProductAndDescription&gt;(t); } p = temp; } p = p.OrderBy&lt;vProductAndDescription&gt;(sidx, sord); return new JgGrid(page, p, rows); } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload