Server-Side Sorting

In this module we are going to piggy back on the architecture we have set up for server-side paging to implement server-side sorting.

In module 23, MatPaginator & PageEvent: Custom Server-Side Paging, we implemented the server side paging in the Vehicles controller. Then we took it one step further and broke out that paging logic into a separate class called ApiResult. We noted that ApiResult was basically a paging service we used to loosely couple our paging logic from our controller logic. This way we can isolate both components for unit testing.

In module 24, Unit Testing: Custom Server-Side Paging, we took advantage of our new architecture improvements and unit tested our ApiResult and Vehicle Controller, and also our extension helper to convert Vehicles to VehicleDTOs.

Now, that we have done the work and taken the time to properly decouple our components, we should be able to add sorting to our ApiResult class fairly quickly. Fingers crossed though. You never really know what challenges will rear their ugly heads until you actually start programming.

At any rate, let’s get started.

Install Dynamic LINQ

We’re going to get into updating the ApiResult class in a moment. But first we are going to install the System.Linq.Dynamic.Core package to make it a little easier to work with LINQ queries to sort our data.

Open a command prompt, navigate to the FredsCarsAPI project and run the following command to install Dynamic LINQ.

dotnet add package System.Linq.Dynamic.Core --version 1.3.7

Modify the ApiResult class

Open the ApiResult.cs file and make the following modifications in bold blue font.

FredsCarsAPI/Data/ApiResult.cs

using Microsoft.EntityFrameworkCore;
using System.Reflection;
using System.Linq.Dynamic.Core;

namespace FredsCarsAPI.Data
{
    public class ApiResult<T>
    {
        private ApiResult(
            List<T> data,
            int count,
            int pageIndex,
            int pageSize,
            string? sortColumn,
            string? sortOrder)
        {
            Data = data;
            PageIndex = pageIndex;
            PageSize = pageSize;
            TotalCount = count;
            TotalPages = (int)Math.Ceiling(count / (double)pageSize);
            SortColumn = sortColumn;
            SortOrder = sortOrder;
        }

        // factory method
        public static async Task<ApiResult<T>> CreatAsync(
            IQueryable<T> source,
            int pageIndex,
            int pageSize,
            string? sortColumn = null,
            string? sortOrder = null
        )
        {
            var count = await source.CountAsync();

            // sorting
            if (!string.IsNullOrEmpty(sortColumn)
                && IsValidProperty(sortColumn))
            {
                sortOrder = !string.IsNullOrEmpty(sortOrder)
                    && sortOrder.ToUpper() == "ASC"
                    ? "ASC"
                    : "DESC";

                // Dynamic LINQ Query
                source = source.OrderBy(
                    string.Format(
                        "{0} {1}",
                        sortColumn,
                        sortOrder)
                    );
            }

            // paging
            source = source
                .Skip(pageIndex * pageSize)
                .Take(pageSize);

            var data = await source.ToListAsync();

            return new ApiResult<T>(
                data,
                count,
                pageIndex,
                pageSize,
                sortColumn,
                sortOrder
            );
        }

        // SQL Injection Guard
        public static bool IsValidProperty(
            string propertyName,
            bool throwExceptionIfNotFound = true)
        {
            var prop = typeof(T).GetProperty(
                propertyName,
                BindingFlags.IgnoreCase |
                BindingFlags.Public |
                BindingFlags.Instance);
            if (prop == null && throwExceptionIfNotFound)
                throw new NotSupportedException(
                    string.Format(
                        $"ERROR: Property '{propertyName}' does not exist.")
                );
            return prop != null;
        }

        public List<T> Data { get; private set; }
        public int PageIndex { get; private set; }
        public int PageSize { get; private set; }
        public string? SortColumn { get; set; }
        public string? SortOrder { get; set; }
        // total record count
        public int TotalCount { get; private set; }
        public int TotalPages { get; private set; }
    }
}

Let’s inspect the modified code above.

