Telerik blogs

LINQ is a brilliant thing is it not? I can still recall the days of old when "best practices" said that all of the database access went into stored procedures. This resulted in a database with procedure names like usp_getProductByIdAndNameWhereEmployeeIsNotNull. I think that the only people who really enjoyed this structure were DBA's.

SQL is a very powerful and flexible language. LINQ (and Entity Framework specifically) does a fairly good job of replicating its functionality. However, LINQ doesn't really deal very well with parameterized queries. It does to a point, but then pretty major problems start to arise. Let me show you what I mean.

Consider a grid with paging requirements. The user is controlling what "page" of records they are on. They may even be controlling how many records they want to see per page. LINQ implements this as take and skip. Each page is determined by how many records need to be viewed, offset by how many records from the front of the set the user wants to start viewing. This is simple to do using LINQ. The following is demonstrated using an ASP.NET MVC Controller method and an EF Model.

Paging With Take And Skip

readonly NorthwindEntities _context = new NorthwindEntities();

[HttpPost]
public ActionResult Get(int take, int skip) {
    var employees = _context.Employees.OrderBy(e => e.Employee_ID)
      .Select(e => new Models.Employee {
        EmployeeID = e.Employee_ID,
        FirstName = e.First_Name,
        LastName = e.Last_Name,
        Title = e.Title,
        BirthDate = e.Birth_Date
    }).Skip(skip).Take(take);

    return Json(employees);
}

I selected the results into a UDO called "Models.Product" because EF objects cannot be serialized due to circular dependencies.

If the user wanted to view page 3 of the Products dataset, they only have to specify that they want to skip the first 20 records, and then view the next 10. A Kendo UI Grid implementation which sends requests with paging parameters might look like this:

Get Page 3 Of Data

