Grid with Custom querying, Sorting And Paging

Grid custom querying

The default scenario is that you give an IQueryable to the GridModelBuilder and it will sort it and page it using .OrderBy and .Skip/.Take, this works ok on EntityFramework, but if you're doing something like sql procedures, service calls etc. you might want to do this sorting and paging yourself.

Sorting rules and current page are in the GridParams {SortNames[], SortDirections[], Page} parameter and to tell the GridModelBuilder not to do any querying you have to set the PageCount parameter on it. (grouping doesn't require any querying)

GridDemo/CustomQuerying.cshtml

@(Html.Awe().Grid("CustomQueryingGrid")
                .Url(Url.Action("GetItems", "CustomQueryingGrid"))
                .Height(450)
                .Columns(
                        new Column { Bind = "Id", Groupable = false, Sortable = false, Width = 70 },
                        new Column { Bind = "Person" },
                        new Column { Bind = "Food" }))

Demos/Grid/CustomQueryingGridController.cs

public class CustomQueryingGridController : Controller
{
    public ActionResult GetItems(GridParams g)
    {
        const int PageSize = 10;
        var items = Db.Lunches.AsQueryable();

        if (g.SortNames != null)
        {
            IOrderedQueryable<Lunch> orderedItems = null;

            // doing this for demo purposes
            // one might use something like Dynamic Linq 
            // or generate a sql string etc.

            for (int i = 0; i < g.SortNames.Length; i++)
            {
                var column = g.SortNames[i];
                var direction = g.SortDirections[i];

                if (i == 0)
                {
                    if (column == "Person")
                        orderedItems = direction == "asc"
                                    ? items.OrderBy(o => o.Person)
                                    : items.OrderByDescending(o => o.Person);
                    else if (column == "Food")
                        orderedItems = direction == "asc"
                                    ? items.OrderBy(o => o.Food)
                                    : items.OrderByDescending(o => o.Food);
                }
                else
                {
                    if (column == "Person")
                        orderedItems = direction == "asc"
                                ? orderedItems.ThenBy(o => o.Person)
                                : orderedItems.ThenByDescending(o => o.Person);
                    else if (column == "Food")
                        orderedItems = direction == "asc"
                                ? orderedItems.ThenBy(o => o.Food)
                                : orderedItems.ThenByDescending(o => o.Food);
                }
            }

            items = orderedItems;
        }

        var count = items.Count();
        var totalPages = (int)Math.Ceiling((float)count / PageSize);
        var page = items.Skip((g.Page - 1) * PageSize).Take(PageSize);

        return Json(new GridModelBuilder<Lunch>(page.AsQueryable(), g)
            {
                Key = "Id",
                Map = o => new { o.Id, o.Person, o.Food },
                PageCount = totalPages,
                ItemsCount = count
            }.Build());
    }
}