jQuery Datatable with MVC 5 and Entity Framework

Question

I need some guidance on what to put in my controller so that I can use server-side processing with my jQuery datatables. I am using MVC 5 and Entity Framework.

The example at: http://datatablesmvc.codeplex.com/documentation states the following:

public class HomeController : Controller {
[HttpPost]
 public ActionResult GetDataTables(DataTable dataTable) {
  List<List<string>> table = new List<List<string>>();
  //Do something with dataTable and fill table    
  return new DataTableResult(dataTable, table.Count, table.Count, table);
   }
 }

But what do I do when I am using LINQ such as this?

 public ActionResult Index()
         {
           var activity = db.Activity.Include(a => a.ActivityType);
              return View(activity.ToList());
          }

Answer 1

------------------------------- Updated answer -------------------------------

Why the update?

This answer seems to keep getting much attention from SO users and I thought everyone could benefit from a "little" update.

What's changed so far?

DataTables.Mvc started over an year ago. It has changed and now it's called DataTables.AspNet. But that's not all.

At that time, aim was to help with base classes. Problem is that you'd just get a zip and should manually merge all that into your project. Also, there was no binder for models and integration was really boring.

Now we have a modular architecture with Nuget packages to help. You can either reference Core package and implement everything yourself or you can get apropriate packages (Mvc5 or AspNet; WebApi2 is comming soon) with native model binders, one-line registration and a full test suite.

How to get started?

Check out samples folder on dev branch (click here). Don't forget to get appropriate Nuget packages. You can find a list of them here.

------------------------------- Original answer -------------------------------

First things first

You can either use DataTables 1.9, 1.10 with old API or 1.10 with the new API.

If you choose the new API (1.10 only) than you'll miss some plugins here and there but you can use DataTables.AspNet on GitHub to help with the bindings.

If not, you can take a look and change the code to match request variables from other versions (support will be provided later on my project).

The real-deal

Point is that you'll have to handle three items:

1. Global filter/search
2. Column filter/search
3. Column sort

Gimme some code!

That might change from which version and if you're using (or not) my binding class. Consider that you're using it, for the sake of avoiding handling request parameters here, ok?

So, you can play with something like this:

[HttpPost]
public ActionResult Index([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestParameters)
{
    var totalCount = myDbContext.Set<Something>().Count();
    var filteredDataSet = myDbContext.Set<Something>().Where(_s => _s.ToLower().Contains(requestParameters.Search.Value));

    foreach(var column in requestParameters.Columns.GetFilteredColumns())
    {
        // Apply individual filters to each column.
        // You can try Dynamic Linq to help here or you can use if statements.

        // DynamicLinq will be slower but code will be cleaner.
    }

    var isSorted = false;
    IOrderedEnumerable<Something> ordered = null;
    foreach(var column in requestParameters.Columns.GetSortedColumns())
    {
        // If you choose to use Dynamic Linq, you can apply all sorting at once.
        // If not, you have to apply each sort manually, as follows.

        if (!isSorted)
        {
            // Apply first sort.
            if (column.SortDirection == Column.SortDirection.Ascendant)
                ordered.OrderBy(...);
            else
                ordered.OrderByDescending(...);

            isSorted = true;
        }
        else
        {
            if (column.SortDirection == Column.SortDirection.Ascendant)
                ordered.ThanBy(...);
            else
                ordered.ThanByDescending(...);
        }
    }

    var pagedData = ordered.Skip(requestParameters.Start).Take(requestParameters.Length);

    var dataTablesResult = new DataTablesResult(
        requestParameters.Draw,
        pagedData,
        filteredDataSet.Count(),
        totalCount
    );

    return View(dataTablesResult);
}