jqgrid with asp.net webmethod and json working with sorting, paging, searching and LINQ -- but needs dynamic operators

Question

THIS WORKS! .. but still needs one more thing...

Okay, so this is both a "comment" and question. First, is the working example that may help others in search of a asp.net webmethod / jqGrid approach. The code below completely works for sending/receiving JSON parameters from and to jqGrid in order to have correct paging, sorting, filtering (with single search only) utilizing LINQ.. it uses pieces from here and there...

Second, is my question: Has anyone determined an appropriate method for accounting for dynamic operators being sent to the codebehind? Since the client can potentially send "eq" (equal), "cn" (contains) "gt" (greater than), I need a better way of dynamically generating a whereclause that isn't just limited to me building a whereclause string with "=" or "<>", but rather can encompass that along with Dynamic Linq's ability to utilize .Contains or .EndsWith, etc.

I may need some sort of predicate builder function ..

code that handles this as of now (which works, but is limited):

if (isSearch) {
    searchOper = getOperator(searchOper); // need to associate correct operator to value sent from jqGrid
    string whereClause = String.Format("{0} {1} {2}", searchField, searchOper, "@" + searchField);

    //--- associate value to field parameter
    Dictionary<string, object> param = new Dictionary<string, object>();
    param.Add("@" + searchField, searchString);

    query = query.Where(whereClause, new object[1] { param });
}

On with the show.........

==================================================

First, THE JAVASCRIPT

<script type="text/javascript">
$(document).ready(function() {

    var grid = $("#grid");

    $("#grid").jqGrid({
        // setup custom parameter names to pass to server
        prmNames: { 
            search: "isSearch", 
            nd: null, 
            rows: "numRows", 
            page: "page", 
            sort: "sortField", 
            order: "sortOrder"
        },
        // add by default to avoid webmethod parameter conflicts
        postData: { searchString: '', searchField: '', searchOper: '' },
        // setup ajax call to webmethod
        datatype: function(postdata) {    
            $(".loading").show(); // make sure we can see loader text
            $.ajax({
                url: 'PageName.aspx/getGridData',  
                type: "POST",  
                contentType: "application/json; charset=utf-8",  
                data: JSON.stringify(postdata),
                dataType: "json",
                success: function(data, st) {
                    if (st == "success") {
                        var grid = $("#grid")[0];
                        grid.addJSONData(JSON.parse(data.d));
                    }
                },
                error: function() {
                    alert("Error with AJAX callback");
                }
            }); 
        },
        // this is what jqGrid is looking for in json callback
        jsonReader: {  
            root: "rows",
            page: "page",
            total: "totalpages",
            records: "totalrecords",
            cell: "cell",
            id: "id", //index of the column with the PK in it 
            userdata: "userdata",
            repeatitems: true
        },
        colNames: ['Id', 'First Name', 'Last Name'],   
        colModel: [
            { name: 'id', index: 'id', width: 55, search: false },
            { name: 'fname', index: 'fname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} },
            { name: 'lname', index: 'lname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} }
        ],  
        rowNum: 10,  
        rowList: [10, 20, 30],
        pager: jQuery("#pager"),
        sortname: "fname",   
        sortorder: "asc",
        viewrecords: true,
        caption: "Grid Title Here",
    gridComplete: function() {
        $(".loading").hide();
    }
    }).jqGrid('navGrid', '#pager', { edit: false, add: false, del: false },
    {}, // default settings for edit
    {}, // add
    {}, // delete
    { closeOnEscape: true, closeAfterSearch: true}, //search
    {}
)
});
</script>

==================================================

Second, THE C# WEBMETHOD

[WebMethod]
public static string getGridData(int? numRows, int? page, string sortField, string sortOrder, bool isSearch, string searchField, string searchString, string searchOper) {
    string result = null;

    MyDataContext db = null;
    try {
        //--- retrieve the data
        db = new MyDataContext("my connection string path");  
        var query = from u in db.TBL_USERs
                    select new User {
                        id = u.REF_ID, 
                        lname = u.LAST_NAME, 
                        fname = u.FIRST_NAME
                    };

        //--- determine if this is a search filter
        if (isSearch) {
            searchOper = getOperator(searchOper); // need to associate correct operator to value sent from jqGrid
            string whereClause = String.Format("{0} {1} {2}", searchField, searchOper, "@" + searchField);

            //--- associate value to field parameter
            Dictionary<string, object> param = new Dictionary<string, object>();
            param.Add("@" + searchField, searchString);

            query = query.Where(whereClause, new object[1] { param });
        }

        //--- setup calculations
        int pageIndex = page ?? 1; //--- current page
        int pageSize = numRows ?? 10; //--- number of rows to show per page
        int totalRecords = query.Count(); //--- number of total items from query
        int totalPages = (int)Math.Ceiling((decimal)totalRecords / (decimal)pageSize); //--- number of pages

        //--- filter dataset for paging and sorting
        IQueryable<User> orderedRecords = query.OrderBy(sortfield);
        IEnumerable<User> sortedRecords = orderedRecords.ToList();
        if (sortorder == "desc") sortedRecords= sortedRecords.Reverse();
        sortedRecords = sortedRecords
          .Skip((pageIndex - 1) * pageSize) //--- page the data
          .Take(pageSize);

        //--- format json
        var jsonData = new {
            totalpages = totalPages, //--- number of pages
            page = pageIndex, //--- current page
            totalrecords = totalRecords, //--- total items
            rows = (
                from row in sortedRecords
                select new {
                    i = row.id,
                    cell = new string[] {
                        row.id.ToString(), row.fname, row.lname 
                    }
                }
           ).ToArray()
        };

        result = Newtonsoft.Json.JsonConvert.SerializeObject(jsonData);

    } catch (Exception ex) {
        Debug.WriteLine(ex);
    } finally {
        if (db != null) db.Dispose();
    }

    return result;
}

/* === User Object =========================== */
public class User {
    public int id { get; set; }
    public string lname { get; set; }
    public string fname { get; set; }
}

==================================================

Third, NECESSITIES

1. In order to have dynamic OrderBy clauses in the LINQ, I had to pull in a class to my AppCode folder called 'Dynamic.cs'. You can retrieve the file from downloading here. You will find the file in the "DynamicQuery" folder. That file will give you the ability to utilized dynamic ORDERBY clause since we don't know what column we're filtering by except on the initial load.

2. To serialize the JSON back from the C-sharp to the JS, I incorporated the James Newton-King JSON.net DLL found here : http://json.codeplex.com/releases/view/37810. After downloading, there is a "Newtonsoft.Json.Compact.dll" which you can add in your Bin folder as a reference

3. Here's my USING's block using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web.UI.WebControls; using System.Web.Services; using System.Linq.Dynamic;

4. For the Javascript references, I'm using the following scripts in respective order in case that helps some folks: 1) jquery-1.3.2.min.js ... 2) jquery-ui-1.7.2.custom.min.js ... 3) json.min.js ... 4) i18n/grid.locale-en.js ... 5) jquery.jqGrid.min.js

5. For the CSS, I'm using jqGrid's necessities as well as the jQuery UI Theme: 1) jquery_theme/jquery-ui-1.7.2.custom.css ... 2) ui.jqgrid.css

The key to getting the parameters from the JS to the WebMethod without having to parse an unserialized string on the backend or having to setup some JS logic to switch methods for different numbers of parameters was this block

postData: { searchString: '', searchField: '', searchOper: '' },

Those parameters will still be set correctly when you actually do a search and then reset to empty when you "reset" or want the grid to not do any filtering

Hope this helps some others!!!! And thanks if you have time to read and reply regarding the dynamic approach to building the whereclause with operators at runtime

Answer 1

Consider this extension method, that converts a string into a MemberExpression:

public static class StringExtensions
{
    public static MemberExpression ToMemberExpression(this string source, ParameterExpression p)
    {
        if (p == null)
            throw new ArgumentNullException("p");

        string[] properties = source.Split('.');

        Expression expression = p;
        Type type = p.Type;

        foreach (var prop in properties)
        {
            var property = type.GetProperty(prop);
            if (property == null)
                throw new ArgumentException("Invalid expression", "source");

            expression = Expression.MakeMemberAccess(expression, property);
            type = property.PropertyType;
        }

        return (MemberExpression)expression;
    }
}

The method below converts the strings that you have into an Lambda Expression, that you can use to filter a Linq query. It is a generic method, with T as the domain entity.

    public virtual Expression<Func<T, bool>> CreateExpression<T>(string searchField, string searchString, string searchOper)
    {
        Expression exp = null;
        var p = Expression.Parameter(typeof(T), "p");

        try
        {
            Expression propertyAccess = searchField.ToExpression(p);

            switch (searchOper)
            {
                case "bw":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "cn":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "ew":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "gt":
                    exp = Expression.GreaterThan(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "ge":
                    exp = Expression.GreaterThanOrEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "lt":
                    exp = Expression.LessThan(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "le":
                    exp = Expression.LessThanOrEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "eq":
                    exp = Expression.Equal(propertyAccess, Expression.Constant(searchString.ToType(propertyAccess.Type), propertyAccess.Type));
                    break;
                case "ne":
                    exp = Expression.NotEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                default:
                    return null;
            }

            return (Expression<Func<T, bool>>)Expression.Lambda(exp, p);
        }
        catch
        {
            return null;
        }
    }

So, you can use it like this:

db.TBL_USERs.Where(CreateExpression<TBL_USER>("LAST_NAME", "Costa", "eq"));