Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

pingcrosby · February 2017

I have seen people asking a few times how to use datatables serverside with MVC and EF with paging sorting and searching etc. I had some code lying around so I thought its time to give back and share a simple implementation.

Unfortunately I don't have time right now to create a sample solution for download so I just hacked out the code and pasted it in here as a guide.

For the dynamic searching (where clause) you will need linqkit for the predicate building.

First map the datatable inbound JSON requests to classes

Start - JSon class sent from Datatables

public class DataTableAjaxPostModel
{
    // properties are not capital due to json mapping
    public int draw { get; set; }
    public int start { get; set; }
    public int length { get; set; }
    public List<Column> columns { get; set; }
    public Search search { get; set; }
    public List<Order> order { get; set; }
}

public class Column
{
    public string data { get; set; }
    public string name { get; set; }
    public bool searchable { get; set; }
    public bool orderable { get; set; }
    public Search search { get; set; }
}

public class Search
{
    public string value { get; set; }
    public string regex { get; set; }
}

public class Order
{
    public int column { get; set; }
    public string dir { get; set; }
}
/// End- JSon class sent from Datatables

Next implement your action in a standard controller (note in this example we are not using a Web-API controller)

This method just grabs the data sent from the table and calls YourCustomSearchFunc() before returning a formatted json obj for Datatables to consume.

public JsonResult CustomServerSideSearchAction(DataTableAjaxPostModel model)
{
    // action inside a standard controller
    int filteredResultsCount;
    int totalResultsCount;
    var res = YourCustomSearchFunc(model, out filteredResultsCount, out totalResultsCount);

    var result = new List<YourCustomSearchClass>(res.Count);
    foreach (var s in res)
    {
        // simple remapping adding extra info to found dataset
        result.Add(new YourCustomSearchClass
        {
            EmployerId = User.ClaimsUserId(),
            Id = s.Id,
            Pin = s.Pin,
            Firstname = s.Firstname,
            Lastname = s.Lastname,
            RegistrationStatusId = DoSomethingToGetIt(s.Id),
            Address3 = s.Address3,
            Address4 = s.Address4
        });
    };

    return Json(new
    {
        // this is what datatables wants sending back
        draw = model.draw,
        recordsTotal = totalResultsCount,
        recordsFiltered = filteredResultsCount,
        data = result
    });
}

YourCustomSearchFunc() is very simple it just sets up the sort column and sort direction before calling the database search functionality. In this example we are only allowing sorting on a single column but you could easily implement multi column sorting.

public IList<YourCustomSearchClass> YourCustomSearchFunc(DataTableAjaxPostModel model, out int filteredResultsCount, out int totalResultsCount)
{
    var searchBy = (model.search != null) ? model.search.value : null;
    var take = model.length;
    var skip = model.start;

    string sortBy = "";
    bool sortDir = true;

    if (model.order != null)
    {
        // in this example we just default sort on the 1st column 
        sortBy = model.columns[model.order[0].column].data;
        sortDir = model.order[0].dir.ToLower() == "asc";
    }

    // search the dbase taking into consideration table sorting and paging
    var result = GetDataFromDbase(searchBy, take, skip, sortBy, sortDir, out filteredResultsCount, out totalResultsCount);
    if (result == null)
    {
        // empty collection...
        return new List<YourCustomSearchClass>();
    }
    return result;
}

This is the main meat of the functionality. In it we simply select from the dbase but instead of using a fixed where clause we use a dynamic expression built using the wonderful LinqKit to generate the predicate.

Additionally we use Take and Skip to allow us to page through the data. Notice we use the where clause twice. Once to select the data and pick a page, the second time to count how many items we could have returned.

