ASP.NET8 using DataTables.net – Part9 – Advanced Filters

Mark PelfMark Pelf
9 min read

ASP.NET8 using DataTables.net – Part9 – Advanced Filters

A practical guide to using jQuery DataTables.net component in Asp.Net 8 MVC application.

Abstract: A practical guide to building an Asp.Net 8 MVC application that uses jQuery component DataTables.net. This is a continuation of article Part8.

1 ASP.NET8 using jQuery DataTables.net

I was evaluating the jQuery DataTables.net component [1] for usage in ASP.NET8 projects and created several prototype (proof-of-concept) applications that are presented in these articles.

1.1 Articles in this series

Articles in this series are:

  • ASP.NET8 using DataTables.net – Part1 – Foundation
  • ASP.NET8 using DataTables.net – Part2 – Action buttons
  • ASP.NET8 using DataTables.net – Part3 – State saving
  • ASP.NET8 using DataTables.net – Part4 – Multilingual
  • ASP.NET8 using DataTables.net – Part5 – Passing additional parameters in AJAX
  • ASP.NET8 using DataTables.net – Part6 – Returning additional parameters in AJAX
  • ASP.NET8 using DataTables.net – Part7 – Buttons regular
  • ASP.NET8 using DataTables.net – Part8 – Select rows
  • ASP.NET8 using DataTables.net – Part9 – Advanced Filters

2 Final result

The goal of this article is to create a proof-of-concept application that demos the DataTables.net component with the use of a custom advanced filter. Let us present the result of this article.

In this example, we disabled the DataTables.net component’s default filter in the upper right corner and created a separate custom form advanced filter. The default filter (search) box was simple and applied the same value for all columns. Often in professional usage one needs more elaborate filtering ability.

I looked very carefully at the site [1] at all the extensions and plugins that the DataTables.net component offers for advanced filtering. But I didn’t like anything that was there, nothing was professional enough for my users. So, I decided to do some coding in ASP.NET and create my own custom advanced filter, and integrate DataTables.net component for presentation of the table. The solution presented here is what I came up with.

3 Advanced Filter Form

The custom advanced filter is just another ASP.NET form, and I decided to save the filter state into ASP.NET Session object, so that info is available to the DataTables component during each AJAX call. That way “filter state” is shared between different actions/forms in the application. Here is the relevant code.

//HomeController.cs ======================================

public class HomeController : Controller
{
public const string EMPLOYEES_ADVANCED_FILTER_STATE = "EMPLOYEES_ADVANCED_FILTER_STATE";

public IActionResult EmployeesAdvancedFilter(EmployeesAdvancedFilterVM model)
{           
    try
    {
        ISession ? CurrentSession=this.HttpContext?.Session;

        if (model.IsSubmit)
        {
            //we have submit
            //we save the advanced-filter state to session
            if (CurrentSession != null && model != null)
            {
                model.FirstName=model.FirstName?.Trim();
                model.LastName = model.LastName?.Trim();
                model.City = model.City?.Trim();
                model.Country = model.Country?.Trim();
                string jsonUserEmployeesAdvancedFilterState = JsonSerializer.Serialize(model);
                CurrentSession.SetString(EMPLOYEES_ADVANCED_FILTER_STATE, jsonUserEmployeesAdvancedFilterState);
            }

            return RedirectToAction("Employees", "Home");
        }
        else if (model.IsReset)
        {
            //we have reset
            //we clear advanced-filter state in session
            CurrentSession?.Remove(EMPLOYEES_ADVANCED_FILTER_STATE);
        }

        //go for presentation
        {
            //we get advanced-filter state from session if there is one
            string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);

            if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState)) 
            {
                model = JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState)
                    ?? new EmployeesAdvancedFilterVM();
            }                    
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }

    return View(model);
}

//EmployeesAdvancedFilterVM.cs
namespace Example09.Models.Home
{
    public class EmployeesAdvancedFilterVM
    {
        //model
        public string? FirstName { get; set; } = null;
        public string? LastName { get; set; } = null;
        public string? City { get; set; } = null;
        public string? Country { get; set; } = null;
        public bool IsSubmit { get; set; } = false;
        public bool IsReset { get; set; } = false;

        //view model
    }
}

<!-- EmployeesAdvancedFilter.cshtml -->
@using Example09.Models.Home;

@model EmployeesAdvancedFilterVM

