Note: there is a new version of jqGrid built specifically
for MVC, but it's a commercial version which I am unfamiliar with. This article
relates to the standard, free version.
Disclaimer: this code is not yet in production, and so is yet
to be thoroughly tested. There may also be a much simpler, entirely different approach - if so, I'd love to know.
Special thanks to the jqGrid expert Oleg @
ok-soft-gmbh.com for the fundamental groupOp/filter code.
Ok, so you have built your jqGrid, and all is well, but:
- You
need to add search functionality.
- You
need to have full search functionality because you are using the jqGrid
options multipleSearch: true,
and multipleGroup: true, so you will be and/or-ing multiple search clauses.
- You
don't want to code a specific search code for every jqGrid in your site,
you want some simple shared functionality for the search that can be
re-used.
- You want to be able to handle any text field name and value to support (3).
- You
are using MVC and LINQ.
The most fundamental problem here is that LINQ will only
allow type-safe queries. Out-of-the-box, this means that you cannot dynamically
construct queries on the fly.
This is a problem for jqGrid searches from multiple sources, as you do not know
the types (or names) for the where clause until run-time, or even the parameter value.
For example, normally in linq, you would write:
...where customer.id = 5
But we need something more like:
...where "customer.id = 5"
The most direct solution to this problem would probably be
with expression trees. However, expression trees are rather complex, hard to
read, and would become more involved to handle every possible where clause
scenario.
Fortunately,
there is a solution to this, the system.linq.dynamic class. This isn't part of
the standard framework, but you can download and compile it yourself under the
Microsoft Public Licence. For more information, see ScottGu's blog:
Ok, assuming you have built that dll, and added a reference in
your project and a using statement in your controller, how are we going to
build our where clause from what jqGrid provides us?
Well first we'll create some jqGrid support classes to
provide plumbing (I will refer to this file later as the jqGrid support classes file):
public class JqSearchIn
{
public string sidx { get; set; }
public string sord { get; set; }
public int page { get; set; }
public int rows { get; set; }
public bool _search {
get; set; }
public string
searchField { get; set;
}
public string
searchOper { get; set;
}
public string
searchString { get; set;
}
public string filters
{ get; set; }
// ignore this for now... all will become clear
public WhereClause
GenerateWhereClause(Type targetSearchType)
{
return new WhereClauseGenerator().Generate(_search, filters,
targetSearchType);
}
}
public class JqGridSearchOut
{
public int total { get; set; }
public int page { get; set; }
public int records { get; set; }
public Array rows
{ get; set; }
}
public class JqGridFilter
{
public GroupOp
groupOp { get; set;
}
public List<JqGridRule> rules { get;
set; }
public List<JqGridFilter> groups { get;
set; }
}
public class JqGridRule
{
public string field {
get; set; }
public Operations
op { get; set;
}
public string data { get; set; }
}
public enum GroupOp
{
AND,
OR
}
public enum Operations
{
eq, // "equal"
ne, // "not equal"
lt, // "less"
le, // "less or equal"
gt, // "greater"
ge, // "greater or equal"
bw, // "begins with"
bn, // "does not begin with"
ew, // "ends with"
en, // "does not end with"
cn, // "contains"
nc // "does not
contain"
//in, // "in"
//ni // "not in"
}
Having done that, we can now create the signature for
our controller, so assuming your jqGrid has an Ajax
call in it's setup, like this:
grid.jqGrid({
height:
300,
url:
ajxGetJqGridCustomers,
...
Then the controller signature would look like this:
public JsonResult GetJqGridCustomers (JqSearchIn si)
(Ideally this should have a [HttpPost] above it, but I'll
leave that up to you once you have everything else working).
Note that in MVC, the JqSearchIn parameter will auto-magically
map correctly to populate the class.
Ok, having got that far, we can now start thinking about how
to generate an appropriate where clause for all that search information the
JqSearchIn has been populated with. Fundamentally, we want to do something like this (note the type
being passed in, which is explained later):
var wc =
si.GenerateWhereClause(typeof(Customer));
// I'm using
entity framework here, but it could just as well
// be straight linq
(i.e. forget the .db part)
customers = db.Customers.Where(wc);
But we're going to have to use parameters (remember our code
must still be converted so it is (a) type-safe, and (b) parameterised against
injection), so we're going to rewrite that like this:
customers = db.Customers.Where(wc.Clause,
wc.FormatObjects);
We will add the object class to the jqGrid support classes file:
public class WhereClause
{
public string Clause
{ get; set; }
public object[]
FormatObjects { get; set;
}
}
We can now create the clause generator and other associated methods (again I would
suggest using the same jqGrid support classes file):
// if you make this static, then consider concurrency
issues
public class WhereClauseGenerator
{
private List<object> _formatObjects;
public WhereClause
Generate(bool _search, string
filters, Type targetSearchType )
{
_formatObjects = new List<object>();
return new WhereClause()
{
Clause = _search
&& !String.IsNullOrEmpty(filters) ?
ParseFilter(new
JavaScriptSerializer()
.Deserialize<JqGridFilter>(filters),targetSearchType).ToString()
: String.Empty,
FormatObjects =
_formatObjects.ToArray()
};
}
private readonly string[] FormatMapping = {
// 0 = field name,
// 1 = total no of formats/params so far - 1
// F = replaced by fmAdd
"({0} = @{1}{F})", //
"eq" - equal
"({0} <> @{1}{F})", //
"ne" - not equal
"({0} < @{1}{F})", //
"lt" - less than
"({0} <= @{1}{F})", //
"le" - less than or equal to
"({0} > @{1}{F})", //
"gt" - greater than
"({0} >= @{1}{F})", //
"ge" - greater than or equal to
"({0}.StartsWith(@{1}){F})", //
"bw" - begins with
"(!{0}.StartsWith(@{1}){F})", // "bn"
- does not begin with
"({0}.EndsWith(@{1}){F})", //
"ew" - ends with
"(!{0}.EndsWith(@{1}){F})", //
"en" - does not end with
"({0}.Contains(@{1}){F})", //
"cn" - contains
"(!{0}.Contains(@{1}){F})" //
"nc" - does not contain
};
private readonly string[] NullValueFormatMapping = {
// 0 = field name
"({0} = NULL)", //
"eq" - equal
"({0} != NULL)", // "ne"
- not equal
"(1=0)", //
"lt" - less than
"(1=1)", //
"le" - less than or equal to
"({0} != NULL)", // "gt"
- greater than
"(1=1)", //
"ge" - greater than or equal to
"({0} != NULL)", // "bw"
- begins with
"({0} != NULL)", // "bn"
- does not begin with
"({0} != NULL)", // "ew"
- ends with
"({0} != NULL)", // "en"
- does not end with
"({0} != NULL)", // "cn"
- contains
"({0} !=
NULL)" // "nc" - does not contain
};
private StringBuilder
ParseRule(ICollection<JqGridRule> rules, GroupOp
groupOp, Type targetSearchType)
{
if (rules == null ||
rules.Count == 0)
return null;
var sb = new StringBuilder();
bool firstRule = true;
var props =
targetSearchType.GetProperties().ToDictionary(p => p.Name, p =>
p.PropertyType);
foreach (var rule in rules)
{
if (!firstRule)
// skip groupOp before the first rule
sb.Append(groupOp);
else
firstRule = false;
// get the object type of the rule
Type
ruleParseType;
bool emptyNullable = false;
try
{
Type ruleType = ruleParseType =
props[rule.field];
if (ruleType.IsGenericType &&
ruleType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
if (rule.data == "")
emptyNullable = true;
ruleParseType = Nullable.GetUnderlyingType(ruleType);
}
}
catch(KeyNotFoundException)
{
throw new
ArgumentOutOfRangeException(rule.field + " is not a property of type "
+
targetSearchType);
}
// parse it in as the correct object type
var fmAdd = "";
if (ruleParseType == typeof(string))
{
_formatObjects.Add(rule.data);
if (rule.data == "")
fmAdd = " OR {0} = NULL";
}
else
{
if (emptyNullable)
_formatObjects.Add(null);
else
{
var parseMethod =
ruleParseType.GetMethod("Parse", new[] {typeof (string)});
if (parseMethod != null)
_formatObjects.Add(parseMethod.Invoke(props[rule.field], new object[]
{rule.data}));
else
throw new ArgumentOutOfRangeException(rule.field +
" is not a string and cannot be parsed
either!!");
}
}
string fm = emptyNullable ? NullValueFormatMapping[(int) rule.op]
: FormatMapping[(int) rule.op].Replace("{F}",fmAdd);
sb.AppendFormat(fm, rule.field, _formatObjects.Count-1);
}
return sb.Length > 0 ? sb : null;
}
private void
AppendWithBrackets(StringBuilder dest, StringBuilder src)
{
if (src == null ||
src.Length == 0)
return;
if (src.Length > 2 && src[0] != '(' && src[src.Length - 1] != ')')
{
dest.Append('(');
dest.Append(src);
dest.Append(')');
}
else
{
// verify that no other '(' and ')' exist in the b. so that
// we have no case like src = "(x < 0) OR (y >
0)"
for (int i = 1; i
< src.Length - 1; i++)
{
if (src[i] != '('
&& src[i] != ')') continue;
dest.Append('(');
dest.Append(src);
dest.Append(')');
return;
}
dest.Append(src);
}
}
private StringBuilder
ParseFilter(ICollection<JqGridFilter> groups, GroupOp
groupOp, Type targetSearchType)
{
if (groups == null ||
groups.Count == 0)
return null;
var sb = new StringBuilder();
bool firstGroup = true;
foreach (var group in groups)
{
var sbGroup = ParseFilter(group, targetSearchType);
if (sbGroup == null
|| sbGroup.Length == 0)
continue;
if (!firstGroup)
// skip groupOp before the first group
sb.Append(groupOp);
else
firstGroup = false;
sb.EnsureCapacity(sb.Length + sbGroup.Length + 2);
AppendWithBrackets(sb, sbGroup);
}
return sb;
}
private StringBuilder
ParseFilter(JqGridFilter filters, Type targetSearchType)
{
var
parsedRules = ParseRule(filters.rules, filters.groupOp, targetSearchType);
var parsedGroups = ParseFilter(filters.groups,
filters.groupOp, targetSearchType);
if (parsedRules != null
&& parsedRules.Length > 0)
{
if
(parsedGroups != null &&
parsedGroups.Length > 0)
{
var groupOpStr =
filters.groupOp.ToString();
var sb = new
StringBuilder(parsedRules.Length +
parsedGroups.Length + groupOpStr.Length + 4);
AppendWithBrackets(sb, parsedRules);
sb.Append(groupOpStr);
AppendWithBrackets(sb, parsedGroups);
return sb;
}
return parsedRules;
}
return parsedGroups;
}
}
You'll notice several things in the above listing:
1. Most obviously, as expected, there is some recursion to build the
clause (remember we are supporting multiple group and/ors). Note - for security you should never allow jgGrid's built-in showquery option string to be passed into your query.
2. There are various format objects for the parameters to
ensure the where clause parameter list is built appropriately.
3. Most importantly, a targetSearchType is passed in. This
is the most interesting part.
As you recall, the initial call from the controller is as
follows:
si.GenerateWhereClause(typeof(Customer));
Which further down the stack becomes gets passed in to
ParseRule:
private StringBuilder ParseRule(ICollection<JqGridRule> rules, GroupOp
groupOp, Type targetSearchType)
ParseRule then uses Reflection to get the underlying type of
each property being searched against in the entity/class, in this case
Customer. It also uses reflection to get the correct parse method for the type.
This means that we can parse in the correct type and value to build the where
clause. Or in other words, we can keep dynamic linq happy.
One last bit of plumbing is to add a view model for the
output:
public class CustomerItemsJqGridRowOut
{
public int Id { get; set; }
public string
FirstName { get; set;
}
public string LastName
{ get; set; }
}
Ok, having done the tricky parts, all that remains is to head
back to the controller and finish off by adding paging and the rest of the code:
public JsonResult GetJqGridCustomers(JqSearchIn si)
{
int totalRecords;
int startRow = (si.page * si.rows) + 1;
int skip = (si.page > 0 ? si.page - 1 : 0) *
si.rows;
// note - these queries require "using
System.Dynamic.Linq" library
IQueryable<Customer> customers;
if (si._search && !String.IsNullOrEmpty(si.filters))
{
var wc = si.GenerateWhereClause(typeof(Customer));
customers = db.Customers.Where(wc.Clause, wc.FormatObjects);
totalRecords = customers.Count();
customers = customers
.OrderBy(si.sidx + " " +
si.sord)
.Skip(skip)
.Take(si.rows);
}
else
{
customers = db.Customers;
totalRecords = customers.Count();
customers = customers
.OrderBy(si.sidx + " " +
si.sord)
.Skip(skip)
.Take(si.rows);
}
var customersList = customers.ToList();
var totalPages = (int)Math.Ceiling((float)totalRecords
/ si.rows);
var grid = new
JqGridSearchOut
{
total = totalPages,
page = si.page,
records = totalRecords,
rows = (from cst in customersList
select new
CustomerItemsJqGridRowOut()
{
Id = cst.Id,
FirstName =
cst.FirstName,
LastName =
cst.LastName
}).ToArray()
};
return Json(grid);
}
Hopefully this should be of use to someone out there.
Edit - I should probably do something about the OrderBy() clause as that part is obviously not parameterised, and could be injection attacked.
