Creating a generic Class for DataTables server-side operations in Asp.Net Core

Introduction

DataTables is a jQuery plugin that enhance HTML tables to support pagination, search and ordering, in addition to easily editable themes and easy internationalisation or multiple language support.
It supports multiple ways of fetching data, either from the DOM, JS or, and in this case using server-side processing as it's the most adapted mode for large databases and big projects, and as all the operations are handled on the server side this limits the amount of records being fetched by DataTables and increases performance thus speeding up loading time.

Setup

For starters we need to create a new project with the Asp.Net Core Web Application template of the type Web API using the .Net SDK version 6.0

The initial folder structure looks like this:

We'll clean it up by removing the existing controller and model and making ones of our own.

Implementation

To test DataTables we need some data models, we can use a sample database like this one you are free to choose another one, or even another DBMS if you want.
To scaffold the data models we can use EntityFramework Core, but first we'll have to add a few Nuget Packages to help us establish a connection to the database and scaffold the models:

One important thing to use EFCore is that we need the dotnet-ef command line tool, if you haven't installed it before you can install it using the following cli command:
dotnet tool install --global dotnet-ef

Then to scaffold our database we'll need a folder to store the Models and DbContext we'll create a Data folder in the project directory then run the following command in the CLI:

dotnet ef dbcontext scaffold -f
"server=DBHOST;database=DBNAME;user=YOURUSERNAME;password=YOURPASSWORD" 
Pomelo.EntityFrameworkCore.MySql --output-dir ./Data

By doing that we now have our data Models and DbContext classes.

---

We'll start by creating a Controller that will contain a unique POST method that returns an object containing the data requested (you'll need as many Action Methods as DataTables you want to render).

[HttpPost]
public ActionResult GetDataTableData(
[FromBody] DatatableRequest request,
[FromServices] DataTableService<Customer> service)
{
    var model = service.GetData();
    var response = service.GetDatatableObject(request, model);
    return Ok(response);
}

Note: In the AJAX request made by DataTables on the front side the DataTable sends an object in the request body that includes details about the table and what it's asking for, mainly the columns it contains, the number of rows it's trying to show, a search keyword if it contains any and the sort column and direction. Hence the POST request and not a GET.

Don't forget to register the service using the type of the Model you are working with:

builder.Services.AddScoped<DataTableService<Customer>>();

To handle this data we'll have to create a class that'll be called DatatableRequest and it'll define the fields of the request Payload.

public class DatatableRequest
{
    public int Draw { get; set; }
    public int Start { get; set; }
    public int Length { get; set; }
    public Search? Search { get; set; }
    public List<Sort>? Sorts { get; set; }
    public List<Column>? Columns { get; set; }
}

• Draw field is a number that gets auto-incremented each time a request is made.
• Start field defines where pagination starts.
• Length field is how many rows we want to show.
• Search field contains the search Value and a boolean Regex.
• Sorts field is a list containing the ordering Column and Dir or direction.
• Columns field is a list of the columns of the DataTable, each column contains parameters defined on the front side to determine if it's searchable, orderable..

---

Another important class we'll need is the DatatableResponse<T> that follows the structure of a DataTable Response object:

public class DatatableResponse<T>
{
    public int Draw { get; set; }
    public int RecordsTotal { get; set; }
    public int RecordsFiltered { get; set; }
    public List<T>? Data { get; set; }
}

• RecordsTotal is the number of all rows for the given model
• RecordsFiltered is the number of rows after a search
• Data is a list of the model's objects

---

We'll then proceed to the creation of our main content which is a generic class that'll handle the operations required by the DataTable. We'll make a directory called Services and create a class DataTableService.

And add a method that should be able to give us the correct data in response to the request made:

public DatatableResponse<T> GetDatatableObject(DatatableRequest request,
    IQueryable<T> model)
{
    var recordsTotal = model.Count();
    if (request.Search.Value != "") model = Search(model, request.Search.Value, request.Columns);

    model = Sort(request.Order, request.Columns, model);
    var recordsFiltered = recordsTotal;
    try
    {
        recordsFiltered = model.Count();
    }
    catch (Exception)
    {
        // Ignored
    }

    var data = Paginate(model, request.Start, request.Length);

    return new DatatableResponse<T>
        { Draw = request.Draw, RecordsFiltered = recordsFiltered, RecordsTotal = recordsTotal, Data = data };
}

The first thing to do is count the rows for the given IQueryable to get the recordsTotal, then we have to check if there is a search value to look for in our data model, if so we have to implement a Search method that lets us search the IQueryable else we can use a Nuget Package like NinjaNye.SearchExtensions that offers that for columns with string data type as simply as:

private static IQueryable<T> Search(IQueryable<T> model, StringValues searchValue)
{
    return model.Search().Containing(searchValue);
} 

Although for this tutorial we'll be using a generic search method, that searches all columns using string.Contains.
The idea behind that is to call a Search method on each column of a given Data Model, to do that we need to be able to use a loop and an Or predicate but LINQ doesn't offer something like that instead if we use a Where condition inside a loop the multiple queries would be translated to SQL And which will make the search fail.
To solve that we can use LINQKit which offers a set of extensions for LINQ to SQL or EF queries, which is useful to build complex queries and in our case the need to search multiple columns by using a simple loop, that needs to be translated to an OR operator.

---

Next is the suggested implementation for the Search method:

private static IQueryable<T> Search(IQueryable<T> model, StringValues searchValue, List<Column> columns)
{
    var predicate = PredicateBuilder.New<T>(true);

    predicate = columns.Aggregate(predicate, (current, column) => SearchAColumn(current, column, searchValue));

    return model.AsExpandable().Where(predicate.Compile()).AsQueryable();
}

This method takes the model we are searching and the search value and a list of columns used in the DataTable so as to search only the columns visible to the end user.
The first thing to do is use LINQKit PredicateBuilder to create a dynamic lambda expression tree that will help us perform an Or-based predicate.
After that we use LINQ Aggregate that helps us building our predicate for each column with a custom method that contains the search logic.
And at the end we return our model and perform the queries contained on our predicate (again we need the help of LINQKit to convert the Expression into a Func so that the compiler can understand it).

---

Next we'll implement the method SearchAColumn to search a single column, which takes the predicate, the DataTable Column object and the search value.

private static ExpressionStarter<T> SearchAColumn(ExpressionStarter<T> predicate, Column column,
    StringValues value
)
{
if (!column.Searchable) return predicate;

var filterColumn = FormatColumnName(column.Data);
var searchValue = value.ToString();
var prop = typeof(T).GetProperty(filterColumn);

if (prop == null) return predicate;

return predicate.Or(x => Contains(prop, x, searchValue));
}

After that thanks to LINQKit Or extension method we can add the current expression tree to our existing predicate.
That's it like this we can search our Model rows for the given search value.

An important thing to note here is that if you have some nullable property the search would be broken as using Contains on a null string throws a NullReferenceException error and it seems like LINQKit doesn't throw that.

To fix that you'll have to check if the property is null before using string.Contains as expression trees don't support the null-conditional operator.

private static bool Contains(PropertyInfo prop, T x, string searchValue)
{
    return prop.GetValue(x) != null && prop.GetValue(x).ToString().ToLower().Contains(searchValue);
}

---

The next thing to take into consideration is the data ordering, as DataTables lets us do multi-column sorting we can use a loop to run a Sort method on each given column.

private static IQueryable<T> Sort(IReadOnlyList<Sort> orders, IReadOnlyList<Column> columns,
    IQueryable<T> model)
{
    var column = FormatColumnName(columns[orders[0].Column].Data);

    var orderedQueryable = model
        .OrderBy(column + " " + orders[0].Dir);

    for (var i = 1; i < orders.Count; i++)
    {
        column = FormatColumnName(columns[orders[i].Column].Data);
        orderedQueryable = orderedQueryable.ThenBy(column + " " + orders[i].Dir);
    }

    return orderedQueryable;
}

Unlike Search the Sort method is quite simple, we run an OrderBy on the first value in our Column list and then loop through the other columns and use ThenBy extension.

An important thing to note is that for OrderBy and ThenBy to work we need to add the Nuget package System.Linq.Dynamic.Core.

---

The last thing to do is run pagination on the model return the correct number and page requested in the request data.

private static List<T> Paginate(IQueryable<T> model, int skip, int pageSize)
{
    try
    {
        return model.Skip(skip).Take(pageSize).ToList();
    }
    catch (NullReferenceException)
    {
        return new List<T>();
    }
}

The Paginate method consists of using LINQ Skip and Take to return the correct data records.

And the last step is to return an object that has 4 fields, which is the draw, recordsTotal, recordsFiltered and data.
Now testing that on a front app you'll get something like this:

Conclusion

I hope you'll find this article useful on how to create a generic DataTables Class in Asp.Net Core with the basic operations of filtering and sorting.

You'll find the source code used in this article in this repository.
I included an Angular demo app there too that uses Angular DataTables library so you can test the rendering if you want, you just have to add the correct Models and edit the columns in the dtOptions field.

If you have any suggestions on how to improve this article feel free to leave a comment.