jqGrid LINQ and anonymous types

jqGrid takes the following JSON format:

{ 
  "total": "5", 
  "page": "2", 
  "records": "55",
  "rows" : [
    {"id" :"21", "cell" :["cell11", "cell12", "cell13"]},
    {"id" :"22", "cell" :["cell21", "cell22", "cell23"]},
      ...
    {"id" :"30", "cell" :["cell31", "cell32", "cell33"]},
  ]
}

I'm trying to make a method as reusable as possible to pass data back through AJAX to the jqGrid.

var result = new
{
    total = (int) Math.Ceiling((double) totalCount/PageSize),
    page = PageIndex,
    records = totalCount,
    rows = data.Select((d, id) => new {id, cell = d.SerializeGridParameters()}).ToArray()
};

As you can see, currently I managed to add the index without extra effort, but I'm having trouble with the field data.

So far, I managed to deal with it by using an interface:

public interface IGridParameterListable
{
    List<string> SerializeGridParameters();
}

For my data (which is an IEnumerable<T> where T : IGridParameterListable).

Thing is I'd much rather have a generic method that just blindly converts objects property values to a List<string>..

Doesn't sound too cute, I know, I'm open to other ideas.

I w开发者_开发技巧ant to avoid as much as possible repeating the data structure for a grid on both the client and server sides.

This is not an answer, just some source code

In case anyone wants my implementation of jqGrid for ASP.NET WebForms, in light of it being pretty hard to implement, I'll post the code here.

First, some JSON classes:

public class GridFilter
{
    public string groupOp { get; set; }
    public GridRule[] rules { get; set; }
}

public class GridRule
{
    public string field { get; set; }
    public string op { get; set; }
    public string data { get; set; }
}

public class GridSettings
{
    public bool IsSearch { get; set; }
    public int PageSize { get; set; }
    public int PageIndex { get; set; }
    public string SortColumn { get; set; }
    public string SortOrder { get; set; }

    public GridFilter Where { get; set; }
}

The front-end code, I changed some of it so it allows me to post to the service with just one parameter, filled with the Grid info. Also, I don't really care for single filters, so I just support the multiple filter mode (which is strictly better in my opinion anyways).

<table id="UsersGrid"></table>
<div id="UsersGridPager"></div>

