Creating a generic Class for DataTables server-side operations in Asp.Net Core
Tags: dotnet, datatables, linqkit, efcore
Published: 2022-11-10 14:07:05

## Introduction
[DataTables](https://datatables.net/) 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
![Project Creation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oja02ncx66wuljpcj8ar.png)

The initial folder structure looks like this:

![Initial files structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1590sa9uz749rflprivz.png)
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](https://www.mysqltutorial.org/mysql-sample-database.aspx) 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:

![Database Packages](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/57041entgjltjf8qc7c4.png)

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:
```bash
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.

![Data Folder Content](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dwox1phxnqtivyro46lk.png)

--- 

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).
```csharp 
[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](https://datatables.net/manual/server-side) 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.

```csharp
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](https://datatables.net/manual/server-side):
```csharp
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:
```csharp
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:
```csharp
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](https://github.com/scottksmith95/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:
```csharp
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.
```csharp
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.
```csharp 
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.
```csharp
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.
```csharp
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:


![Rendered DataTable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c2sjdoo3ta7x974dzyas.png)

##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](https://github.com/elf-is/datatables-test).
I included an Angular demo app there too that uses [Angular DataTables](https://l-lin.github.io/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.