@{
    <div class="text-center">
        <h3 class="display-4">Employees Advanced Filter</h3>
    </div>

    <fieldset class="border rounded-3 p-3" style="width:600px">
        <legend class="float-none w-auto px-3">Choose filter parameters</legend>

        <p class="bg-light m-1 border p-1">
        * = whildchar, zero or more characters <br/>
        ? = whildchar, one character
        </p>

        <form id="form1" method="post">                  

            <div class="form-group">
                <label asp-for=FirstName>Given Name</label>
                <input class="form-control" asp-for="FirstName" />
            </div>

            <div class="form-group">
                <label asp-for=LastName>Family Name</label>
                <input class="form-control" asp-for="LastName" />
            </div>

            <div class="form-group">
                <label asp-for=City>Town</label>
                <input class="form-control" asp-for="City" />
            </div>

            <div class="form-group">
                <label asp-for=Country></label>
                <input class="form-control" asp-for="Country" />
            </div>

            <button type="submit" form="form1" class="btn btn-primary mt-3 ms-3 float-end" asp-area="" 
                asp-controller="Home" asp-action="EmployeesAdvancedFilter" asp-route-IsSubmit="true">
                Submit
            </button>

            <a class="btn btn-primary mt-3 float-end" asp-area=""
                    asp-controller="Home" asp-action="EmployeesAdvancedFilter" asp-route-IsReset="true">
                Reset
            </a>
        </form>
    </fieldset>
}

4 Client-side DataTables.net component

Here I will just show what the ASP.NET view using DataTables component looks like.

<!-- Employees.cshtml -->
@using Example09.Models.Home;

@model EmployeesVM

<partial name="_LoadingDatatablesJsAndCss" />

@{
    <div class="text-center">
        <h3 class="display-4">Employees table - Advanced Filter</h3>
    </div>

    <h4 class="bg-info m-4 border p-3">
        @{
            string text1 = "Filter= " + Model?.AdvancedFilterState;
            @text1
        }
    </h4>

    <!-- Here is our table HTML element defined. JavaScript library Datatables
    will do all the magic to turn it into interactive component -->
    <table id="EmployeesTable01" class="table table-striped table-bordered ">
    </table>
}

<script type="text/javascript">
    // Datatables script initialization =========================================
    // we used defer attribute on jQuery so it might not be available at this point
    // so we go for vanilla JS event

    document.addEventListener("DOMContentLoaded", InitializeDatatable);

    function InitializeDatatable() {
        $("#EmployeesTable01").dataTable(
            //providing initialization parameters as JavaScript object
            {
                //processing-Feature control the processing indicator.
                processing: true,
                //paging-Enable or disable table pagination.
                paging: true,
                //info-Feature control table information display field
                info: true,
                //ordering-Feature control ordering (sorting) abilities in DataTables.
                ordering: true,
                //searching-Feature control search (filtering) abilities
                searching: false,

                //autoWidth-Feature control DataTables' smart column width handling.
                autoWidth: true,
                //lengthMenu-Change the options in the page length select list.
                lengthMenu: [10, 15, 25, 50, 100],
                //pageLength-Change the initial page length (number of rows per page)
                pageLength: 10,

                //order-Initial order (sort) to apply to the table.
                order: [[1, 'asc']],

                //serverSide-Feature control DataTables' server-side processing mode.
                serverSide: true,

                //Load data for the table's content from an Ajax source.
                ajax: {
                    "url": "@Url.Action("EmployeesDT", "Home")",
                    "type": "POST",
                    "datatype": "json"
                },

                //Set column specific initialisation properties.
                columns: [
                    //name-Set a descriptive name for a column
                    //data-Set the data source for the column from the rows data object / array
                    //title-Set the column title
                    //orderable-Enable or disable ordering on this column
                    //searchable-Enable or disable search on the data in this column
                    //type-Set the column type - used for filtering and sorting string processing
                    //visible-Enable or disable the display of this column.
                    //width-Column width assignment.
                    //render-Render (process) the data for use in the table.
                    //className-Class to assign to each cell in the column.

                    {   //0
                        name: 'id',
                        data: 'id',
                        title: "Employee Id",
                        orderable: true,
                        searchable: false,
                        type: 'num',
                        visible: false
                    },
                    {
                        //1
                        name: 'givenName',
                        data: "givenName",
                        title: "Given Name",
                        orderable: true,
                        searchable: true,
                        type: 'string',
                        visible: true
                    },
                    {
                        //2
                        name: 'familyName',
                        data: "familyName",
                        title: "Family Name",
                        orderable: true,
                        searchable: true,
                        type: 'string',
                        visible: true
                    },
                    {
                        //3
                        name: 'town',
                        data: "town",
                        title: "Town",
                        orderable: true,
                        searchable: true,
                        type: 'string',
                        visible: true
                    },
                    {
                        //4
                        name: 'country',
                        data: "country",
                        title: "Country",
                        orderable: true,
                        searchable: true,
                        type: 'string',
                        visible: true,
                        width: "150px",
                        className: 'text-center '
                    },
                    {
                        //5
                        name: 'email',
                        data: "email",
                        title: "Email",
                        orderable: true,
                        searchable: true,
                        type: 'string',
                        visible: true
                    },
                    {
                        //6
                        name: 'phoneNo',
                        data: "phoneNo",
                        title: "Phone Number",
                        orderable: false,
                        searchable: true,
                        type: 'string',
                        visible: true
                    }
                ],

                layout: {
                    top1Start: {
                        buttons:
                            [
                                {
                                    text: 'Filter',
                                    action: AdvancedFilter
                                }
                            ]
                    }
                }
            } // end of initialization object
        );

        function AdvancedFilter(e, dt, node, config) {
            let EmployeesAdvancedFilter = "@Url.Action("EmployeesAdvancedFilter", "Home")";
            //redirect to another page
            window.location.replace(EmployeesAdvancedFilter);
        };
    }
