DEV Community: Ismail El Fekak

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

Ismail El Fekak — Thu, 10 Nov 2022 14:07:05 +0000

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.

Using DotLiquid to create a custom template in Asp.Net Core

Ismail El Fekak — Thu, 01 Sep 2022 12:58:35 +0000

Introduction

Liquid is an open-source template language created by Shopify and written in Ruby. It can be used to add dynamic content to pages, and to create a wide variety of custom templates. While DotLiquid is a templating system ported to the .NET framework from Ruby’s Liquid Markup.

Even though the fact that Liquid is vastly used, the documentation for DotLiquid isn't that exhaustive and finding guides for specific use cases might not bear any fruits.

I myself struggled a bit while trying to use it, especially with complex JSON objects but at the end I've finally got some success.

We'll move directly to how to use DotLiquid to create a template in Asp.Net Core (.Net 6).

Setup

First we'll need a working .net project, using your favorite IDE create a new Asp.Net Core Web App using the MVC template:

If you prefer using CLI type in this command in the directory where you want to add your project:
dotnet new mvc -au None

Then using either Nuget Package Manager or the CLI, we'll have to add the DotLiquid package
dotnet add package DotLiquid and the
NewtonSoft.Json package to the project
dotnet add package Newtonsoft.Json

Demo

On creation our project would look like this:

First in our wwwroot (or webroot) folder let's create a template directory and add a template file to it, which are files that end with the .liquid extension and use a combination of objects, tags, and filters to make the content dynamic.
For short it's a normal HTML syntax + some extra syntax for the logic.

Let's name our file example.liquid, and for the moment we'll put a single div in it, to test that the template rendering works.

<div>
    <h3>The template content</h3>
</div>

Then in the HomeController let’s add the logic to render a template to its Index Action Method:

 public IActionResult Index()
{
    // we need to read the contents of the template file
    string liquidTemplateContent = System.IO.File.ReadAllText("wwwroot/template/example.liquid");
    // then we parse the contents of the template file into a liquid template
    Template template = Template.Parse(liquidTemplateContent);
    // then we render the template
    string result = template.Render();
    // and then we return the result to the view in a view bag object
    ViewBag.template = result;

    return View();
}

Then all we need to do is put our template in our view file, overwrite the content of Views/Index.cshtml with the following:

@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    @Html.Raw(ViewBag.template)
</div>

Here we used the @Html.Raw helper tag to render the html inside the template

Finally run your project to see the result, that should look like this:

Now to use dynamic data, let's start by by adding a class named Employee to the Model directory, that should look like this:

Assuming we want to render employees data in our template, to enforce security DotLiquid doesn't let you access a Models attribute directly, as explained in their docs:

DotLiquid is focused on making your templates safe. It does this by making sure templates can only access properties and methods that have been specifically enabled.
This means that you can't just pass a model instance to the template and access the properties directly.

So to enable the properties we'll have to create a Drop object which uses an opt-in approach to expose data.
A Drop class is a class that inherits from the DotLiquid Drop class and exposes the data we want to pass to our template.

Let's add the following to our Employee class:

public class EmployeeDrop : Drop
{
    private readonly Employee _employee;

    public EmployeeDrop(Employee employee)
    {
        _employee = employee;
    }

    public string Name => _employee.Name;
    public string Email => _employee.Email;
    public string Phone => _employee.Phone;
}

I omitted adding the address for the moment to show that if we don't add an attribute to the drop the template wouldn't be able to output it and instead would show a blank string.

Then in the HomeController we need to make the following change to the Index action method:

 public IActionResult Index()
    {
        // we need to read the contents of the template file
        string liquidTemplateContent = System.IO.File.ReadAllText("wwwroot/template/example.liquid");
        // then we parse the contents of the template file into a liquid template
        Template template = Template.Parse(liquidTemplateContent);
        // then we create a new instance of the model class
        Employee employee = new Employee
        {
            Name = "John Doe",
            Email = "john.doe@example.com",
            Phone = "555-555-5555",
            Address = "123 Main St."
        };

        Hash hash = Hash.FromAnonymousObject(new
        {
            employee = new EmployeeDrop(employee)
        });
        // then we render the template
        string result = template.Render(hash);
        // and then we return the result to the view in a view bag object
        ViewBag.template = result;

        return View();
    }

Let's not forget adding the data to our example.liquid template:

