Update: 06/18/2013 – It is recommended that you follow this post for Kendo UI Grid, Datasource filtering http://blog.longle.net/2013/06/18/mvc-4-web-api-odata-entity-framework-kendo-ui-grid-datasource-with-mvvm/
Update: 05/11/2012 – Added support for querying objects with child properties
I recently did a post Telerik’s HTML5 Kendo UI (Grid, Detail Template, TabStrip) which illustrated how to wire up their HTML5 Grid and handle server side paging. After doing so I quickly found myself needing to wire up the rest of server side bells and whistles e.g. sorting, filtering, etc.. Did some relentless googling and didn’t find any good resources on how to do this with MVC3 and EF4 so hence this blog post for the those of us that are doing just that. Rather than starting from scratch I’ll go ahead and continue where the my last blog left off.
So this first thing we need to do is configure our Kendo UI Grid for to do server side sorting and filtering so that we decompose what the requests pay loads look like coming from the Grid when performing these types of actions on it.
Configuring the Kendo UI Grid:
$(document).ready(function () {
var grid = $("#grid").kendoGrid({
dataSource: {
type: "json",
serverPaging: true,
serverSorting: true,
serverFiltering: true,
allowUnsort: true,
pageSize: 5,
transport: {
read: {
url: "Products/GetAll",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
data: {}
},
parameterMap: function (options) {
return JSON.stringify(options);
}
},
schema: {
model: {
fields: {
ProductId: { type: "number" },
Name: { type: "string" },
Status: { type: "string" },
Created: { type: "date" }
}
},
data: "Products",
total: "TotalCount"
}
},
height: 700,
sortable: true,
groupable: true,
pageable: true,
filterable: true,
columns: [
{ field: "ProductId", title: "ProductId" },
{ field: "ProductType", title: "ProductType" },
{ field: "Name", title: "Name" },
{ field: "Created", title: "Created", format: "{0:MM/dd/yyyy}" }
],
detailTemplate: kendo.template($("#template").html()),
toolbar: kendo.template($("#toolBarTemplate").html()),
detailInit: detailInit,
dataBound: function () {
this.expandRow(this.tbody.find("tr.k-master-row").first());
}
});
var dropDown = grid.find("#requestType").kendoDropDownList({
dataTextField: "text",
dataValueField: "value",
autoBind: false,
optionLabel: "All",
dataSource: [
{ text: "Electronics", value: "2" },
{ text: "Machinery", value: "1" }
],
change: function () {
var value = this.value();
if (value) {
grid.data("kendoGrid").dataSource.filter(
{ field: "ProductType", operator: "eq", value: parseInt(value) });
} else {
grid.data("kendoGrid").dataSource.filter({});
}
}
});
});
I’ve highlighted some of the major changes we made to our configuration which include setting up the Grid for server side actions: paging, sorting, filter, unsort and surfacing the filteration capabilities to the UI. Lines 54-72 is for setting up a Grid Toolbar which will contain a Kendo UI DrownDownList so that we can filter the Grid on ProductTypes which we will come back around to later on.
Now that we have the Grid configured for server side processing let’s take a quick look at what’s going down the wire in terms of pay loads for each of these actions so that we can mock up our models for these requests. When loading up IE Developer Tools (hit F12 or Tools > Developer Tools) and clicking on the Network Tab to start capturing network traffic we can see the actual pay load request for each of these actions.
So we can see that the pay load that is coming down the wire when a user performs a filter and sort on the grid is:
{"take":5,"skip":0,"page":1,"pageSize":5,"group":[],"filter":{"filters":[{"field":"ProductType","operator":"eq","value":"3"}],"logic":"and"},"sort":[{"field":"Name","dir":"desc"}]}
From this we can start mocking up our models needed for these types of Grid Actions for our Controller.
namespace MvcApplication3.Models
{
public class GridFilter
{
public string Operator { get; set; }
public string Field { get; set; }
public string Value { get; set; }
}
public class GridFilters
{
public List<GridFilter> Filters { get; set; }
public string Logic { get; set; }
}
public class GridSort
{
public string Field { get; set; }
public string Dir { get; set; }
}
}
Making changes to our Controller Action
We need to make changes to our existing Action on our Controller to support these new Grid objects that is being posted from our Grid when a user does a server side sort, filter, etc..
public class ProductsController : Controller
{
[HttpPost]
public JsonResult GetAll(int skip, int take, int page, int pageSize,
List<GridSort> sort = null, GridFilters filter = null)
{
var myDatabaseContext = new MyDatabaseContext();
var products = myDatabaseContext.Products.AsQueryable();
var totalCount = myDatabaseContext.Products.AsQueryable();
if (filter != null && (filter.Filters != null && filter.Filters.Count > 0))
{
string whereClause = null;
var parameters = new List<object>();
var filters = filter.Filters;
for (var i = 0; i < filters.Count; i++)
{
if (i == 0)
whereClause += string.Format(" {0}",
BuildWhereClause<Product>(i, filter.Logic, filters[i],
parameters));
else
whereClause += string.Format(" {0} {1}",
ToLinqOperator(filter.Logic),
BuildWhereClause<Product>(i, filter.Logic, filters[i],
parameters));
}
products = products.Where(whereClause, parameters.ToArray());
totalCount = products.Where(whereClause, parameters.ToArray());
}
if (sort != null && sort.Count > 0)
foreach (var s in sort)
{
s.Field = (s.Field == "ProductType") ? "ProductTypeId" : s.Field;
products = products.OrderBy(s.Field + " " + s.Dir);
}
products = products.Skip(skip).Take(take);
List<Product> productList = products.ToList();
var productViewModels = new List<ProductViewModel.Product>();
foreach (var p in productList)
{
productViewModels.Add(new ProductViewModel.Product
{
Completed = p.Completed.Date,
CompletedBy = p.CompletedBy,
Created = p.Created.Date,
CreatedBy = p.CreatedBy,
Name = p.Name,
ProductId = p.ProductId,
ProductType = p.ProductType.Name,
ProductDetails = p.ProductDetails,
Status = p.Status,
Updated = p.Updated.Date,
UpdatedBy = p.UpdatedBy
});
}
return Json(
new ProductViewModel
{
Products = productViewModels,
TotalCount = totalCount.Count()
});
}
public static string BuildWhereClause<T>(int index, string logic,
GridFilter filter, List<object> parameters)
{
var entityType = (typeof(T));
var property = entityType.GetProperty(filter.Field);
switch (filter.Operator.ToLower())
{
case "eq":
case "neq":
case "gte":
case "gt":
case "lte":
case "lt":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
parameters.Add(DateTime.Parse(filter.Value).Date);
return string.Format("EntityFunctions.TruncateTime({0}){1}@{2}",
filter.Field,
ToLinqOperator(filter.Operator),
index);
}
if (typeof(int).IsAssignableFrom(property.PropertyType))
{
parameters.Add(int.Parse(filter.Value));
return string.Format("{0}{1}@{2}",
filter.Field,
ToLinqOperator(filter.Operator),
index);
}
parameters.Add(filter.Value);
return string.Format("{0}{1}@{2}",
filter.Field,
ToLinqOperator(filter.Operator),
index);
case "startswith":
parameters.Add(filter.Value);
return string.Format("{0}.StartsWith(" + "@{1})",
filter.Field,
index);
case "endswith":
parameters.Add(filter.Value);
return string.Format("{0}.EndsWith(" + "@{1})",
filter.Field,
index);
case "contains":
parameters.Add(filter.Value);
return string.Format("{0}.Contains(" + "@{1})",
filter.Field,
index);
default:
throw new ArgumentException(
"This operator is not yet supported for this Grid",
filter.Operator);
}
}
public static string ToLinqOperator(string @operator)
{
switch (@operator.ToLower())
{
case "eq": return " == ";
case "neq": return " != ";
case "gte": return " >= ";
case "gt": return " > ";
case "lte": return " <= ";
case "lt": return " < ";
case "or": return " || ";
case "and": return " && ";
default: return null;
}
}
public JsonResult GetProductDetails(int skip, int take, int page,
int pageSize, string group)
{
var myDatabaseContext = new MyDatabaseContext();
var productDetails = myDatabaseContext.ProductDetails
.OrderBy(p => p.ProducDetailtId);
return Json(
new ProductDetailsViewModel
{
ProductDetails = productDetails.Skip(skip).Take(take),
TotalCount = productDetails.Count()
},
JsonRequestBehavior.AllowGet);
}
}
Note: Instead of downloading the LINQ Dynamic Query Library, you may want to actually download the sample application for this post because the DynamicQueryable.cs class from the Linq Dynamic Libray has been slightly modified to handle EntityFunctions to support string search actions from our Grid such as Contains, StartsWidth and EndsWith string searches.
A few quick notes in regards to our changes to our Action on our Controller to now support complete server side processing of paging, sorting and filtering.
- BuildWhereClause<T>(int index, string logic, GridFilter filter, List parameters) This helper method will build our our where clauses and predicates so tha we can chain them up and pass them into Dynamic LINQ.
- ToLinqOperator(string @operator) This helper method will convert operators that are sent from our Grid to C# operators that Dynamic LINQ will understand and convert them for us
-
Lines 48-64, here we are iterating through the results to trim off the timestamp off of any properties that are of type datetime, so that when we do any grouping or filtering from the grid the timestamp of these fields are ignored.
foreach (var p in productList) { productViewModels.Add(new ProductViewModel.Product { Completed = p.Completed.Date, CompletedBy = p.CompletedBy, Created = p.Created.Date, CreatedBy = p.CreatedBy, Name = p.Name, ProductId = p.ProductId, ProductType = p.ProductType.Name, ProductDetails = p.ProductDetails, Status = p.Status, Updated = p.Updated.Date, UpdatedBy = p.UpdatedBy }); } -
Lines 88-103, here we are checking against the type of the column (property) that we are searching against so that we can convert the search criteria to the appropriate type. Currently we are supporting searches against types of string, datetime and int. If you need to add more types simply enhance this section of the implementation.
if (typeof(DateTime).IsAssignableFrom(property.PropertyType)) { parameters.Add(DateTime.Parse(filter.Value).Date); return string.Format("EntityFunctions.TruncateTime({0}){1}@{2}", filter.Field, ToLinqOperator(filter.Operator), index); } if (typeof(int).IsAssignableFrom(property.PropertyType)) { parameters.Add(int.Parse(filter.Value)); return string.Format("{0}{1}@{2}", filter.Field, ToLinqOperator(filter.Operator), index); } parameters.Add(filter.Value); -
Lines 109-123, here we are just framing up the different queries for string searches from the Grid. The Grid supports StartsWith, Contains, and EndsWith.
case "startswith": parameters.Add(filter.Value); return string.Format("{0}.StartsWith(" + "@{1})", filter.Field, index); case "endswith": parameters.Add(filter.Value); return string.Format("{0}.EndsWith(" + "@{1})", filter.Field, index); case "contains": parameters.Add(filter.Value); return string.Format("{0}.Contains(" + "@{1})", filter.Field, index);As you can see in the screenshot right below these are the current string search capabilites that the Grid has.
Great, let’s run a few searches from the Grid now.
- Search on ProductId: 3
- Search on Created Date: 04/20/2013
- Search on Created Date >= 04/15/2012 and Name containing “sample product 3”
Voila! Our controller only returns 3 records which assert our test case and are all of Created date >= 04/20/2012 and all contain the string “sample product 3” in the name.
Update: 04/24/2012
Added recursion to the filters processing to support multiple search criterias on the same field while at the same time supporting searches across multiple fields.
[HttpPost]
public JsonResult GetAll(int skip, int take, int page, int pageSize,
List<GridSort> sort = null, GridFilter filter = null)
{
var myDatabaseContext = new MyDatabaseContext();
var products = myDatabaseContext.Products.AsQueryable();
var totalCount = myDatabaseContext.Products.AsQueryable();
if (filter != null && (filter.Filters != null && filter.Filters.Count > 0))
{
ProcessFilters(filter, ref products);
totalCount = products;
}
if (sort != null && sort.Count > 0)
foreach (var s in sort)
{
s.Field = (s.Field == "ProductType") ? "ProductTypeId" : s.Field;
products = products.OrderBy(s.Field + " " + s.Dir);
}
products = products.Skip(skip).Take(take);
List<Product> productList = products.ToList();
var productViewModels = new List<ProductViewModel.Product>();
foreach (var p in productList)
{
productViewModels.Add(new ProductViewModel.Product
{
Completed = p.Completed.Date,
CompletedBy = p.CompletedBy,
Created = p.Created.Date,
CreatedBy = p.CreatedBy,
Name = p.Name,
ProductId = p.ProductId,
ProductType = p.ProductType.Name,
ProductDetails = p.ProductDetails,
Status = p.Status,
Updated = p.Updated.Date,
UpdatedBy = p.UpdatedBy
});
}
return Json(
new ProductViewModel
{
Products = productViewModels,
TotalCount = totalCount.Count()
});
}
public static void ProcessFilters(GridFilter filter, ref IQueryable<Product> queryable)
{
var whereClause = string.Empty;
var filters = filter.Filters;
var parameters = new List<object>();
for (int i = 0; i < filters.Count; i++)
{
var f = filters[i];
if (f.Filters == null)
{
if (i == 0)
whereClause += BuildWhereClause<Product>(f, i, parameters) + " ";
if (i != 0)
whereClause += ToLinqOperator(filter.Logic) +
BuildWhereClause<Product>(f, i, parameters) + " ";
if (i == (filters.Count - 1))
{
CleanUp(ref whereClause);
queryable = queryable.Where(whereClause, parameters.ToArray());
}
}
else
ProcessFilters(f, ref queryable);
}
}
Looks like our server side paging, sorting and filteration is golden!
Update: 05/11/2012- Added support for querying objects with child properties
public static class GridHelper
{
public static void ProcessFilters<T>(GridFilter filter, ref IQueryable<T> queryable)
{
var whereClause = string.Empty;
var filters = filter.Filters;
var parameters = new List<object>();
for (int i = 0; i < filters.Count; i++)
{
var f = filters[i];
if (f.Filters == null)
{
if (i == 0)
whereClause += BuildWherePredicate<T>(f, i, parameters) + " ";
if (i != 0)
whereClause += ToLinqOperator(filter.Logic) + BuildWherePredicate<T>(f, i, parameters) + " ";
if (i == (filters.Count - 1))
{
TrimWherePredicate(ref whereClause);
queryable = queryable.Where(whereClause, parameters.ToArray());
}
}
else
ProcessFilters(f, ref queryable);
}
}
public static string TrimWherePredicate(ref string whereClause)
{
switch (whereClause.Trim().Substring(0, 2).ToLower())
{
case "&&":
whereClause = whereClause.Trim().Remove(0, 2);
break;
case "||":
whereClause = whereClause.Trim().Remove(0, 2);
break;
}
return whereClause;
}
public static string BuildWherePredicate<T>(GridFilter filter, int index, List<object> parameters)
{
var entityType = (typeof(T));
PropertyInfo property;
if(filter.Field.Contains("."))
property = GetNestedProp<T>(filter.Field);
else
property = entityType.GetProperty(filter.Field);
var parameterIndex = parameters.Count;
switch (filter.Operator.ToLower())
{
case "eq":
case "neq":
case "gte":
case "gt":
case "lte":
case "lt":
if (typeof(DateTime).IsAssignableFrom(property.PropertyType))
{
parameters.Add(DateTime.Parse(filter.Value).Date);
return string.Format("EntityFunctions.TruncateTime(" + filter.Field + ")" + ToLinqOperator(filter.Operator) + "@" + parameterIndex);
}
if (typeof(int).IsAssignableFrom(property.PropertyType))
{
parameters.Add(int.Parse(filter.Value));
return string.Format(filter.Field + ToLinqOperator(filter.Operator) + "@" + parameterIndex);
}
parameters.Add(filter.Value);
return string.Format(filter.Field + ToLinqOperator(filter.Operator) + "@" + parameterIndex);
case "startswith":
parameters.Add(filter.Value);
return filter.Field + ".StartsWith(" + "@" + parameterIndex + ")";
case "endswith":
parameters.Add(filter.Value);
return filter.Field + ".EndsWith(" + "@" + parameterIndex + ")";
case "contains":
parameters.Add(filter.Value);
return filter.Field + ".Contains(" + "@" + parameterIndex + ")";
default:
throw new ArgumentException("This operator is not yet supported for this Grid", filter.Operator);
}
}
public static string ToLinqOperator(string @operator)
{
switch (@operator.ToLower())
{
case "eq":
return " == ";
case "neq":
return " != ";
case "gte":
return " >= ";
case "gt":
return " > ";
case "lte":
return " <= ";
case "lt":
return " < ";
case "or":
return " || ";
case "and":
return " && ";
default:
return null;
}
}
public static PropertyInfo GetNestedProp<T>(String name)
{
PropertyInfo info = null;
var type = (typeof(T));
foreach(var prop in name.Split('.'))
{
info = type.GetProperty(prop);
type = info.PropertyType;
}
return info;
}
}
Happy Coding…! 🙂
Download sample application: https://skydrive.live.com/redir.aspx?cid=949a1c97c2a17906&resid=949A1C97C2A17906!465&parid=949A1C97C2A17906!361
https://drive.google.com/file/d/0B91gwLeUpEWBU2tnN0dNeVhSTzA/view?usp=sharing
Hi just wanted to give you a quick heads up and let you know a few
of the images aren’t loading correctly. I’m not sure why
but I think its a linking issue. I’ve tried it in two different web browsers and both show the same results.
LikeLike
insightful but as many others have said, the sort and filter is always null.
LikeLike
Hi, I am calling the WCF service from the jQuery KendoGrid method. I have a trouble is passing sort and filter as the parameters. It would be great if you can guide me to solve the issue.
LikeLike
I don’t have an example with WCF, however the Kendo team does and they are using WCF data services with OData. I would highly recommend you you pure ASP.NET REST with OData for this.
LikeLike
It’s great tutorial…
I’m finding to have a dynamic column with kendo grid. how can we archive this?
LikeLike
Hi Lee,
I’m appreciate blogger hard work, for publishing awesome article.
This solution work 10 times faster than which Kendo provided by default, But I’m missing aggregate concept in your solution.
Have you done the same to .net MVC application, if so please send me the link where i can get it.
Thank You
LikeLike
Hi,
Could you please provide the latest code sample, link is not working.
Thanks.
LikeLike
You can get the edge releases here: https://genericunitofworkandrepositories.codeplex.com/SourceControl/latest#main/
LikeLike
Hello Le,
Could you please upload the sample again as the link is not working.
LikeLike
Good article, and once again I am still surprised about the lack of Kendo UI examples. The GridHelper makes sense, however, I have configured my grid with OData, and it seems that all filtering works fine without the need of the additional helper code.
I am, however, unable to find how to display navigation property data (hierarchical data that is part of the main dataset) as row details without making a second data call. Any examples on how to accomplish this?
LikeLike
Hi B, thanks for the positive feedback, are you following this post? If you are then you just need to you the .Include so that your detail row data is included in the query and results so you can avoid making the second call.
LikeLike
I think the admin of this site is genuinely working hard for his site,
because here every material is quality based material.
LikeLike
Thanks for the positive feedback jackpot.
LikeLike
Hello Le,
actually this filtering method is pretty clean, but i think it has a problem with bool data types.
I tried to filter boolean field but i got the following exception in CheckAndPromoteOperands() method
[Operator ‘==’ incompatible with operand types ‘Boolean?’ and]
!!!!!!
i did some tracing then i add the following line to to BuildWherePredicate()
if (typeof(bool?).IsAssignableFrom(property.PropertyType))
{
parameters.Add(bool.Parse(filter.Value));
return string.Format(filter.Field + ToLinqOperator(filter.Operator) + “@” + parameterIndex);
}
and it’s working now like charm .
what do you think about this Le ?
LikeLike
hi besherh,
I think it looks fine, just as fyi, I am working on a blog post right now that eliminates all of this code for filtering with Kendo UI Grid and Entity Framework, should have this completed by tomorrow. Will update you when I have it published.
LikeLike
besherh,
Here is the post on Kendo UI Grid with filtering using OData, this is recommended over the approach on this post.
http://blog.longle.net/2013/06/18/mvc-4-web-api-odata-entity-framework-kendo-ui-grid-datasource-with-mvvm/
LikeLike
Can you some one give me the database??
LikeLike
Please use Entity Framework Migrations and this will automatically setup the database for you.
LikeLike
Pretty nice post. I just stumbled upon your weblog and wanted to say that I
have truly enjoyed surfing around your blog posts.
In any case I will be subscribing to your feed and I hope you
write again soon!
LikeLike
thanks nose job for stopping by and for the positive feedback!
LikeLike
can dynamic linq be setup to perform a contains on an interger or decimal column?
LikeLike
Pingback: SortDescription values null using Kendo UI and ASP.NET MVC3 | Mark Tinderholt 'as a Service'
Hi Le, I ran into this other problem and I was wondering if you experienced similar issues. Looking at fiddler, when I unsort or ungroup, kendo grid sends the same header parameters when I sort or group. For example, If I group by first name and ungroup, the graphical grouping effect is removed, but the grid is still grouped by the first name. Same thing happens with sorting. What I have figured out so far is that once the list of sort or group is sent, they are being overwritten by the next list. But when there is a difference in length, it causes problem. For example, there is a sort, sort = {a,b,c}. If you do another sort, sort = {d,e}, you end up with {d,e,c}. So what I think is happening is , when I ungroup which give me empty sort = {}, previous sort that has greater length just takes over the new empty sort. Do you know what might be causing this?
LikeLike
That worked! Thank you so much for your help.
LikeLike
your welcome, thanks for stopping by… 🙂
LikeLike
I set breaks points in the parameterMap, only time it breaks is when I return options. Do you have any idea what might cause this?
LikeLike
and columns array is done. but i just commented out to hide the details.
LikeLike
Please configure your transport as follows:
transport: { read: { url: myUrl, dataType: "json", type: "POST", contentType: "application/json; charset=utf-8", data: {} }, parameterMap: function (options) { return kendo.stringify(options); } }Also decorate your Action with HttpPost attribute
[HttpPost] public ContentResult getResults(Results model, int skip, int take, int pageSize, List<GridSort> sort, List<GridSort> group) {}LikeLike
Both kendo.stringify(options) and json.stringify(options) give me an empty grid.
It looks like it doesn’t even get to the break point in the action method.
parameterMap: function (options) { //return kendo.stringify(options); //return options; return JSON.stringify(options) }returning options gives me a grid with result set, but sorting doesn’t work.
LikeLike
I was playing around with the parameter map, I wasn’t able to make the grid display anything with the JSON.stringify()
LikeLike
Jin,
Please try this, use
parameterMap: function (options) { return kendo.stringify(options); }for the paramterMap function. Also I’m not sure why you are creating an object before returning the options, also try just passing back the options object back as it is. Last but not least could you go ahead and complete the columns array configuration for the Grid as well?
LikeLike
client side code
<script type="text/javascript"> var myurl = '@ViewData["URL"]'; myurl = myurl.replace(/amp;/g, ""); $(document).ready(function () { $("#resultgrid").kendoGrid({ pageable: true, groupable: true, resizable: true, scrollable: false, reorderable: true, dataSource: { serverPaging: true, serverSorting: true, transport: { read: { url: myurl, dataType: "json" }, parameterMap: function (options) { var object = {}; object.model = options.model; object.page = options.page; object.size = options.size; object.skip = options.skip; object.take = options.take; object.sort = options.sort; return options; } }, schema: {data: "rs",total:"tc"}, pageSize: 100 }, sortable: { mode: "multiple", allowUnsort: true }, detailTemplate: kendo.template($("#template").html()), detailInit: detailInit, columns: [ // column name ] }); function detailInit(e) { var detailRow = e.detailRow; detailRow.find(".orders").kendoGrid(); } $("#comboBox").kendoDropDownList({ dataTextField: "text", dataValueField: "value", dataSource: [ { text: 100, value: 100 }, { text: 200, value: 200 }, { text: 500, value: 500 } ], change: function (e) { var grid = $("#resultgrid").data("kendoGrid"); grid.dataSource.pageSize(parseInt(this.value(), 10)); } }); });signature of action code
model for the grid parameter
public class GridSort { public string field { get; set; } public string dir { get; set; } }LikeLike
Thanks for the reply. I still have trouble making it work. What confuses me is that I can get the page, pageSize, and skip without any problem. Then, my guess is that it is the type of the variable sort that is making it different from those variables. I can see the sort object is retrieved as parameter, but I am not sure if it is because I have the matching name or because I have the right type. If it is the problem with the mvc parameter mapping, where should I look to fix this?
LikeLike
Jin,
Please post your client side code and the signature of your action from your controller, along with the actual C# model for the Grid parameters.
LikeLike
and sort is list of as you defined.
LikeLike
Hi Jin,
Typically when you are able to see that the View is successfully posting back to your action a hydrated payload (in your case your List of GridSort), however it’s getting lost somewhere after your action is hit, are typically due to Mvc parameter mapping and/or serialization or deserialization of your JSON payload(s) from your View to your Action.
First off I would definitely get your Kendo Grid working with the default contract (out of the box), which I believe is:
namespace MvcApplication3.Models { public class GridFilter { public string Operator { get; set; } public string Field { get; set; } public string Value { get; set; } } public class GridFilters { public List<GridFilter> Filters { get; set; } public string Logic { get; set; } } public class GridSort { public string Field { get; set; } public string Dir { get; set; } } }Also make sure that your action parameter matches this exactly, that way there is absolutely not guess work for the MVC runtime to do when those parameters are passed from your View to your Action.
public class ProductsController : Controller { [HttpPost] public JsonResult GetAll(int skip, int take, int page, int pageSize, List<GridSort> sort = null, GridFilters filter = null) { // your code goes here, make sure that are of your parameters are all there. } }Let me know if this helps.
LikeLike
I could replicate the server-side paging, but sorting wouldn’t work.
I have call a method from the view
public ContentResult getResults(Results model, int skip, int take, int pageSize, List sort, List group)
However sort[0] has null dir and null field. when
I can see the Count is 1.
I can see that view page is sending, field: processDate dir: acs through fiddler.
Do you have any idea what might be causing this?
LikeLike
Curious, have you considered using the “odata” dataType? The format on the wire is a little more bulky, but it eliminates almost *all* of your EF code on the back end. Just produce an IQueryable and ASP.NET does the rest.
Here’s how to do it in ASP.NET MVC3: http://mattfrear.com/2011/01/25/playing-with-odata/
And how to do it in ASP.NET MVC4: http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api
LikeLike
Hi Nicholas,
I actually started out with an example using oData however at the time of the blog post there were some major changes with the WebApi making it’s transition from WCF to ASP.NET MVC. It was somewhat hard for me to get documentation and even when I was able to there were namespaces amoungst other things that were moving around within the library. Hind sight, I’m working on a book project with Telerik’s Kendo UI Web Controls and now that WebApi has been released, I’m favoring oData for the book.
LikeLike
Thanks for great Post!!!!…I m trying to do serverside paging using skip and top parameters set on my API. But i dont want to use Kendo UI for it. i just want to do simple paging. Can you please guide me how can i do that.
thanks
LikeLike
Pingback: 20 JavaScript Frameworks: MVVM Templating, Licenses etc. | FariDesign.net
I might figure it out in time (walked back through the blog posts..) but where do the databases used in this example come from?
Thanks!
James
LikeLike
James,
If you download the sample application using the link in the post, the project is using EF4’s Code-First approach. I have a class MvcApplication3.Entities.DropCreateDatabaseTables.cs that will automatically create the db for you once the app has started and hydrate your db with some mock data for you to get started.
DropCreateDatabaseTables.cs Class:
namespace MvcApplication3.Entities { //public class DropCreateDatabaseTables : DropCreateDatabaseAlways public class DropCreateDatabaseTables : DropCreateDatabaseIfModelChanges { protected override void Seed(MyDatabaseContext context) { var requestTypes = new List(); var electronicType = new ProductType {Name = "Electronics", ProductTypeId = 2}; requestTypes.Add(electronicType); var machineryType = new ProductType {Name = "Machinery", ProductTypeId = 1}; requestTypes.Add(machineryType); var productDetails = new List { new ProductDetail {Key = "Height", Value = "5"}, new ProductDetail {Key = "Length", Value = "10"} }; var requests = new List { new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "AT&T Titan", ProductId = 1, Status = "New", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType, ProductDetails = productDetails }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "AT&T Titan II", ProductId = 2, Status = "InProgress", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = machineryType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Nokia Lumnia 800", ProductId = 3, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Nokia Lumnia 900", ProductId = 4, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "HTC Sensation", ProductId = 5, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Apple IPad 2", ProductId = 6, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Apple IPad 3", ProductId = 7, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Apple iPhone 3", ProductId = 8, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "Apple iPhone 4", ProductId = 9, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType }, new Product { Created = DateTime.Now, Completed = DateTime.Now.AddDays(7), CompletedBy = Guid.NewGuid(), CreatedBy = Guid.NewGuid(), Name = "BlackBerry 4.0", ProductId = 10, Status = "Completed", Updated = DateTime.Now.AddDays(7), UpdatedBy = Guid.NewGuid(), XmlContent = string.Empty, ProductType = electronicType } }; requests.ForEach(r => context.Products.Add(r)); requestTypes.ForEach(r => context.ProductTypes.Add(r)); base.Seed(context); } } }If you look in the Global.asax.cs and in the Application_Start() method you will see where this is registered.
public class MvcApplication : System.Web.HttpApplication { public static void RegisterGlobalFilters(GlobalFilterCollection filters) { filters.Add(new HandleErrorAttribute()); } public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.MapRoute( "GetAll", "Products/GetAll/{page}/{count}", new { controller = "Products", action = "GetAll", page = UrlParameter.Optional, count = UrlParameter.Optional }); routes.MapRoute( "Default", // Route name "{controller}/{action}/{id}", // URL with parameters new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults ); } protected void Application_Start() { Database.SetInitializer(new DropCreateDatabaseTables()); AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); } }LikeLike
Thanks for this, it has really helped me. I’m working on adapting it a bit to streamline the process. For one, I made everything generic and converted the code into a couple of extension methods. This way you can do:
var queryable = GetQueryable();
var results = query.Filter(filter).Sort(sort).Skip(skip).Take(take).ToArray();
I’m working now on two bugs/improvements. The first is that it doesn’t seem to query boolean types correctly. The second involves querying properties of child objects. For example, I have a Person object, which contains a Profile object and I want to filter on a property on the Profile object. I’m working so that I can pass in the field name of “Profile.FavoriteColor” and have it build the required code.
If I get these figured out, I’ll let you know and send you a link.
LikeLike
citezein, thanks for the positive feedback, here you go: Update: 05/11/2012 – Added support for querying objects with child properties
LikeLike
Hi,
Thanks for this great blog entry !
I’d like to know a bit more about the way you pass parameters into the Controller. I’ve replicated your code in an MVC 4 WebAPI controller, and I can indeed grab the GridFilters, but this seems to work only for POST.
I have tried to modify the code to get it to work for GET, but apparently the WebAPI model binder is expecting something different.
Would you have any insight on how I could pass those filter parameters to a GET Web API controller action ?
Thanks a lot
LikeLike
Hi Luc,
Thanks for the feedback. I’ve only brushed up on the WebApi breifly a few months back when there was quite a bit of reorganizing of some of the name spaces and classes in the library. I found this out when trying to initially setup the Kendo Grid to use OData. So with some of these road blocks in the way I switched to implementing the grid with JSON instead. As of now I wouldn’t have any insight on how to wire this up using GET’s with the WebApi library.
LikeLike
I have also ran into the same issue, post is fine but not the GET. That is actually what drew me here to look for a solution 🙂
Did you ever figure out a solution?
LikeLike
The grid filter param does not get serialized correctly when filters are applied on multiple fields.
1) Filter on 1 field
Params from the grid for filter:
{“filters”:
[{“field”:”ProductType”,”operator”:”eq”,”value”:”text1″},
{“field”:”ProductType”,”operator”:”eq”,”value”:”text2″}],
“logic”:”and”}
This is self explanatory.
2) Filter on 2 fields
Params from the grid for filter:
{“filters”:
[ {“field”:”ProductType”,”operator”:”eq”,”value”:”text1″},
{“field”:”ProductType”,”operator”:”eq”,”value”:”text2″},
{“filters”:
[{“field”:”Name”,”operator”:”eq”,”value”:”text3″},
{“field”:”Name”,”operator”:”eq”,”value”:”text4″}],
“logic”:”and”}],
“logic”:”and”}
I’m all confused with this. I don’t know why grid generates such a different sets of filter params when filters are applied to multiple fields. May be by design or a bug.
So with that being said – I found that the filter params does not get serialized to GridFilters object on server side correctly when filters are applied to multiple fields.
I tried making a slight change in GridFilters object. It correctly bound the params to GridFilters object.
public class GridFilters
{
public List Filters { get; set; }
public string Logic { get; set; }
}
public class GridFilter
{
public string Field { get; set; }
public string Operator { get; set; }
public string Value { get; set; }
public List Filters { get; set; }
public string Logic { get; set; }
}
It’s still not that trivial to iterate through the filters to correctly create the query. Can you give some information on how filters will work on multiple fields?
Ujjwal Karjee
LikeLike
Ujjwal,
I’ve added recursion to the filter processing to support multiple search criterias on a column and at the same time supporting searches across multiple fields. I’ve re-uploaded the solution and updated the download link.
LikeLike
great! it was exactly what i’m looking for … hope to see a github or codeplex repository for this mvc-kendo integration ..
LikeLike
Thanks for feedback Poretti and great idea, I’ll try to get together a full MVC project using all Kendo UI controls if time permits.
LikeLike
Pingback: Telerik’s HTML5 Kendo UI (Grid, Detail Template, TabStrip) with MVC3 and JSON « Long Le's Blog