</script>

More about JavaScript properties can be found in the manual at [1]. The application here is just a proof of concept for ASP.NET environment.

5 ASP.NET back-end processing

So, we are now at C#/.NET part, writing our ASP.NET code. Note that in action Employees() and in method FilterRowsPerSavedAdvancedFilterState() we retrieve the filter state from ASP.NET Session object. The main trick here is that during each AJAX call, we get the filter state from the ASP.NET Session object and apply filter content to the back-end processing of the table.

////EmployeesVM.cs
namespace Example09.Models.Home
{    
    public class EmployeesVM
    {
        //view model
        public String? AdvancedFilterState { get; set; } = null;        
    }
}

//HomeController.cs ======================================
namespace Example09.Controllers
{
    public class HomeController : Controller
    {
        public const string EMPLOYEES_ADVANCED_FILTER_STATE = "EMPLOYEES_ADVANCED_FILTER_STATE";

public IActionResult Employees(EmployeesVM model)
{
    try
    {
        ISession? CurrentSession = this.HttpContext?.Session;

        {
            //we get advanced-filter state from session if there is one
            string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);

            if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState))
            {
                EmployeesAdvancedFilterVM? AdvancedFilterState =
                    JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState);

                if (AdvancedFilterState != null)
                {
                    string filterState =
                        "Given Name: " + AdvancedFilterState.FirstName +
                        "; Family Name: " + AdvancedFilterState.LastName +
                        "; Town: " + AdvancedFilterState.City+
                        "; Country: " + AdvancedFilterState.Country ;
                    model.AdvancedFilterState = filterState;    
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
    return View(model);
}

//this is target of AJAX call and provides data for
//the table, based on selected input parameters
public IActionResult EmployeesDT(DataTables.AspNet.Core.IDataTablesRequest request)
{
    // There is dependency in this method on names of fields
    // and implied mapping. I see it almost impossible to avoid.
    // At least, in this method, we avoided dependency on the order
    // of table fields, in case order needs to be changed
    //Here are our mapped table columns:
    //Column0 id -> Employee.Id
    //Column1 givenName -> Employee.FirstName
    //Column2 familyName -> Employee.LastName
    //Column3 town -> Employee.City
    //Column4 country -> Employee.Country
    //Column5 email -> Employee.Email
    //Column6 phoneNo -> Employee.Phone

    try
    {
        IQueryable<Employee> employees = MockDatabase.MockDatabase.Instance.EmployeesTable.AsQueryable();

        //here we get the count that needs to be presented by the UI
        int totalRecordsCount = employees.Count();

        employees = FilterRowsPerSavedAdvancedFilterState(employees);

        var iQueryableOfAnonymous = employees.Select(p => new
        {
            id = p.Id,
            givenName = p.FirstName,
            familyName = p.LastName,
            town = p.City,
            country = p.Country,
            email = p.Email,
            phoneNo = p.Phone,
        });

        //here we get the count that needs to be presented by the UI
        int filteredRecordsCount = iQueryableOfAnonymous.Count();

        iQueryableOfAnonymous = SortRowsPerRequestParamters(iQueryableOfAnonymous, request);

        iQueryableOfAnonymous = iQueryableOfAnonymous.Skip(request.Start).Take(request.Length);

        //here we materialize the query
        var dataPage = iQueryableOfAnonymous.ToList();

        var response = DataTablesResponse.Create(request, totalRecordsCount, filteredRecordsCount, dataPage);

        return new DataTablesJsonResult(response, false);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        var response = DataTablesResponse.Create(request, "Error processing AJAX call on server side");
        return new DataTablesJsonResult(response, false);
    }
}


private IQueryable<Example09.MockDatabase.Employee> FilterRowsPerSavedAdvancedFilterState(
   IQueryable<Example09.MockDatabase.Employee> iQueryableOfEmployee)
{
    try
    {
        ISession? CurrentSession = this.HttpContext?.Session;

        {
            //we get advanced-filter state from session if there is one
            string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);

            if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState))
            {
                EmployeesAdvancedFilterVM? advancedFilter = 
                    JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState);