<script type="text/javascript">
    $(document).ready(function () {
        $('#UsersGrid').jqGrid({
            colNames: ['ID', 'Online', 'Computer', 'IP', 'User'],
            colModel: [
                { name: 'ID', width: 100, index: 'ID', searchoptions: { sopt: ['eq', 'ne']} },
                { name: 'IsOnline', width: 100, index: 'IsOnline', searchoptions: { sopt: ['eq', 'ne']} },
                { name: 'Name', index: 'Name', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
                { name: 'IP', index: 'IP', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
                { name: 'User', index: 'User', searchoptions: { sopt: ['eq', 'ne', 'cn']} }
              ],
            jsonReader: {
                root: function (json) { return JSON.parse(json.d).rows; },
                page: function (json) { return JSON.parse(json.d).page; },
                total: function (json) { return JSON.parse(json.d).total; },
                records: function (json) { return JSON.parse(json.d).records; }
            },
            serializeGridData: jqGridSettings,
            caption: "Usuarios",
            emptyrecords: "No se encontraron usuarios",
            url: "/GridTest/GridTestService.asmx/GetData",
            ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
            datatype: 'json',
            mtype: 'POST',
            height: 250,
            rowNum: 10,
            rowList: [10, 25, 50],
            rownumbers: true,
            autowidth: true,
            pager: "#UsersGridPager"
        }).navGrid("#UsersGridPager",
            {
                refresh: true,
                add: false,
                edit: false,
                del: false,
                search: true
            },
            {},
            {},
            {},
            {
                sopt: ["eq", "ne", "cn"],
                multipleSearch: true,
                showQuery: true
            }
        );

        function jqGridSettings(p) {
            var settings = {
                grid: {
                    PageIndex: p.page,
                    PageSize: p.rows,
                    IsSearch: p._search,
                    SortColumn: p.sidx,
                    SortOrder: p.sord,
                    Where: jqGridFilters(p.filters)
                }
            };

            return JSON.stringify(settings);
        }

        function jqGridFilters(json) {
            var filters = {};
            if (!json) {
                return;
            }
            if (!json.length) {
                return;
            }
            var parsed = JSON.parse(json);
            if (!!parsed.rules) {
                filters = parsed;
            }

            return filters;
        }
    });

Now, for the actual implementation... firstly, there are a couple of LINQ extension methods we will be needing, for the ordering and sorting of the data. These are as follows:

public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
{
    if (string.IsNullOrEmpty(sortColumn))
        return query;

    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, string 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
    LambdaExpression lambda = null;
    switch (operation)
    {
        case "eq": // equal
            {
                lambda = Expression.Lambda(Expression.Equal(memberAccess, filter), parameter);
                break;
            }
        case "ne": // not equal
            {
                lambda = Expression.Lambda(Expression.NotEqual(memberAccess, filter), parameter);
                break;
            }
        case "cn": // contains
            {
                Expression condition = Expression.Call(memberAccess,
                                                       typeof (string).GetMethod("Contains"),
                                                       Expression.Constant(value.ToString()));
                lambda = Expression.Lambda(condition, parameter);
                break;
            }
    }

    var result = Expression.Call(
           typeof(Queryable), "Where",
           new[] { query.ElementType },
           query.Expression,
           lambda);

    return query.Provider.CreateQuery<T>(result);
}

The actual implementation of the search, as a matter of preference, I put it in my GridSettings class, as a member method, but it could be an extension method for IQueryable, too.

public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select)
{
    //filtering
    if (IsSearch && Where.rules != null)
    {
        if (Where.groupOp == "AND") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.And.Name()
        {
            foreach (var rule in Where.rules)
                query = query.Where(rule.field, rule.data, rule.op);
        }
        else if (Where.groupOp == "OR") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.Or.Name()
        {
            var temp = (new List<T>()).AsQueryable();

            foreach (var rule in Where.rules)
            {
                var t = query.Where(rule.field, rule.data, rule.op);
                temp = temp.Concat(t);
            }

            //remove repeat records
            query = temp.Distinct();
        }
    }

    //sorting
    query = query.OrderBy(SortColumn, SortOrder);

    //count
    var totalCount = query.Count();

    //paging
    var data = query.Skip((PageIndex - 1) * PageSize).Take(PageSize);

    //convert to grid format
    var result = new
    {
        total = (int)Math.Ceiling((double)totalCount / PageSize),
        page = PageIndex,
        records = totalCount,
        rows = data.Select((d, id) => new { id, cell = select(d) }).ToArray()
    };

    return JsonConvert.SerializeObject(result);
}

Then, for the actual service we will be needing snippets like this:

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[ScriptService]
public class GridTestService : System.Web.Services.WebService
{
    [WebMethod]
    [ScriptMethod]
    public string GetData(GridSettings grid)
    {
        var query = new FakeComputersRepository().Computers();
        var response = grid.SerializeQuery(query, d => new List<string>
        {
            d.ID.ToString(),
            d.IsOnline.ToString(),
            d.Name,
            d.IP,
            d.User
        });

        return response;
    }
}

As you can see I just fetch the data, pick the columns I want to display (which have to be the same and in the same order as in the grid in the client-side), and that's pretty much it. The idea is for this to be as reusable as possible.

Nico, that is the best implementation of jqgrid with asp.net webforms I have seen. I have made a small but important improvement by adding a third parameter to the SerializeQuery method: a list of the actual row id's, so that we can add these to the result. Without that list, the row id that gets sent to the client is actually just the index of the item in the list with row data and not the actual id from the database row. That is not useable in a scenario where you need to enable editing and deletion in jqgrid.

public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select, Func<T, List<int>> ids)
    {
        //filtering
        if (IsSearch && Where.rules != null)
        {
            if (Where.groupOp == "AND") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.And.Name()
            {
                foreach (var rule in Where.rules)
                    query = query.Where(rule.field, rule.data, rule.op);
            }
            else if (Where.groupOp == "OR") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.Or.Name()
            {
                var temp = (new List<T>()).AsQueryable();

                foreach (var rule in Where.rules)
                {
                    var t = query.Where(rule.field, rule.data, rule.op);
                    temp = temp.Concat(t);
                }

                //remove repeat records
                query = temp.Distinct();
            }
        }

        //sorting
        query = query.OrderBy(SortColumn, SortOrder);

        //count
        var totalCount = query.Count();

        //paging
        var data = query.Skip((PageIndex - 1) * PageSize).Take(PageSize);

        //convert to grid format
        var result = new
        {
            total = (int)Math.Ceiling((double)totalCount / PageSize),
            page = PageIndex,
            records = totalCount,
            rows = data.Select((d) => new { 
                id = ids(d),
                cell = select(d) 
            }).ToArray()
        };

        return JsonConvert.SerializeObject(result);
    }

This might be a better option.

public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select)
{
    // stuff ...

    var result = new
    {
        total = (int)Math.Ceiling((double)totalCount / PageSize),
        page = PageIndex,
        records = totalCount,
        rows = data.Select((d, id) => new { id, cell = select(d) }).ToArray()
    };

    // stuff ...
}

I eliminate the need of an interface, and move the conversion to the implementation of the query for each particular grid. In this example:

[WebMethod]
[ScriptMethod]
    public string GetData(GridSettings grid)
    {
        var query = new FakeComputersRepository().Computers();
        var response = grid.SerializeQuery(query, d => new List<string>
        {
            d.ID.ToString(),
            d.IsOnline.ToString(),
            d.Name,
            d.IP,
            d.User
        });

        return response;
    }

A tad better, I think. Any other ideas, to further expand on this?