Grid custom data querying

By default the GridModelBuilder will query the data (order and get page), but you can to that yourself and pass the ordered and paged data. You might need to do that if you're using stored procedures, service calls, or any other data storage tech that doesn't support linq.
You might also want to load the data using async/await methods and only after that pass it to the GridModelBuilder, and for this scenario the GridModelBuilder has methods to apply the OrderBy and Page on your query before you call your Async Load method, see examples below.

Manually ordering and paging data


Here we are showing how we could query the data manually before passing it to the GridModelBuilder. One could also use something like Dynamic Linq or generate a sql script etc.
GridDemo/CustomQuerying.cshtml
@(Html.Awe().Grid("CustomQueryingGrid")
.Url(Url.Action("GetItems", "CustomQueryingGrid"))
.Height(300)
.Columns(
new Column { Bind = "Id", Groupable = false, Sortable = false, Width = 70 },
new Column { Bind = "Person" },
new Column { Bind = "Food.Name" }))
Demos/Grid/CustomQueryingGridController.cs
public IActionResult GetItems(GridParams g)
{
var pageSize = g.PageSize;
var query = 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"
? query.OrderBy(o => o.Person)
: query.OrderByDescending(o => o.Person);
else if (column == "Food.Name")
orderedItems = direction == "asc"
? query.OrderBy(o => o.Food.Name)
: query.OrderByDescending(o => o.Food.Name);
}
else
{
if (column == "Person")
orderedItems = direction == "asc"
? orderedItems.ThenBy(o => o.Person)
: orderedItems.ThenByDescending(o => o.Person);
else if (column == "Food.Name")
orderedItems = direction == "asc"
? orderedItems.ThenBy(o => o.Food.Name)
: orderedItems.ThenByDescending(o => o.Food.Name);
}
}

query = orderedItems;
}

var itemsCount = query.Count();
var pageItems = query.Skip((g.Page - 1) * pageSize).Take(pageSize).ToArray();

return Json(new GridModelBuilder<Lunch>(g)
{
KeyProp = o => o.Id,
Map = o => new { o.Id, o.Person, FoodName = o.Food.Name },
ItemsCount = itemsCount,
PageItems = pageItems
}.Build());
}

Execute async query before building model


In this grid we are fetching the data before calling GridModelBuilder.Build, this way you could use an async call to fetch data, await for the call and after pass the items and count to the GridModelBuilder.
GridDemo/CustomQuerying.cshtml
@(Html.Awe().Grid("GridQuery2")
.Url(Url.Action("GetEfAsyncItems", "CustomQueryingGrid"))
.Height(300)
.Columns(
new Column { Bind = "Id", Groupable = false, Sortable = false, Width = 70 },
new Column { Bind = "Person" },
new Column { Bind = "Food.Name" }))
Demos/Grid/CustomQueryingGridController.cs
public IActionResult GetEfAsyncItems(GridParams g)
{
var query = Db.Lunches.AsQueryable();

var gmb = new GridModelBuilder<Lunch>(g)
{
KeyProp = o => o.Id,
Map = o => new { o.Id, o.Person, FoodName = o.Food.Name }
};

// change OrderByFunc if necessary, e.g. if Column.Bind values don't match Lunch Properties names
//gmb.OrderByFunc = (q, rules) => { return q; };

// apply OrderBy according to GridParams
query = gmb.OrderBy(query);

// g.Key is set when calling api.update(id); not used in this demo
if (g.Key != null)
{
var item = Db.Find<Lunch>(Convert.ToInt32(g.Key));
gmb.GetItem = () => item;
}
else
{
gmb.ItemsCount = query.Count();
gmb.PageItems = gmb.GetPage(query).ToArray();
}

return Json(gmb.Build());
}
See also: Grid with List Count Column



Comments