In this module we are going to once again try to piggy back off of the work we’ve done for paging, sorting, and filtering in order to add in the Category Search in the SideNav.
ApiResult Behavior Analysis
The order we are currently modifying and building up the IQueryable source variable in the ApiResult class begins with filtering, than sorting, and lastly paging. This order is important because the filtered set is the set we want to sort and page. And it only makes sense to sort first before gettting the correct page because otherwise we would grab a page of unsorted data and only sort that page.
In this module we are going to begin to add in the Advanced Search starting with searching by Vehicle Category; car, truck, or jeep.
In this application we will never perform the (filter) Quick Search and Advanced Search together. It will be one or the other. If we are performing an Advanced Search from the SideNav it needs to go up front just like the filter search does.
So the order will be either:
- Filter
- Sort
- Page
OR
- Search
- Sort
- Page
Modify the ApiResult class
Open the ApiResult.cs file and modify it with the contents shown below 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,
bool searched,
int pageIndex,
int pageSize
)
{
Data = data;
PageIndex = pageIndex;
PageSize = pageSize;
Searched = searched;
TotalCount = count;
TotalPages = (int)Math.Ceiling(count / (double)pageSize);
}
// factory method
public static async Task<ApiResult<T>> CreatAsync(
IQueryable<T> source,
int pageIndex,
int pageSize,
string? sortColumn = null,
string? sortOrder = null,
string? filterColumns = null,
string? filterValue = null,
string? searchColumns = null,
string? searchValues = null
)
{
// filtering
string[]? filterColumnArray = null;
if (!string.IsNullOrEmpty(filterColumns))
{
filterColumnArray = filterColumns.Split(',');
}
if (!string.IsNullOrEmpty(filterColumns)
&& !string.IsNullOrEmpty(filterValue)
&& filterColumnArray.ToList().All(fc => IsValidProperty(fc))
)
{
string queryString = string.Empty;
foreach (var fc in filterColumnArray)
{
queryString += string.Format("{0}.ToUpper().StartsWith(@0) || ",
fc);
}
queryString = queryString.Substring(0, queryString.Length - 4);
source = source.Where(queryString, filterValue.ToUpper());
}
// Search
// -- Categories
bool searching = false;
string[]? searchColumnArray = null;
string[]? searchValuesArray = null;
if (!string.IsNullOrEmpty(searchColumns) &&
!string.IsNullOrEmpty(searchValues))
{
searchColumnArray = searchColumns.Split(',');
searchValuesArray = searchValues.Split(',');
searching = true;
}
if (searching == true
&& searchColumnArray.ToList().All(sc => IsValidProperty(sc))
)
{
string queryString = string.Empty;
foreach (var sc in searchColumnArray)
{
foreach (var sv in searchValuesArray)
{
queryString += $"{sc} == \"{sv}\" || ";
}
}
queryString = queryString.Substring(0, queryString.Length - 4);
source = source.Where(queryString);
}
// get record count
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,
searching,
pageIndex,
pageSize
);
}
// 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 bool Searched { get; private set; }
// total record count
public int TotalCount { get; private set; }
public int TotalPages { get; private set; }
}
}
In the code above we are once again extending the ApiResult class with some generic ability; this time to search on columns by values sent to it via factory and constructor parameters.
Let’s start by looking at the properties at the bottom of the file. We have added a property called Searched of type bool. If you remember when we were unit testing sorting we found that we really didn’t need to capture sorting information and send it back down to the client to track like we did for paging. The MatSort component seemed to do a pretty good job of tracking its own state with a little bit of careful tweaking on our part. And we carried this notion forward with filtering. We didn’t need to return any filtering information to the client via properties either. However, it turns out that tracking whether the user has just completed a search and returning that information to the client can help if the next user interaction is a click on a sorting column as we shall see when we walk through TypeScript and HTML.
At the top of the file in the constructor, we take in a parameter called searched of type bool. In the body of the constructor we set the class Searched property we just talked about to the value of the incoming searched parameter. This will be passed up from the CreatAsync() class factory method.
The class CreatAsync() factory method takes in two new parameters; search Columns and searchValues both of type nullable string.
In the body of CreatAsync() factory method, the new search code comes after the filtering and before the sorting as discussed earlier.
The first thing we do is declare a few local variables to make life easier.
searching: bool, initialized to false.searchColumnArray:nullable string array.searchValuesArray:nullable string array.
Next we have an if statement checking that the strings searchColumns and searchValues are not null or empty. If this is true we split the incoming comma delineated strings of columns and values into their matching string array counterparts; searchColumnArray and searchValuesArray. And while we are here we set searching to true so we don’t have to keep performing the same if condition check over and over again.
If searching has been set to true we perform our SQL Injection check for every column in searchColumnArray within another if statement with a short circuiting logical AND operator.
Next, within the body of the if statement, we set up a queryString variable to build up the search string for a Dynamic LINQ query. To accomplish this we have a nested foreach loop. The outer loop iterates through the columns to search by and the inner loop iterates through the values to search on. For each iteration of the inner loop, we add in another C# logical OR condition to the query string with the logical OR operator, ‘||‘ also using string interpolation.
Finally, we strip off the extra OR operator at the end of the querystring after exiting the forloop, ‘||’, and pass the querystring as a parameter to the Dynamic LINQ version of the IQueryable<T>.Where() method as we are quite used to doing by now.
Modify the Vehicles controller
Open the VehiclesController.cs file and modify its contents with the code shown below 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,
string? filterColumns = null,
string? filterValue = null,
string? searchColumns = null,
string? searchValues = null)
{
// get Vehicles page
var dataQuery = _vehicleRepo.Vehicles.AsNoTracking()
.Include(v => v.VehicleType)
.ConvertVehiclesToDTOs();
return await ApiResult<VehicleDTO>.CreatAsync(
dataQuery,
pageIndex,
pageSize,
sortColumn,
sortOrder,
filterColumns,
filterValue,
searchColumns,
searchValues);
}
}
}
In the controller code above we simply added two incoming parameters to the GetVehicles() HTTP GET method and pass them on up to the ApiResult factory method.
searchColumns: type of string – set to nullsearchValues: type of string – set to null
Test the API
Postman test 1
Open Postman, create a GET request with the following URL and click the Send button.
https://localhost:40443/api/Vehicles?pageIndex=0&pageSize=5&sortColumn=id&sortOrder=asc&searchValues=Truck,Jeep&searchColumns=vehicleType
In the above request we are asking for the first page (pageIndex of 0) with a page size of 5, sorted by id in ascending order, and we want to search by the VehcileType (Categories) column on the values Truck and Jeep.
Here is the complete JSON response.
{
"data": [
{
"id": 7,
"status": "New",
"year": "2022",
"make": "Ram",
"model": "Crew Cab",
"color": "Black",
"price": 68400,
"vin": "3C6UR5DL8NG157035",
"vehicleType": "Truck"
},
{
"id": 8,
"status": "Used",
"year": "2017",
"make": "Ram",
"model": "Crew Cab",
"color": "Red",
"price": 33000,
"vin": "1C6RR7PT0HS814596",
"vehicleType": "Truck"
},
{
"id": 9,
"status": "New",
"year": "2022",
"make": "Jeep",
"model": "Compass",
"color": "White",
"price": 34980,
"vin": "3C4NJDFB5NT114024",
"vehicleType": "Jeep"
},
{
"id": 10,
"status": "New",
"year": "2022",
"make": "Jeep",
"model": "Compass",
"color": "Red",
"price": 39275,
"vin": "3C4NJDCB1NT118172",
"vehicleType": "Jeep"
},
{
"id": 11,
"status": "New",
"year": "2022",
"make": "Jeep",
"model": "Grand Cherokee",
"color": "Pearlcoat",
"price": 53575,
"vin": "1C4RJKBG5M8201121",
"vehicleType": "Jeep"
}
],
"pageIndex": 0,
"pageSize": 5,
"searched": true,
"totalCount": 6,
"totalPages": 2
}In the above JSON result every Vehicle is either a Truck or a Jeep and we have sent a searched property back to the client with a value of true. This will come in handy when we modify the TypeScript and HTML. Also notice totalPages is 2. Keep this in mind for the second Postman test.
Postman test 2
In Postman create a second GET request with the following URL and click the Send button.
https://localhost:40443/api/Vehicles?pageIndex=1&pageSize=5&sortColumn=id&sortOrder=asc&searchValues=Truck,Jeep&searchColumns=vehicleType
In the above URL we simply changed pageIndex to 1 in order to ask for the second page of results. The following is the JSON result.
{
"data": [
{
"id": 12,
"status": "New",
"year": "2021",
"make": "Jeep",
"model": "Wrangler Sport S",
"color": "Green",
"price": 40940,
"vin": "1C4GJXAN0MW856433",
"vehicleType": "Jeep"
}
],
"pageIndex": 1,
"pageSize": 5,
"searched": true,
"totalCount": 6,
"totalPages": 2
}We get back one more Vehicle of category Jeep to make the sixth record and that matches the totalCount in the JSON result of 6. These two Postman API tests should match what we see in the browser Web Development tools if we inspect the Network request once we finish up with the TypeScript and HTML and run the application.
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
/*** existing code ***/
@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'];
defaultPageIndex: number = 0;
defaultPageSize: number = 10;
public defaultSortColumn: string = "id";
public defaultSortOrder: "asc" | "desc" = "asc";
defaultFilterColumns: string = "make,model";
filterValue?: string | null;
searchColumns?: string | null;
searchValues?: string;
search: boolean = false;
@ViewChild(MatPaginator) paginator!: MatPaginator;
@ViewChild(MatSort) sort!: MatSort;
// Category Search Checkbox client-model
categoryAll: Category = {
id: 0,
name: 'All',
selected: true,
categories: []
};
allCategoriesSelected: boolean = true;
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: true
}
);
})
}, error => console.error(error));
}
loadData(filterValue?: string | null, search: boolean = false) {
// get vehicles
var pageEvent = new PageEvent();
pageEvent.pageIndex = 0;
pageEvent.pageSize = 5;
this.filterValue = filterValue;
this.search = search;
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);
if (this.filterValue) {
params = params
.set("filterColumns", this.defaultFilterColumns)
.set("filterValue", this.filterValue);
}
if (this.search) {
// search on categories
let categories: string = "";
if (!(this.categoryAll.selected)
&& this.categoryAll.categories?.some(c => {
return c.selected
}))
{
this.categoryAll.categories?.forEach(c => {
if (c.selected) {
categories += `${c.name},`
}
})
categories = categories.substr(0, categories.length - 1)
params = params
.set("searchValues", categories)
.set("searchColumns", "vehicleType");
}
// search on other columns.
// -- here...
}
this.http.get<any>(url, { params })
.subscribe(result => {
this.paginator.length = result.totalCount;
this.paginator.pageIndex = result.pageIndex;
this.paginator.pageSize = result.pageSize;
this.search = result.searched;
this.vehicles.data = result.data;
}, error => console.error(error));
}
/*** existing code ***/Let’s walk through the TypeScript changes above for the Vehicles component.
The first thing we did was add three class variables:
searchColumns: string or nullsearchValues: stringsearch: boolean – initialized to false
Next, we add an incoming parameter called search of type boolean to the loadData() method with a default value of false. Then we set the new class search property to the value of the search parameter. If the user is searching then this value will be set to true, our logic further down the line will perform searching, and we will send this boolean value back down to the client in the JSON response.
Finally, we add the search logic to the getVehicleData() method. If the class search property (this.search) is equal to true we perform the search. First we define a local variable called categories of type string. We are going to use this string to build up the categories we want to search as a comma delineated string (car and/or truck and/or jeep) and use it’s value to set the searchValues HTTP parameter.
Once we have the categories string defined and initialized to an empty string, we peform an if condition check that relies on our Angular live categories model for our checkboxes in the SideNav we set up way back in module 20, MatCheckbox: Category Search UI, where we set up the category model, interface, and categories HTML.
Recall the data model for categories we are working with from the top of this TypeScript file:
// Category Search Checkbox client-model
categoryAll: Category = {
id: 0,
name: 'All',
selected: true,
categories: []
};And, the interface this is based off of:
FredsCars/src/app/vehicles/vehicleType.ts
export interface VehicleType {
id: number;
name: string;
}
export interface Category {
id: number;
name: string;
selected: boolean;
categories?: Category[]
}This is the if statement we are working through right now:
if (!(this.categoryAll.selected)
&& this.categoryAll.categories?.some(c => {
return c.selected
}))The first part of this if statement checks that the All Categories checkbox in the SideNav Advanced Search UI is not checked. If All Categories is checked we don’t need to weed any categories out of our search and we can ignore the categories part of the search. The second part of the if statement checks that one or more subcategories are checked. So, to enter the body of the if block, we need to have one or more subcategory checkboxes checked, but not all of them.
If the if condition passes we enter the body of the if block and start building up the categories for the searchValues.
this.categoryAll.categories?.forEach(c => {
if (c.selected) {
categories += `${c.name},`
}
})Here, we loop through each subcategory (car, truck, or jeep) and if their selected property is true in the data model we add their name and a comma to the categories string. Then we strip off the trailing comma.
The rest of the categories section for the search just adds two HTTP parameters.
searchValues: gets set to the newly built up categories string.searchColumns: gets set to the literal value “vehicleType”.- We can hard code vehicleType here because we are working in the TypeScript file specific to Vehicles.
Modify the Vehicles component HTML
Open the vehicles.component.html file and make the modifications shown below in bold blue font.
FredsCars/src/app/vehicles/vehicles.component.html
<mat-sidenav-container>
<mat-sidenav mode="side" #sidenav>
<div style="margin-right: 10px;">
<p><b>Search Panel</b></p>
<hr />
<p><b>Categories</b></p>
<mat-checkbox color="primary"
[checked]="allCategoriesSelected"
[indeterminate]="someSearchCategoriesSelected()"
(change)="setAllCategoriesSelected($event.checked)">
{{ categoryAll.name }}
</mat-checkbox>
<ul>
<li *ngFor="let category of categoryAll.categories">
<mat-checkbox color="accent"
[(ngModel)]="category.selected"
(ngModelChange)="updateAllCategoriesSelected()">
{{category.name}}
</mat-checkbox>
</li>
</ul>
<button mat-raised-button color="primary"
(click)="loadData(null, true)">
Search
</button>
</div>
<!-- debug -->
<!--
<div style="margin-top: 40px;">
<b>this.search:</b><br />
{{ this.search }}<br />
<b>categoryAll:</b><br />
{{ categoryAll.selected }}<br />
<b>Categories:</b>
<ul>
<li *ngFor="let category of categoryAll.categories">
{{category.name}}: {{ category.selected }}
</li>
</ul>
<b>categories (stringified):</b><br />
{{ categoryAll.categories | json }}
</div>
-->
<!-- debug -->
</mat-sidenav>
<mat-sidenav-content>
<p *ngIf="!(vehicles.data.length > 0)">
<mat-spinner style="margin: 0 auto;"></mat-spinner>
</p>
<div *ngIf="vehicles.data.length > 0"
style="margin-bottom: 10px;">
<button mat-fab extended
color="primary"
(click)="sidenav.toggle()">
<mat-icon>
search
</mat-icon>
Advanced Search
</button>
</div>
<mat-form-field [ngClass]="{'hide-results' : vehicles.data.length <= 0}">
<mat-label>Quick Search</mat-label>
<input matInput #filter (keyup)="loadData(filter.value)"
placeholder="Filter by Make or Model" />
</mat-form-field>
<table mat-table [dataSource]="vehicles"
matSort
[matSortActive]="defaultSortColumn"
[matSortDirection]="defaultSortOrder"
(matSortChange)="loadData(filter.value, this.search)"
*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>
/*** existing code ***/
<!-- No VIN on vehicles page. We will show this on the details page -->
<!-- Header Template -->
<tr mat-header-row *matHeaderRowDef="columnsToDisplay"></tr>
<!-- Row Template -->
<tr mat-row *matRowDef="let row; columns: columnsToDisplay"></tr>
</table>
<mat-paginator (page)="getVehicleData($event)"
[pageSize]="10"
[pageSizeOptions]="[3, 5, 10]"
showFirstLastButtons
[ngClass]="{'hide-results' : vehicles.data.length <= 0}">
</mat-paginator>
</mat-sidenav-content>
</mat-sidenav-container>In the HTML above it only took a few small changes now that we have done all the preparation in the TypeScript file to support the new Search Feature.
At the top of the mat-sidenav element I just changed the Search Header text from “Search” to “Search Panel”.
And at the bottom of the SideNav I added a new Search button using a native button element with the Angular Material mat-raised-button component attribute. The new Search button has an Angular click event wired up to the loadData() method in the TypeScript file. We pass null to the filterValue parameter of loadData() because we are not filtering, and we pass true to the search parameter. Now loadData() will set the TypeScript class search property value to true, call getVehicleData(), and getVehicleData() will know what to do based on the class search property.
In the mat-sidenav-content element I dressed up the search icon a bit to toggle open and closed the SideNav.
The button element containing the search mat-icon element now has a mat-fab attribute to make it a MatFab button and the extended attribute so the text , “Advanced Search” will be within the primary color fill of the MatFab button. The search icon now has a nice full look and is easier to spot sitting above the Quick (filter) search as a way to get to the advanced search as we shall see when we run the web application.
This feature is called Extended fab Buttons and you can read about it here:
https://material.angular.io/components/button/overview#extended-fab-buttons
And finally, we have added the class search property as a parameter to the loadData() method call on the matSortChange event for the MatTable table element.
Run the program
Run the program in debug mode and click the Vehicles button in the top navigation pane. Next click the new Advanced Search button above the Quick Search filter. Your browser should look similar to the following.
Next, we are going to perform a search. But first hit F12 on your keyboard to open the web development tools so we can capture network requests.
Now, in the Search Panel, uncheck the Car checkbox and click Search. Your browser should look similar to the browser screen shown below.
In the screenshot above, we unchecked the Car category so the the All checkbox’s state becomes indeterminate with a dash, ‘-‘ instead of a checkmark or empty box. We click the search button and we get back one page of results, 1 – 5 of 6 as shown in the pager controls rather than our full set of twelve records.
Down in the web dev tools area if we navigate to the Network tab and go to the Headers sub tab, we can see the request URL. This matches up with the first Postman test we did for our API earlier in the module. The request is asking for the first page (pageIndex of 0) with a pageSize of 5, sorted on id in ascending order, and searching by vehicleType (Type is cut off in the screenshot but it is there) on the values Truck and Jeep.
Now in we go to the Preview tab in the web dev tools we can see the JSON results.
In the JSON results shown above I see 2 Rams for “make” which I know are trucks and 3 jeeps. The client knows we have just searched from the JSON searched property, and we have a total count of 6 vehicles so if we go to the next page we should see one more.
Now, in the browser click the next page button.
In the screen shot above you can see in the web dev tools that the pageIndex in the request has been changed to 1 so we are getting the second page with the last result. This matches the second Postman API test we did earlier in the module.
And the complete JSON results are shown below.
Next, click on the Category header to sort the Searched results by category in ascending order.
What’s Next
In this module we wrapped up the functionality of the Vehicles List page with the Advanced Searching feature (although we may come back at a later point and add in some more search criteria).
All we need to do now is add some unit testing for the new feature to keep things nice and tidy and up to date.
Then we’ll be able to move on to other things like learning about Angular Forms, creating edit and details pages along with delete functionality, and possibly using Forms to improve our Search and Filter code.
Let’s move along to the next module and perform our unit tests for Searching by category.