public List<YourCustomSearchClass> GetDataFromDbase(string searchBy, int take, int skip, string sortBy, bool sortDir, out int filteredResultsCount, out int totalResultsCount)
{
    // the example datatable used is not supporting multi column ordering 
    // so we only need get the column order from the first column passed to us.         
    var whereClause = BuildDynamicWhereClause(Db, searchBy);

    if (String.IsNullOrEmpty(searchBy))
    {
        // if we have an empty search then just order the results by Id ascending
        sortBy = "Id";
        sortDir = true;
    }

    var result = Db.DatabaseTableEntity
                   .AsExpandable()
                   .Where(whereClause)
                   .Select(m => new YourCustomSearchClass
                   {
                       Id = m.Id,
                       Firstname = m.Firstname,
                       Lastname = m.Lastname,
                       Address1 = m.Address1,
                       Address2 = m.Address2,
                       Address3 = m.Address3,
                       Address4 = m.Address4,
                       Phone = m.Phone,                      
                       Postcode = m.Postcode,                      
                   })
                   .OrderBy(sortBy, sortDir) // have to give a default order when skipping .. so use the PK
                   .Skip(skip)
                   .Take(take)
                   .ToList();

    // now just get the count of items (without the skip and take) - eg how many could be returned with filtering
    filteredResultsCount = Db.DatabaseTableEntity.AsExpandable().Where(whereClause).Count();
    totalResultsCount = Db.DatabaseTableEntity.Count();

    return result;
}

Here is the predicate builder function that just plugs in a where clause dynamically. You will need to install (nugget) in linqkit for this.

In this example I am searching where the searchterm appears in either the firstname or lastname

private Expression<Func<DatabaseTableMappedClass, bool>> BuildDynamicWhereClause(DBEntities entities, string searchValue)
{
    // simple method to dynamically plugin a where clause 
    var predicate = PredicateBuilder.New<DatabaseTableMappedClass>(true); // true -where(true) return all
    if (String.IsNullOrWhiteSpace(searchValue) == false)
    {
        // as we only have 2 cols allow the user type in name 'firstname lastname' then use the list to search the first and last name of dbase
        var searchTerms = searchValue.Split(' ').ToList().ConvertAll(x => x.ToLower());

        predicate = predicate.Or(s => searchTerms.Any(srch => s.Firstname.ToLower().Contains(srch)));
        predicate = predicate.Or(s => searchTerms.Any(srch => s.Lastname.ToLower().Contains(srch)));
    }
    return predicate;
}

The only left to show is the datatable itself

var table = $('#SearchResultTable').DataTable({
    "proccessing": true,
    "serverSide": true,
    "ajax": {
        url: "@Url.Action("CustomServerSideSearchAction", "Home")",
        type: 'POST'
    },
    "language": {
        "search": "",
        "searchPlaceholder": "Search..."
    },
   "columns": [
        { "data": "Firstname" },
        { "data": "Lastname" }
    ]
});

Hope this helps

allan · February 2017

Superb - bookmarked! Thanks for sharing this. Hugely appreciated!

Allan

joroma · July 2017

Aside from LinqKit, did you use any other library? How did you get OrderBy to accept string parameters?

jhomel89 · October 2017

Thanks for the source codes. You save my day. But please modify the the 'ajax' portion in the js file. it should be like this:

"ajax": {
url: "/Controller/Action", //somthing like this @Url.Action("CustomServerSideSearchAction", "Home")
type: 'POST',
contentType: "application/json",
data: function (d) {
return JSON.stringify(d);
}
}
we need to stringify the data before sending it to controller action to bind it to the model.

erensogut · November 2017

Thanks for the codes.But I can not handle how pass to length of model via ajax.Because I made a filtering modal and remove original X entries per page part.I need pass it via model above.

Aquib_Raitech · December 2017

Thanks for implementation, saved my time. But one thing how to load next page data, because it just sorting the data from server side and loading in the first page, but how can i get second page data or other relative pages data just like normal datatable works.

filippoaceto · January 2018

i can't load next page data, it remain processing...
But in the server the data is loaded correctly.

Sylvanov · January 2018

Hi Everybody,

sorry to come here very late. But later than never. I've tried this and it working I'll not says perfectly cause I had to change a code to make id work.
this is the code I changed : In methode "GetDataFromDbase" I change the "OrderBy(sortBy, sortDir)" into a normal OrderBy, thus I'm unable to sort oder columns.

How can I fixe this? any solution will be verry appreciated.

