Modern enterprise applications often deal with large datasets: orders, users, products, or logs. Simple table displays are not enough—users expect powerful filtering capabilities to find exactly what they need. Implementing advanced filtering correctly ensures high performance, maintainability, and a great user experience.
In this article, we will cover a production-ready approach to implementing advanced filtering in Angular frontend with SQL Server backend. The solution includes:
Architecture overview
Filter requirements and design
SQL Server query strategies
Backend implementation in ASP.NET Core
Angular frontend filtering implementation
Best practices for performance, security, and usability
This guide assumes you have solid experience with Angular, ASP.NET Core, and SQL Server.
1. Architecture Overview
Angular Frontend <---- REST API ----> ASP.NET Core Backend <---- SQL Server Database
Responsibilities
Angular Frontend
Display filter UI components (dropdowns, text inputs, date pickers)
Collect filter criteria from the user
Send structured filter requests to the backend
Render filtered results in tables or charts
ASP.NET Core Backend
Receive structured filter requests
Translate filters into dynamic SQL queries or LINQ expressions
Apply pagination, sorting, and security filters
Return filtered data
SQL Server Database
2. Defining Filter Requirements
Before writing code, define what filtering features you need. Typical requirements:
Text search (e.g., product name, customer email)
Exact match (e.g., status = ‘Active’)
Range filtering (e.g., price between 100–500, date between two dates)
Multi-select filters (e.g., categories, tags)
Logical operations (AND / OR between filters)
Pagination and sorting
Example
Show all Active orders from India in the last 30 days with amount > 1000 and product category Electronics or Appliances.
This is more than just a simple query; it needs dynamic query construction.
3. SQL Server Query Design
Dynamic filtering can be implemented using parameterized queries, stored procedures, or LINQ with EF Core.
3.1 Using Parameterized SQL
Example table: Orders
SELECT * FROM Orders
WHERE
(@Status IS NULL OR Status = @Status) AND
(@Country IS NULL OR Country = @Country) AND
(@StartDate IS NULL OR OrderDate >= @StartDate) AND
(@EndDate IS NULL OR OrderDate <= @EndDate) AND
(@MinAmount IS NULL OR Amount >= @MinAmount) AND
(@MaxAmount IS NULL OR Amount <= @MaxAmount) AND
(@CategoryList IS NULL OR Category IN (SELECT value FROM STRING_SPLIT(@CategoryList, ',')))
ORDER BY OrderDate DESC
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;
Key points
Use IS NULL checks to apply filters only when criteria exist
Use STRING_SPLIT for multi-select filters
Apply OFFSET-FETCH for pagination
3.2 Indexing Tips
Index columns that are frequently filtered: Status, Country, OrderDate
Consider composite indexes for common filter combinations
Use covering indexes if query selects only few columns
4. Backend Implementation (ASP.NET Core)
We use Entity Framework Core for ORM and LINQ for dynamic filtering.
4.1 Filter Model
Models/OrderFilter.cs
public class OrderFilter
{
public string? Status { get; set; }
public string? Country { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public decimal? MinAmount { get; set; }
public decimal? MaxAmount { get; set; }
public List<string>? Categories { get; set; }
public string? SearchText { get; set; }
public int Skip { get; set; } = 0;
public int Take { get; set; } = 50;
public string? SortColumn { get; set; }
public bool SortDescending { get; set; } = true;
}
4.2 Backend Service
Services/OrderService.cs
using Microsoft.EntityFrameworkCore;
public class OrderService
{
private readonly AppDbContext _context;
public OrderService(AppDbContext context)
{
_context = context;
}
public async Task<List<Order>> GetFilteredOrdersAsync(OrderFilter filter)
{
var query = _context.Orders.AsQueryable();
if (!string.IsNullOrEmpty(filter.Status))
query = query.Where(o => o.Status == filter.Status);
if (!string.IsNullOrEmpty(filter.Country))
query = query.Where(o => o.Country == filter.Country);
if (filter.StartDate.HasValue)
query = query.Where(o => o.OrderDate >= filter.StartDate.Value);
if (filter.EndDate.HasValue)
query = query.Where(o => o.OrderDate <= filter.EndDate.Value);
if (filter.MinAmount.HasValue)
query = query.Where(o => o.Amount >= filter.MinAmount.Value);
if (filter.MaxAmount.HasValue)
query = query.Where(o => o.Amount <= filter.MaxAmount.Value);
if (filter.Categories != null && filter.Categories.Any())
query = query.Where(o => filter.Categories.Contains(o.Category));
if (!string.IsNullOrEmpty(filter.SearchText))
query = query.Where(o => o.CustomerName.Contains(filter.SearchText) || o.ProductName.Contains(filter.SearchText));
// Sorting
if (!string.IsNullOrEmpty(filter.SortColumn))
{
query = filter.SortDescending
? query.OrderByDescending(e => EF.Property<object>(e, filter.SortColumn))
: query.OrderBy(e => EF.Property<object>(e, filter.SortColumn));
}
else
{
query = query.OrderByDescending(o => o.OrderDate);
}
// Pagination
query = query.Skip(filter.Skip).Take(filter.Take);
return await query.AsNoTracking().ToListAsync();
}
}
4.3 Controller
Controllers/OrdersController.cs
[Route("api/[controller]")]
[ApiController]
public class OrdersController : ControllerBase
{
private readonly OrderService _orderService;
public OrdersController(OrderService orderService)
{
_orderService = orderService;
}
[HttpPost("filter")]
public async Task<IActionResult> FilterOrders([FromBody] OrderFilter filter)
{
var result = await _orderService.GetFilteredOrdersAsync(filter);
return Ok(result);
}
}
5. Angular Frontend Implementation
5.1 Angular Filter Model
models/order-filter.model.ts
export interface OrderFilter {
status?: string;
country?: string;
startDate?: string;
endDate?: string;
minAmount?: number;
maxAmount?: number;
categories?: string[];
searchText?: string;
skip?: number;
take?: number;
sortColumn?: string;
sortDescending?: boolean;
}
5.2 Angular Service
services/order.service.ts
import { HttpClient } from '@angular/common/http';
import { Injectable } from '@angular/core';
import { Observable } from 'rxjs';
import { OrderFilter } from '../models/order-filter.model';
@Injectable({
providedIn: 'root'
})
export class OrderService {
private apiUrl = 'https://localhost:5001/api/orders/filter';
constructor(private http: HttpClient) {}
getFilteredOrders(filter: OrderFilter): Observable<any> {
return this.http.post<any>(this.apiUrl, filter);
}
}
5.3 Angular Component
components/order-filter/order-filter.component.ts
import { Component, OnInit } from '@angular/core';
import { FormBuilder, FormGroup } from '@angular/forms';
import { OrderService } from '../../services/order.service';
import { OrderFilter } from '../../models/order-filter.model';
@Component({
selector: 'app-order-filter',
templateUrl: './order-filter.component.html'
})
export class OrderFilterComponent implements OnInit {
filterForm: FormGroup;
orders: any[] = [];
constructor(private fb: FormBuilder, private orderService: OrderService) {
this.filterForm = this.fb.group({
status: [''],
country: [''],
startDate: [''],
endDate: [''],
minAmount: [''],
maxAmount: [''],
categories: [[]],
searchText: ['']
});
}
ngOnInit(): void {
this.applyFilter();
}
applyFilter() {
const filter: OrderFilter = this.filterForm.value;
filter.skip = 0;
filter.take = 50;
this.orderService.getFilteredOrders(filter).subscribe(res => {
this.orders = res;
});
}
}
5.4 Component Template
<form [formGroup]="filterForm" (ngSubmit)="applyFilter()">
<input formControlName="searchText" placeholder="Search text" />
<select formControlName="status">
<option value="">All</option>
<option value="Active">Active</option>
<option value="Cancelled">Cancelled</option>
</select>
<input type="date" formControlName="startDate" />
<input type="date" formControlName="endDate" />
<input type="number" formControlName="minAmount" placeholder="Min Amount" />
<input type="number" formControlName="maxAmount" placeholder="Max Amount" />
<select multiple formControlName="categories">
<option value="Electronics">Electronics</option>
<option value="Appliances">Appliances</option>
<option value="Furniture">Furniture</option>
</select>
<button type="submit">Apply Filter</button>
</form>
<table>
<thead>
<tr>
<th>Order ID</th>
<th>Customer</th>
<th>Amount</th>
<th>Category</th>
<th>Status</th>
<th>Order Date</th>
</tr>
</thead>
<tbody>
<tr *ngFor="let order of orders">
<td>{{ order.id }}</td>
<td>{{ order.customerName }}</td>
<td>{{ order.amount }}</td>
<td>{{ order.category }}</td>
<td>{{ order.status }}</td>
<td>{{ order.orderDate | date }}</td>
</tr>
</tbody>
</table>
6. Real-World Best Practices
6.1 Backend Performance
Always use AsNoTracking in EF Core for read-only queries.
Index frequently filtered columns.
Use pagination to avoid returning large datasets.
Use dynamic LINQ or expression trees for complex filters.
6.2 Security
Always use parameterized queries to prevent SQL injection.
Validate input on both frontend and backend.
Implement user-level access filtering.
6.3 Frontend UX
Use multi-select dropdowns for categories.
Apply debounceTime for text search to reduce API calls.
Show loading indicators while fetching data.
Remember to reset skip/take when applying a new filter.
6.4 Extensibility
7. Advanced Filtering Patterns
Filter Serialization: Store filters in URL query params for bookmarking.
Server-Side Filtering: Always prefer backend filtering for large datasets.
Dynamic OR/AND Logic: Build expression trees to support complex filter combinations.
Caching Frequent Queries: Cache common filters to reduce DB load.
Full-Text Search: Use SQL Server Full-Text Index for search across large text columns.
8. Testing Strategy
Backend Tests:
Frontend Tests:
Verify filter form initializes correctly.
Test API calls with mocked service.
Verify table updates correctly after filtering.
Summary
In this article, we implemented a production-ready advanced filtering system using Angular + ASP.NET Core + SQL Server.
Key takeaways:
Define filter requirements clearly: text search, range, multi-select, logical operators.
Use dynamic queries or expression trees to avoid hardcoding conditions.
Optimize SQL Server queries with indexing and pagination.
Angular handles filter forms, multi-selects, and debounced search.
Best practices include security, caching, UX, and extensibility.
This architecture can scale to millions of rows, handle complex filter scenarios, and is maintainable for enterprise applications.