First let’s note that we added two properties at the bottom of the ApiResult class; SortColumn and SortOrder.

At the top of the file we have two new using statements to bring in Reflection and Dynamic LINQ.

using System.Reflection;
using System.Linq.Dynamic.Core;

We already mentioned Dynamic LINQ will make it easier to work with our sorting via LINQ queries. But this style will leave us vulnerable to some security risks and Reflection is going to help us deal with that. We’ll get more into these details as we look through the code.

In the constructor we added two incoming parameters; sortColumn and sortOrder both of type nullable string. And in the body of the constructor we set the two new class properties, SortColumn and SortOrder to these two incoming parameters.

Next, let’s look at the factory method our client-code (Angular) calls, CreateAsync().

First the incoming parameter list for this method also has two new additions; sortColumn and sortOrder also of type nullable string.

Then, We get our count of total records in the usual way.

Next, comes our new sorting logic. We have added the sorting logic before the paging logic because we want to do any sorting before the paging. If we did the paging first, and fetched say 10 records out of 100 with a page index of 5, we would get records with IDs 51 to 60 and just sort that set. What we want to do is first sort all the data and then grab a page from that.

We put all of our sorting logic in an if statement and execute sorting only if the incoming sortColumn parameter is not null or empty and the sortColumn value represents a valid property of the generic T class of ApiResult; in this case Vehicles.

Whenever the if statement is true we proceed to modify the incoming sortOrder parameter. If sortOrder is not null or empty and its value is either “asc” or “ASC” (or any combinations of upper and lower case as in “aSc”) then we set sortOrder to “ASC”. If the the sortOrder value is anything else we set it to “DESC”. Anything else could be “asc”, “”, or undefined.

Next we use the incoming sortColumn parameter value and the modified sortOrder value to modify the IQueryable source parameter by using the IQueryable<T>.OrderBy() method with Dynamic LINQ.

source = source.OrderBy(
	string.Format(
		"{0} {1}",
		sortColumn,
		sortOrder)
	);

Using Dynamic LINQ

Let’s talk quickly about the specific problem Dynamic LINQ solves for us here.

When we use regular LINQ from the System.Linq namespace, we are using LINQ with strongly typed classes like Vehicle. The problem we would have in the ApiResult class is that we don’t know what the type T is going to be. It could be Vehicles, VehicleType, or UserAdmin.

So, the possible properties to sort by could be Make, Model and Vin, or it could be Id, and Name. It is unknown until runtime.

This is where Dynamic LINQ comes in from the System.Linq.Dynamic.Core namespace.

In the Dynamic LINQ version of the OrderBy() method above, we pass a formatted string and two parameters to fill the placeholders (rather than a lambda expression like we do for a regular LINQ OrderBy() method).

"{0} {1}", sortColumn, sortOrder

This will end up looking something like “Name ASC”.

At the end of the factory method we return a new instance of ApiResult as usual but now we add the new sortColumn and sortOrder parameters to the ApiResult constructor call.

You can read about Dynamic LINQ and find plenty of online examples at https://dynamic-linq.net.

Dealing with SQL Injection

Let’s take another look at the beginning of our sorting logic; specifically the if statement:

if (!string.IsNullOrEmpty(sortColumn)
	&& IsValidProperty(sortColumn))

Again, we only execute the sorting logic if the sortColumn is not null or empty AND sortColumn is a valid property of T. But what is this all about? Well, now that we are building up our sort phrase by concatenating together string values from the client, we are open to a security risk called SQL Injection. SQL Injection is where an experienced or malicious hacker intentionally or inadvertently can send dangerous values through an http request to incoming parameters such as our sortColumn and sortOrder parameters.

Let’s take a closer look at the IsValidProperty() method our main sorting if statement calls.