$("#grid").kendoGrid({
  dataSource: {
    transport: {
      read: {
        url: "Employees/Get",
        type: "POST"
    },
    pageSize: 10,
    serverPaging: true
  },
  pageable: true
});

I'm using a POST since MVC blocks unsecured GETS by default. That's good since we're going to be needing a POST request structure coming up shortly.

All is well, good and quite simple. Now lets imagine that the user wants not only to view page 3 of data, but also to sort it by some arbitrary field in any one of three directions: ascending, descending or none. LINQ does not provide a way for us to change a sort dynamically. This is because a sort in the database is an "ORDER BY". An "ORDER BY" in LINQ does not take in a string parameter, but rather a function which returns the strongly typed EF model property on which to sort. The direction is a keyword.

What developers sometimes end up doing is using switch statements and some rather creative LINQ gymnastics or rather verbose expression trees. To make this easier for you when using Kendo UI, we have open sourced a project called Dynamic LINQ Helpers.

Say Hello To The Dynamic LINQ Helpers

These helpers add an extension method to IQueryable which will do paging, sorting and filtering for you; no LINQ Pommel horse required. Lets take a look at how you can use this open source extension with your Kendo UI Projects today.

With MVC

First add the Kendo.DynamicLinq Nuget package to your project. Let's use the above Products example. We can now alter the controller method to receive take, skip, sort and filter parameters. It's ok if you aren't going to use them all.

EmployeesController.cs

using Kendo.DynamicLinq;

public class EmployeesController : Controller {

  [HttpPost]
  public ActionResult Get(int take, int skip, IEnumerable<Kendo.DynamicLinq.Sort> sort, Kendo.DynamicLinq.Filter filter) {
    var employees = _context.Employees.OrderBy(e => e.Employee_ID)
      .OrderBy(e => e.Employee_ID)
      .Select(e => new Models.Employee {
        EmployeeID = e.Employee_ID,
        FirstName = e.First_Name,
        LastName = e.Last_Name,
        Title = e.Title,
        BirthDate = e.Birth_Date
    });

    return Json(employees.ToDataSourceResult(take, skip, sort, filter));
  }
}

You can see that you simply call the ToDataSourceResult method on the IQueryable that is returned from the LINQ query. A few items of note...

  • You have to include the Kendo.DynamicLinq 'using' statement in order to get the ToDataSourceResult extension method
  • You must call OrderBy on the LINQ query prior to calling the ToDataSourceResult extension method. EF requires this.

This returns a DataSourceResult, but what does a DataSourceResult look like? As a simple JavaScript object, it looks like this:

DataSourceResult

{
  "Data": [ 
                      { EmployeeID: 1, FirstName: "Burke", LastName: "Holland", Title: "Developer Advocate", BirthDate: "UNKNOWN" }, 
                      { EmployeeID: 2, FirstName: "Brandon", LastName: "Satrom", Title: "Director Of PM", BirthDate: "1962-2-28" }
                    ]
  "Total": 20 // the total represents the total number of records in the db, not just in this set
}

We now need to configure the Kendo UI Grid to send all data operations to the server. This grid definition sends paging, sorting and filtering operations to the server as parameters in the request.

Index.cshtml

$("#grid").kendoGrid({
  dataSource: {
    transport: {
      read: {
        url: "Employees/Get",
        contentType: "application/json",
        type: "POST"
      },
      parameterMap: function (options) {
        return kendo.stringify(options);
      }
    },
    schema: {
      data: "Data",
      total: "Total",
    },
    pageSize: 10,
    serverPaging: true,
    serverFiltering: true,
    serverSorting: true
  },
  filterable: true,
  sortable: true,
  pageable: true,
  columns: [
    { field: "FirstName", title: "First Name" },
    { field: "LastName", title: "Last Name" },
    { field: "Title", title: "Title" },
    { field: "BirthDate", title: "Birth Date", format: "{0:MM/dd/yyyy}" }
  ]
});

Aside from your rather standard grid configuration, there are some important nuances here to be aware of.

Serialize Parameters To JSON

The parameterMap function serializes all of the parameters to JSON before sending them over. This is because filter and sort are complex objects and need to be serialized as something before they can be transfered across the wire. Otherwise, the MVC Controller method won't be able to automatically map them to their appropriate matching parameters.

Set Request Content Type

Since we have set the parameters to a JSON object, we need to tell the HTTP request that we doing so. This means that we need to set the contentType to application/json. This will send the the serialized JSON in the request payload as an object. If you don't do this, the entire JSON string will be sent as a parameter key in the request and all of the parameters will be null in the controller method.

Configure The Schema

The Kendo UI Grid must have the "schema" of the DataSourceResult set based on the structure of the DataSourceResult object. This tells Kendo UI which JSON property contains the array of repeating data (Data), and which one contains a total count (Total) of all the records. This total count is required for paging.

Ignoring Paging Parameters

Since the ToDataSourceResult extension method requires you to pass take and skip parameters, you can simply pass any value less than or equal to 0 as the take parameter. Since you obviously don't want to return 0 results, this simply ignores the paging altogether. The take and skip parameters will be automatically serialized as 0 if you turn off paging on the grid.

Using An Object Instead

You can of course move all of these parameters into a model object and just reuse that in your project.

DataSourceRequest.cs

 public class DataSourceRequest {
  public int Take { get; set; }
  public int Skip { get; set; }
  public IEnumerable<Kendo.DynamicLinq.Sort> Sort { get; set; }
  public Kendo.DynamicLinq.Filter Filter { get; set; }
}

EmployeesConroller.cs

using Kendo.DynamicLinq;

public class EmployeesController : Controller {

  [HttpPost]
  public ActionResult Get(Models.DataSourceRequest request) {
    var employees = _context.Employees.OrderBy(e => e.Employee_ID)
      .OrderBy(e => e.Employee_ID)
      .Select(e => new Models.Employee {
        EmployeeID = e.Employee_ID,
        FirstName = e.First_Name,
        LastName = e.Last_Name,
        Title = e.Title,
        BirthDate = e.Birth_Date
    });

    return Json(employees.ToDataSourceResult(request.Take, request.Skip, request.Sort, request.Filter));
  }
}
 

With WebAPI

This works pretty much the exact same way inside of a WebAPI controller with the exception of the fact that you don't serialize the return result to JSON. WebAPI will do that for you.

EmployeesController.cs

[HttpPost]
public DataSourceResult Post(Models.DataSourceRequest request) {
  var products = _repository.Get();
  return products.ToDataSourceResult(request.Take, request.Skip, request.Sort, request.Filter);
}

Open Source

We hope that you find these Dynamic LINQ Extensions helpful with your Kendo UI projects. Since all widgets use the DataSource, any filtering or sorting will work with other widgets as well, and of course with the DataSource API.

Feel free to fork the project and see how we implemented the dynamic functionality. Feel free to copy it and use it however you like. For more information, demos and resources, check the following links which are on the GitHub Repo README page as well.


Burke Holland is the Director of Developer Relations at Telerik
About the Author

Burke Holland

Burke Holland is a web developer living in Nashville, TN and was the Director of Developer Relations at Progress. He enjoys working with and meeting developers who are building mobile apps with jQuery / HTML5 and loves to hack on social API's. Burke worked for Progress as a Developer Advocate focusing on Kendo UI.

Comments

Comments are disabled in preview mode.