Msilviu · February 2018

@Sylvanov you can use the following extension method:
public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> query, string memberName, bool asc = true)
{
ParameterExpression[] typeParams = new ParameterExpression[] {Expression.Parameter(typeof(T), "")};

        System.Reflection.PropertyInfo pi = typeof(T).GetProperty(memberName);

        return (IOrderedQueryable<T>) query.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable),
                asc ? "OrderBy" : "OrderByDescending",
                new Type[] {typeof(T), pi.PropertyType},
                query.Expression,
                Expression.Lambda(Expression.Property(typeParams[0], pi), typeParams))
            );
    }

goldharbor · February 2018

Hello, there is any implementation with additional parameters in the Model, like a Dictionary?

Nands · February 2018

Hi, I got this working with MVC 5 but when tried with MVC 4, the DataTableAjaxPostModel object comes into the controller method but model.search.value is null.
Any idea about the cause or the starting point to debug it?

Nands · February 2018

Adding
contentType: "application/json",
data: function (d) {
return JSON.stringify(d);
}
resolved the issue.

forsythwt · March 2018

For those who visit this article in the future and are trying to do this with .net core, let me assist you, and save some of your hair and sanity.

In the CustomServerSideSearchAction, you need to add the attribute [FromBody] to the incoming parameter. This is because .net core doesn't read the content-type of incoming json objects by default.

//Source:
https://andrewlock.net/model-binding-json-posts-in-asp-net-core/

jessicam · May 2018

You are a hero.

eal2100 · July 2018

In case anyone has trouble using the OrderBy, try downloading the NuGet for System.Linq.Dynamic.

Mohitgupta123 · July 2018

I can't understand when click on page number than how to hit controller and which parameter is send by clicking on that page number.

Note- I just want to know how to send parameter like page number ,limit and display count to the controller in Spring MVC.

If some one implements client side pagination than please provide that example

Mail_id-- guptamohit.psit@gmail.com
skype_id- guptamohit.psit@gmail.com

Thanks in advance.

CupOfJoe · December 2018

You're a F$#%#$ godsend. As someone who is new to this, I've been banging my head against the wall for a day or so, until now. Thank you for the post!

jmp3547 · January 2019

Hello, I've found this really helpful, however when I try to run it, it is giving me either a datatables error (http://datatables.net/tn/7) or it'll enter the CustomServerSideSearchAction(DataTableAjaxPostModel model) method but then model is null and then it throws a null pointer exception.

The only things I've tried is: changing the type in the ajax call as "GET" instead of "POST", where when I changed it to "GET", it went inside the method.

Does anyone have any idea what's going on?

colin · January 2019

The best bet is to go through the steps in the technical note you linked to. There's diagnostic steps that will help pin down the issue.

JeromeAbs · March 2019

To add to @forsythwt comment about using this example with .Net Core:
You may need to add
services.AddMvc().AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());
to your Startup.cs to keep your returned JSON data structure names from being "camelCased".

Also, you will need to install the proper LinqKit package for .Net Core:
PM> Install-Package LinqKit.Microsoft.EntityFrameworkCore

JeromeAbs · March 2019

I would also like to suggest that you do a null check when building your predicate queries:
predicate = predicate.Or(s => searchTerms.Any(srch => s.Firstname != null && s.Firstname.ToLower().Contains(srch)));
predicate = predicate.Or(s => searchTerms.Any(srch => s.Lastname != null && s.Lastname.ToLower().Contains(srch)));

Otherwise you may get a System.NullReferenceException

jricklefs · March 2019

I downloaded the The Nuget Package for System.Linq.Dynamic. v 1.0.7, I could not get sorting to work as in the Example I had to change .OrderBy(sortBy , sortDir)
to .OrderBy(sortBy + sortDir) where sortDIr was either "" or " desc", This could be an extension method as well.

DavidSuescunPelegay · April 2019

I've just created a Visual Studio solution with the full implementation.

https://github.com/DavidSuescunPelegay/jQuery-datatable-server-side-net-core

mhdcindioglu · June 2019