// SQL Injection Guard
public static bool IsValidProperty(
	string propertyName,
	bool throwExceptionIfNotFound = true)
{
	var prop = typeof(T).GetProperty(
		propertyName,
		BindingFlags.IgnoreCase |
		BindingFlags.Public |
		BindingFlags.Instance);
	if (prop == null && throwExceptionIfNotFound)
		throw new NotSupportedException(
			string.Format(
				$"ERROR: Property '{propertyName}' does not exist.")
		);
	return prop != null;
}

The IsValidProperty() method above takes in two parameters; propertyName and the optional parameter throwExceptionIfNotFound with a default value of true.

Using Reflection

Next we call the typeof(T).GetProperty() method from the System.Reflection namespace and pass to it the incoming propertyName parameter. In the GetProperty() method we specify that the case of the property name to look for should be ignored and to search for public instance properties. (Rather than static properties). The result is then assigned to a variable named prop.

Next, if prop is null and throwExceptionIfNotFound is true we throw an exception with a string formatted message specifying the property that does not exist.

If throwExceptionIfNotFound is set to false we just return as a boolean whether prop is null or not. If prop is not equal to null than it is a valid property name. If prop is equal to null then it is an invalid property name.

You can read more about SQL Injection at W3Schools here: https://www.w3schools.com/sql/sql_injection.asp.

Modify the Vehicles controller

Open the VehiclesController.cs file and make the following modifications in bold blue font.

FredsCarsAPI/Controllers/VehiclesController.cs

/*** existing code ***/

        [HttpGet]
        public async Task<ApiResult<VehicleDTO>> GetVehicles(
            int pageIndex = 0, int pageSize = 10,
            string? sortColumn = null,
            string? sortOrder = null)
        {
            // get Vehicles page
            var dataQuery = _vehicleRepo.Vehicles.AsNoTracking()
                .Include(v => v.VehicleType)
                .ConvertVehiclesToDTOs();

            return await ApiResult<VehicleDTO>.CreatAsync(
                dataQuery,
                pageIndex,
                pageSize,
                sortColumn,
                sortOrder);
        }
    }
}

Again, once we have taken the time to think through the architecture and logic in ApiResult for sorting, the Vehicle controller changes above are very simple. We just add the sortColumn and sortOrder parameters in addition to the paging parameters to the HTTP GET call and pass them through to the ApiResult factory method.

Modify the Vehicles component TypeScript

Open the vehicles.component.ts file and make the modifications below in bold blue font.

FredsCars/src/app/vehicles/vehicles.component.ts

import { Component, OnInit, ViewChild } from '@angular/core';
import { HttpClient, HttpParams } from '@angular/common/http';
import { Vehicle } from './vehicle';
import { environment } from '../../environments/environment.development';
import { MatTableDataSource } from '@angular/material/table';
import { MatPaginator, PageEvent } from '@angular/material/paginator';
import { MatSort } from '@angular/material/sort';
import { Category, VehicleType } from './vehicleType';

@Component({
  selector: 'app-vehicles',
  templateUrl: './vehicles.component.html',
  styleUrls: ['./vehicles.component.scss']
})
export class VehiclesComponent implements OnInit {
  // debug JSON var for HTML
  public json = JSON;
  public vehicles!: MatTableDataSource<Vehicle>
  
  columnsToDisplay: string[] = ['id', 'vehicleType', 'status', 'year', 'make', 'model', 'color', 'price'];

  // new variables for default page/sort values
  defaultPageIndex: number = 0;
  defaultPageSize: number = 10;
  public defaultSortColumn: string = "id";
  public defaultSortOrder: "asc" | "desc" = "asc";

  @ViewChild(MatPaginator) paginator!: MatPaginator;
  // uncomment sort variable
  @ViewChild(MatSort) sort!: MatSort;

  // Category Search Checkbox client-model
  categoryAll: Category = {
    id: 0,
    name: 'All',
    selected: false,
    categories: []
  };
  allCategoriesSelected: boolean = false;

  constructor(private http: HttpClient) {
    // Instantiate the MatTableDataSource.
    this.vehicles = new MatTableDataSource<Vehicle>();
  }