                if (advancedFilter != null)
                {
                    //FirstName
                    advancedFilter.FirstName = advancedFilter.FirstName?.Trim();
                    if (!string.IsNullOrEmpty(advancedFilter.FirstName))
                    {
                        /*
                         * In EF we would go for DbFunctions.Like
                        if (advancedFilter.FirstName.Contains('*') || advancedFilter.FirstName.Contains('?'))
                        {
                            string pattern = advancedFilter.FirstName.Replace('*', '%').Replace('?', '_');
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(vk => DbFunctions.Like(vk.FirstName, pattern));
                        }
                        else
                        {
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.FirstName != null && vk.FirstName.Equals(advancedFilter.FirstName));
                        }
                        */

                        //in pure Linq going for Regex
                        if (advancedFilter.FirstName.Contains('*') || advancedFilter.FirstName.Contains('?'))
                        {
                            string pattern = advancedFilter.FirstName.Replace("*", ".*").Replace("?", ".{1}");
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.FirstName != null && Regex.IsMatch(vk.FirstName, pattern));
                        }
                        else
                        {
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.FirstName != null && vk.FirstName.Equals(advancedFilter.FirstName));
                        }
                    }

                    //LastName
                    advancedFilter.LastName = advancedFilter.LastName?.Trim();
                    if (!string.IsNullOrEmpty(advancedFilter.LastName))
                    {
                        //in pure Linq going for Regex
                        if (advancedFilter.LastName.Contains('*') || advancedFilter.LastName.Contains('?'))
                        {
                            string pattern = advancedFilter.LastName.Replace("*", ".*").Replace("?", ".{1}");
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.LastName != null && Regex.IsMatch(vk.LastName, pattern));
                        }
                        else
                        {
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.LastName != null && vk.LastName.Equals(advancedFilter.LastName));
                        }
                    }

                    //City
                    advancedFilter.City = advancedFilter.City?.Trim();
                    if (!string.IsNullOrEmpty(advancedFilter.City))
                    {
                        //in pure Linq going for Regex
                        if (advancedFilter.City.Contains('*') || advancedFilter.City.Contains('?'))
                        {
                            string pattern = advancedFilter.City.Replace("*", ".*").Replace("?", ".{1}");
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.City != null && Regex.IsMatch(vk.City, pattern));
                        }
                        else
                        {
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.City != null && vk.City.Equals(advancedFilter.City));
                        }
                    }

                    //Country
                    advancedFilter.Country = advancedFilter.Country?.Trim();
                    if (!string.IsNullOrEmpty(advancedFilter.Country))
                    {
                        //in pure Linq going for Regex
                        if (advancedFilter.Country.Contains('*') || advancedFilter.Country.Contains('?'))
                        {
                            string pattern = advancedFilter.Country.Replace("*", ".*").Replace("?", ".{1}");
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.Country != null && Regex.IsMatch(vk.Country, pattern));
                        }
                        else
                        {
                            iQueryableOfEmployee = iQueryableOfEmployee.Where(
                                vk => vk.Country != null && vk.Country.Equals(advancedFilter.Country));
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }

    return iQueryableOfEmployee;
}

6 Conclusion

The full example code project can be downloaded at GitHub [99].

7 References

[1] https://datatables.net/

[99] https://github.com/MarkPelf/ASPNET8UsingDataTablesNet

0
Subscribe to my newsletter

Read articles from Mark Pelf directly inside your inbox. Subscribe to the newsletter, and don't miss out.

ASP.NETDatatables

Written by

Mark Pelf
Mark Pelf

A Software Engineer from Belgrade, Serbia.