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

//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>
}

<!-- 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>

////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;
}