  ngOnInit() {
    // moved get vehicles to load data
    //  so MatSort can call multiple times as user
    //  clicks a new sort column.
    this.loadData();

    // get vehcileTypes and transform into search categories
    //  for sidenav (leave in ngOnitInit since we only need to
    //  do this once)
    this.http.get<VehicleType[]>(environment.baseUrl + 'api/vehicleTypes').subscribe(result => {
      result.forEach(vt => {
        this.categoryAll.categories?.push(
          {
            id: vt.id,
            name: vt.name,
            selected: false
          }
        );
      })
    }, error => console.error(error));
  }

  loadData() {
    // get vehicles
    var pageEvent = new PageEvent();
    pageEvent.pageIndex = 0;
    pageEvent.pageSize = 5;
    this.getVehicleData(pageEvent);
  }
  
  getVehicleData(event: PageEvent) {
    // get rid of third empty click state for MatSort
    if (this.sort && this.sort.direction == "") {
      this.sort.direction = "asc";
    }

    var url = environment.baseUrl + 'api/vehicles';
    var params = new HttpParams()
      .set("pageIndex", event.pageIndex.toString())
      .set("pageSize", event.pageSize.toString())
      .set("sortColumn", (this.sort)
        ? this.sort.active
        : this.defaultSortColumn)
      .set("sortOrder", (this.sort)
        ? this.sort.direction
        : this.defaultSortOrder);
    this.http.get<any>(url, { params })
      .subscribe(result => {
        this.paginator.length = result.totalCount;
        this.paginator.pageIndex = result.pageIndex;
        this.paginator.pageSize = result.pageSize;
        this.vehicles.data = result.data
      }, error => console.error(error));
  }

  /*** Category Search Checkbox methods ***/
  public someSearchCategoriesSelected(): boolean {
    return this.categoryAll.categories!.filter(cat =>
      cat.selected).length > 0 && !this.allCategoriesSelected;
  }

  setAllCategoriesSelected(checked: boolean) {
    this.allCategoriesSelected = checked;
    this.categoryAll.selected = checked;
    this.categoryAll.categories!.forEach(cat =>
      (cat.selected = checked));
  }

  updateAllCategoriesSelected() {
    var allSelected: boolean =
      this.categoryAll.categories!.every(cat =>
        cat.selected);

    this.allCategoriesSelected = allSelected;
    this.categoryAll.selected = allSelected;
  }
  /*** End Category Search Checkbox methods ***/
}

Well, we already have a reference to the MatSortModule module in angular-material-module.ts and we also have the import statement in the top of the vehicles.component.ts file for the MatSort component both leftover from when we had default sorting implemented.

import { MatSort } from '@angular/material/sort';

So, now we can bring back references to and work with MatSort in the Vehicles component TypeScript and HTML.

The first real modification we really made was to add four new variables with default paging and sort values.

Next we uncomment the ViewChild property for the MatSort reference variable called sort.

We have have also broken out the call to getVehicleData() from ngOnInit() and moved it to a new loadData() method. So, in ngOnInit() we now call loadData().

The next modification is the new loadData() method itself. Now, anytime the user clicks a sort column, loadData() will be called, create a new PageEvent, and pass it to getVehicleData().

Next, we made a couple of modifications to getVehicleData() itself. First of all, we deal with that third state click on sort columns we talked about back in default client-side sorting where on the third click instead of going from descending back to ascending, it puts the data back to its original state which is not sorted at all. At the top of the getVehicleData() method, we now added an if statement which if the sort variable is not null, meaning the user has clicked a sort column, and the sort.value direction is equal to an empty string, we will manually set the sort.direction back to “asc”.

In the final modification, we add the sortColumn and sortOrder parameters to the HttpParams object. If the user has clicked on a sort column, then we set the SortColumn Http parameter to the sort.active value which will be the name of the column to sort by. Otherwise, we use the defaultSortColumn value.
And also if the user has clicked on a sort column, we set the sortOrder HttpParams variable to the sort.direction value which should be either “asc” or “desc”. Otherwise we set it to the value of defaultSortOrder.