Thanks for this very good code.

I tried this code to get data from Api Service:

tblMaterials = $('#tblMaterials').DataTable({
        serverSide: true,
        processing: true,
        ajax: {
            url: '/Api/Materials',
            type: 'POST',
            contentType: 'application/json',
            data: function (d) {
                return JSON.stringify(d);
            },
        },
        columns: [
            {
                data: null,
                title: 'Id',
                render: function (data, type, item) {
                    return data.id;
                },
            },
            {
                data: null,
                title: 'Name',
                render: function (data, type, item) {
                    return '<button class="btn btn-link btn-sm text-info js-edit" data-id="' + data.id + '" title="Edit this material">' + data.name + '</button>';
                },
                className: 'w-100 py-0',
            },
            {
                data: null,
                render: function (data, type, item) {
                    return '<button class="btn btn-link btn-sm text-danger js-delete" data-id="' + data.id + '" title="Delete this material"><i class="fas fa-trash-alt"></i></button>';
                },
                orderable: false,
                className: 'py-0',
            },
        ],
        language: {
            oPaginate: {
                sPrevious: '<',
                sNext: '>',
            }
        },
        pagingType: 'simple',
        //dom: '<"top"<"d-none d-md-block"l>f>rt<"bottom"ip><"clear">', //(l)ength (f)iltring (p)agination p(r)ocessing (t)able (i)nformation
    });

Every thing is file except ordering, its not working fine.

Any suggestions?

mhdcindioglu · June 2019

I changed the code and it's now working fine:

$(document).ready(function () {
    tblMaterials = $('#tblMaterials').DataTable({
        serverSide: true,
        processing: true,
        ajax: {
            url: '/Api/Materials',
            type: 'POST',
            contentType: 'application/json',
            data: function (d) {
                return JSON.stringify(d);
            },
        },
        columns: [
            {
                data: 'Id',
                title: 'Id',
                render: function (data, type, item) {
                    return item.id;
                },
            },
            {
                data: 'Name',
                title: 'Name',
                render: function (data, type, item) {
                    return '<button class="btn btn-link btn-sm text-info js-edit" data-id="' + item.id + '" title="Edit this material">' + item.name + '</button>';
                },
                className: 'w-100 py-0',
            },
            {
                data: null,
                render: function (data, type, item) {
                    return '<button class="btn btn-link btn-sm text-danger js-delete" data-id="' + data.id + '" title="Delete this material"><i class="fas fa-trash-alt"></i></button>';
                },
                orderable: false,
                className: 'py-0',
            },
        ],
        language: {
            oPaginate: {
                sFirst: '<<',
                sPrevious: '<',
                sNext: '>',
                sLast: '>>'
            }
        },
        pagingType: 'simple',
        //dom: '<"top"<"d-none d-md-block"l>f>rt<"bottom"ip><"clear">', //(l)ength (f)iltring (p)agination p(r)ocessing (t)able (i)nformation
    });

    tblMaterials.on('xhr', function (e, settings, json) {
        $('#btnRefresh').removeAttr('disabled');
    });
});

paparoacho · September 2019

What is YourCustomSearchClass and DataTableMappedClass?

amazeinstudios@gmail.com · November 2019

Can anyone post their solution file for VS 2017 ASP.Net MVC? I am a little confused on where to put the files.

birger100 · December 2019

great article
If anyone need a sample solution mine is available here
https://dev.azure.com/bdh100/_git/DataTables

xichle · January 2020

Thanks for sharing Birger100! It works! I have a minor change for the sorting to work (!).

if (!String.IsNullOrEmpty(searchBy))
{
// if we have an empty search then just order the results by Id ascending
sortBy = "Id";
sortDir = true;
}

Also the link for getting the AdventureWorks2017 database:
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak

joe912 · February 2020

Thanks @DavidSuescunPelegay for the .NET Core implementation

farshidazii · August 2020

I need an example of a (Server-side Procssing) SearchPanes implementation of DataTables in mvc core? please help me

Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

Replies

Howdy, Stranger!

Categories

DataTables

Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables