Advanced jqGrid Search Functionality with LINQ (and EntityFramework) and ASP.NET MVC.

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:

1. You need to add search functionality.
2. 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.
3. 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.
4. You want to be able to handle any text field name and value to support (3).
5. 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:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

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.