Modify the Vehicles HTML Template

Open the vehicles.component.html file and make the modifications shown below.

FredsCars/src/app/vehicles/vehicles.component.html

/*** existing code ***/

    <table mat-table [dataSource]="vehicles"
           matSort
           [matSortActive]="defaultSortColumn"
           [matSortDirection]="defaultSortOrder"
           (matSortChange)="loadData()"
           *ngIf="vehicles.data.length > 0">

      <ng-container matColumnDef="vehicleType">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Category</th>
        <td mat-cell *matCellDef="let item">
          <b>{{ item.vehicleType }}</b>
        </td>
      </ng-container>

      <ng-container matColumnDef="id">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>ID</th>
        <td mat-cell *matCellDef="let item">
          {{ item.id }}
        </td>
      </ng-container>

      <ng-container matColumnDef="status">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Status</th>
        <td mat-cell *matCellDef="let item">
          {{ item.status }}
        </td>
      </ng-container>

      <ng-container matColumnDef="year">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Year</th>
        <td mat-cell *matCellDef="let item">
          {{ item.year }}
        </td>
      </ng-container>

      <ng-container matColumnDef="make">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Make</th>
        <td mat-cell *matCellDef="let item">
          {{ item.make }}
        </td>
      </ng-container>

      <ng-container matColumnDef="model">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Model</th>
        <td mat-cell *matCellDef="let item">
          {{ item.model }}
        </td>
      </ng-container>

      <ng-container matColumnDef="color">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Color</th>
        <td mat-cell *matCellDef="let item">
          {{ item.color }}
        </td>
      </ng-container>

      <ng-container matColumnDef="price">
        <th mat-header-cell *matHeaderCellDef mat-sort-header>Price</th>
        <td mat-cell *matCellDef="let item">
          <b>{{ item.price | currency:"USD" }}</b>
        </td>
      </ng-container>

/*** existing code ***/

Again, we already have the matSort attribute on the table element we brought in during the default-client side paging and sorting module.

We added to the table element the matSortActive and matSortDirection MatTable attributes and bound them to our new defaultSortColumn and defaultSortOrder variables from the TypeScript.

We also added to the table the matSortChange event and wired it to call the new loadData() method anytime a user clicks on a sort column.

And again the mat-sort-header attribute is still on all of our th elements within all of our column definitions left over from default paging and sorting. So, the columns will know to be sortable.

Run the project

At this point if you run the project and navigate to the Vehicles component, your browser should look similar to the following.

In the above screenshot we can see if we open the web development tools with the F12 key, click the Network tab, and inspect the api request, the pageIndex is 0, pageSize is 5, sortColumn is “id”, and sortOrder is “asc”. This setup all starts in ngOnInit() which calls loadData(). loadData() sets up the page event values and calls getVehicleData(). getDataVehicles() then works out the sorting values and makes the HTTP GET call.

Postman check one

If we grab the values from the api network request, and punch them into a Postman request we can test that the data in the browser matches what the API results should look like.

Here is the full JSON response.

{
    "data": [
        {
            "id": 1,
            "status": "New",
            "year": "2021",
            "make": "Dodge",
            "model": "Challenger",
            "color": "Frostbite",
            "price": 64164,
            "vin": "2C3CDZFJ8MH631199",
            "vehicleType": "Car"
        },
        {
            "id": 2,
            "status": "Used",
            "year": "2020",
            "make": "Ford",
            "model": "Escape",
            "color": "Oxford White",
            "price": 22999,
            "vin": "1FMCU0F63LUC25826",
            "vehicleType": "Car"
        },
        {
            "id": 3,
            "status": "New",
            "year": "2021",
            "make": "Dodge",
            "model": "Durange",
            "color": "Black",
            "price": 50557,
            "vin": "1C4RDJDG5MC837730",
            "vehicleType": "Car"
        },
        {
            "id": 4,
            "status": "New",
            "year": "2021",
            "make": "Nissan",
            "model": "Niro",
            "color": "Blue",
            "price": 24960,
            "vin": "2XYZT67JTF24AZG856",
            "vehicleType": "Car"
        },
        {
            "id": 5,
            "status": "New",
            "year": "2021",
            "make": "Kia",
            "model": "Stinger",
            "color": "Gray",
            "price": 36090,
            "vin": "6FG146B89624AZ7952",
            "vehicleType": "Car"
        }
    ],
    "pageIndex": 0,
    "pageSize": 5,
    "sortColumn": "id",
    "sortOrder": "ASC",
    "totalCount": 12,
    "totalPages": 3
}

The results are in fact starting at page index 0 and grabing the first 5 records sorted by “id”.

Sanity Check One

You can also query the expected results right from SQL Server if you really want to dig deep and make absolutely sure each page in your manual user testing for paging and sorting is correct. The following screen shot is a query against the actual FredsCars database in SQL Server using SQL Server Management Studio.

Back in the old days before unit testing and integration testing we use call this kind of digging and paying attention to detail a sanity check.

Sort Ascending in the application

Next, click on the Status column and you’ll see an ascending icon to the right of the column header and the results will be sorted by status ascending. ‘N’ in New comes before ‘U’ in used so all we see right now are new vehicles.

Postman Test 2

JSON Results

{
    "data": [
        {
            "id": 3,
            "status": "New",
            "year": "2021",
            "make": "Dodge",
            "model": "Durange",
            "color": "Black",
            "price": 50557,
            "vin": "1C4RDJDG5MC837730",
            "vehicleType": "Car"
        },
        {
            "id": 4,
            "status": "New",
            "year": "2021",
            "make": "Nissan",
            "model": "Niro",
            "color": "Blue",
            "price": 24960,
            "vin": "2XYZT67JTF24AZG856",
            "vehicleType": "Car"
        },
        {
            "id": 5,
            "status": "New",
            "year": "2021",
            "make": "Kia",
            "model": "Stinger",
            "color": "Gray",
            "price": 36090,
            "vin": "6FG146B89624AZ7952",
            "vehicleType": "Car"
        },
        {
            "id": 6,
            "status": "New",
            "year": "2021",
            "make": "Kia",
            "model": "Stinger",
            "color": "Gray",
            "price": 36090,
            "vin": "6FG146B89624AZ7952",
            "vehicleType": "Car"
        },
        {
            "id": 7,
            "status": "New",
            "year": "2022",
            "make": "Ram",
            "model": "Crew Cab",
            "color": "Black",
            "price": 68400,
            "vin": "3C6UR5DL8NG157035",
            "vehicleType": "Truck"
        }
    ],
    "pageIndex": 0,
    "pageSize": 5,
    "sortColumn": "status",
    "sortOrder": "ASC",
    "totalCount": 12,
    "totalPages": 3
}

Sanity Test 2

This is the SQL Query I used.

WITH SortedVehicles AS
(
SELECT 
    RowNum = ROW_NUMBER() OVER (ORDER BY Status), 
    ID,
	(Case
		WHEN Status = 0 Then 'New'
		WHEN Status = 1 Then 'Used'
	END) as 'Status'
FROM Vehicles
)
SELECT *
FROM SortedVehicles
WHERE RowNum BETWEEN 1 AND 5

Getting a row number back in SQL is a little complicated but here I am just selecting Id and Status from the Vehicles table and transforming a value of 0 to the string ‘New’ and a value of 1 to the value ‘Used’. Remember in our C# model we have an enum for status and this is how Entity Framework stores enums in the database. If you recall we had to configure our API to convert the enum number values to string values with the JSON options in program.cs. At any rate I select Id, and the status for each record and assign each record a row number. I sort by status and then only show the top 5 out of the results using the BETWEEN operator. Here are the results.