jQuery Datatable with MVC 5 and Entity Framework

ajax asp.net-mvc datatables entity-framework linq

Question

To use server-side processing with my jQuery datatables, I need advice on what to include in my controller. I'm using Entity Framework and MVC 5.

According to the example at: http://datatablesmvc.codeplex.com/documentation,

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 should I do while utilizing LINQ in this way?

 public ActionResult Index()
         {
           var activity = db.Activity.Include(a => a.ActivityType);
              return View(activity.ToList());
          }
1
8
4/21/2014 9:58:53 PM

Accepted Answer

———————————————- updated response ———————————————-

Why did you update?

Users of SO seem to keep coming back to this answer frequently, so I though everyone could use a "small" update.

What has so far changed?

DataTables.Mvc began more than a year ago. It has changed and is now known asDataTables.AspNet However, it is not all.

The goal then was to assist with the fundamental subjects. The issue is that you would just receive a zip and would need to manually integrate everything into your project. Additionally, there was no model binder, and integration was extremely tedious.

Now that Nuget packages are available, our architecture is modular. You may use a reference.Core You can either prepare and implement everything yourself or purchase the necessary packages (Mvc5 or AspNet ; WebApi2 is soon coming), one-line registration, native model binders, and a comprehensive test suite.

How do you begin?

Take a looksamples a file ondev branch (please click). Don't forget to get the necessary Nuget packages. There is a list of them here: here.

———————————————- original response ———————————————-

Priorities come first

DataTables 1.9, 1.10 with the old API, or 1.10 with the new API are all options.

You will occasionally miss some plugins if you pick the new API (1.10 only), however you can use GitHub's DataTables.AspNet to assist with the bindings.

If not, you can look at the code and modify it to correspond to request variables from earlier versions (support will be provided later on my project).

The genuine thing

The point is that you must manage three things:

  1. global search and filter
  2. column search/filter
  3. Table sort

I'll write some code!

Depending on the version and if you're using my binding class or not, that could change. Please assume that you are utilizing it in order to avoid handling request parameters at this time.

You can experiment with something similar to 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);
}
19
9/3/2015 1:15:01 PM

Popular Answer

Utilize zzz-5 zzz

Describe EFDatatable.

Jquery Datatable's helper EFDatatable offers an extension to retrieve data from the Entity Framework context in a generic manner. With HTML helper, many datatable.js functionalities are usable. It offers client- or server-side options. Additionally: Documentation on wiki

@(Html.EF().Datatable<Person>()
        .Name("PersonGrid")
        .Columns(cols =>
        {
            cols.Field(a => a.Id).Visible(false);
            cols.Field(a => a.Name).Title("First Name").Class("text-danger");
            cols.Field(a => a.Age).Title("Age").Searchable(false);
            cols.Field(a => a.BirthDate).Title("Birth Date").Format("DD-MMM-Y");
            cols.Command(a => a.Id, "onClick", text: "Click").Title("");
        })
        .Filters(filter =>
        {
            filter.Add(a => a.Id).GreaterThanOrEqual(1);
        })
        .URL(Url.Action("GetDataResult"), "POST")
        .ServerSide(true)
        .Render()
)

The "ToDataResult(request)" extension function makes it very easy to fetch data using server side pagination.

public JsonResult GetDataResult(DataRequest request)
    {
        DataResult result = context.People.ToDataResult(request);
        return Json(result);
    }

Where do I find it?

From the package management terminal, install EFDatatable:

PM> Install-Package EFDatatable

Then include Javascript, CSS, and datatables.net links in your project.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow