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
pingcrosby
Posts: 29Questions: 4Answers: 1
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
Replies
Superb - bookmarked! Thanks for sharing this. Hugely appreciated!
Allan
Aside from LinqKit, did you use any other library? How did you get OrderBy to accept string parameters?
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.
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.
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.
i can't load next page data, it remain processing...
But in the server the data is loaded correctly.
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.
@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), "")};
Hello, there is any implementation with additional parameters in the Model, like a Dictionary?
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?
Adding
contentType: "application/json",
data: function (d) {
return JSON.stringify(d);
}
resolved the issue.
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/
You are a hero.
In case anyone has trouble using the OrderBy, try downloading the NuGet for System.Linq.Dynamic.
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.
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!
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?
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.
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.EntityFrameworkCoreI would also like to suggest that you do a
nullcheck 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
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.I've just created a Visual Studio solution with the full implementation.
https://github.com/DavidSuescunPelegay/jQuery-datatable-server-side-net-core
Thanks for this very good code.
I tried this code to get data from Api Service:
Every thing is file except ordering, its not working fine.
Any suggestions?
I changed the code and it's now working fine:
What is YourCustomSearchClass and DataTableMappedClass?
Can anyone post their solution file for VS 2017 ASP.Net MVC? I am a little confused on where to put the files.
great article
If anyone need a sample solution mine is available here
https://dev.azure.com/bdh100/_git/DataTables
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
Thanks @DavidSuescunPelegay for the .NET Core implementation
I need an example of a (Server-side Procssing) SearchPanes implementation of DataTables in mvc core? please help me