<div>
    <h3>The template content</h3>
    <p>
        Name: {{ employee.name }}
    </p>
    <p>
        Email: {{ employee.email }}
    </p>
    <p>
        Phone: {{ employee.phone }}
    </p>
    <p>
        Address: {{ employee.address }}
    </p>
</div>

After restarting the App we'll get something like this:

Notice that we got an empty address as we didn't expose it in the drop.
Next let's do just that, but instead of a normal string let's say we have a JSON object so we'd cover one of the most recurring problems nowadays.
In the EmployeeDrop let's add the Address attribute like so, we'll use NewtonSoft.Json to deserialize the JSON.

public class EmployeeDrop : Drop
{
    // ... old code
    public IDictionary<string, object>? Address =>
        JsonConvert.DeserializeObject<IDictionary<string, object>>(_employee.Address);
}

Then let's change the address format to a valid JSON in the Index action method:

 public IActionResult Index()
    {
        // ... old code

        var address = @"
        {
            ""Street"" : ""123 Main St"",
            ""City"" : ""Anytown"",
            ""State"" : ""WA"",
            ""Zip"" : ""12345""
        }
        ";
        Employee employee = new Employee
        {
            Name = "John Doe",
            Email = "john.doe@example.com",
            Phone = "555-555-5555",
            Address = address
        };

        // ... old code

        return View();
    }

The last step is to use the new attribute inside our template:

<div>
    <h3>The template content</h3>
    <p>
        Name: {{ employee.name }}
    </p>
    <p>
        Email: {{ employee.email }}
    </p>
    <p>
        Phone: {{ employee.phone }}
    </p>
    <p>Address:</p>
    <p>Street: {{ employee.address.Street }}</p>
    <p>City: {{ employee.address.City }}</p>
    <p>State: {{ employee.address.State }}</p>
    <p>Zip: {{ employee.address.Zip }}</p>
</div>

Although the drop fields is case insensitive, know that the JSON fields are case sensitive.

And here's the preview:

It might seem like you can render any JSON like this but if we tried deserializing a complex object and using it in our template all we'd get for the nested fields are blank strings.

Let's say an Employee can have multiple addresses and we have a JSON object containing an array of addresses, let's make the following changes to the Index action method:

 public IActionResult Index()
    {
        // old code
        var addresses = @"
        {
            ""Addresses"": [{
                    ""Address"": ""123 Main Street"",
                    ""City"": ""Montreal"",
                    ""State"": ""QC"",
                    ""Zip"": ""H1S1M5"",
                    ""Country"": ""Canada""
                },
                {
                    ""Address"": ""456 Main Street"",
                    ""City"": ""Montreal"",
                    ""State"": ""QC"",
                    ""Zip"": ""H1S1M5"",
                    ""Country"": ""Canada""
                }
            ]
        }";
        Employee employee = new Employee
        {
            Name = "John Doe",
            Email = "john.doe@example.com",
            Phone = "555-555-5555",
            Address = addresses
        };

        // ... old code

        return View();
    }

To make our template recognize the Nested JSON we need to use a custom Dictionary Converter with our deserializer, here you'll find one that you can use, you can add it as is to the Employee class and then modify the Address attribute in EmployeeDrop to use it like so:

public class EmployeeDrop : Drop
{
    // ... old code
    public IDictionary<string, object>? Address =>
        JsonConvert.DeserializeObject<IDictionary<string, object>>(_employee.Address, new DictionaryConverter());
}

The final step is to change the template:

<div>
    <h3>The template content</h3>
    <p>
        Name: {{ employee.name }}
    </p>
    <p>
        Email: {{ employee.email }}
    </p>
    <p>
        Phone: {{ employee.phone }}
    </p>
    <p>Addresses:</p>
    <div>
        {% for addr in employee.address.Addresses -%}
            <div>
                <p>Street: {{ addr.Street }}</p>
                <p>City: {{ addr.City }}</p>
                <p>State: {{ addr.State }}</p>
                <p>Zip: {{ addr.Zip }}</p>
                <p>Country: {{ addr.Country }}</p>
            </div>
        {% endfor %}
    </div>
</div>

Then after restarting you'll get a preview like this:

Conclusion

I hope you'll find some answers on how to create a DotLiquid template in Asp.Net Core in this article. I didn't cover the usual control flow or iteration tags as the documentation of the Liquid templates already has all the basics and what's nice in DotLiquid is that you can use almost anything from the liquid template language as is, you can check their docs here.

You'll find the final code on this Github repository.