Advanced Searching & Sorting

Beyond filtering exact matches, modern APIs must support dynamic "Google-style" text searching and dynamic multi-column sorting (e.g., sorting grid columns on the frontend).

1. Searching

While Filtering looks for exact matches (e.g., Price == 50), Searching looks for partial text matches across multiple columns.

public async Task<List<Product>> GetProductsAsync(string searchQuery)
{
    var collection = _context.Products as IQueryable<Product>;

    if (!string.IsNullOrWhiteSpace(searchQuery))
    {
        searchQuery = searchQuery.Trim().ToLower();
        
        // EF Core translates .Contains() into a SQL "LIKE '%query%'" operator
        collection = collection.Where(a => 
            a.ProductName.ToLower().Contains(searchQuery) ||
            a.Description.ToLower().Contains(searchQuery)
        );
    }
    
    return await collection.ToListAsync();
}

2. Dynamic Sorting (System.Linq.Dynamic.Core)

If a frontend data grid allows columns to be clicked to sort them, the API must accept the column name as a string (e.g., orderBy=price desc). Standard LINQ OrderBy() does not accept strings; it requires strongly-typed lambda expressions.

Instead of writing massive switch statements mapping strings to lambdas, install System.Linq.Dynamic.Core.

dotnet add package System.Linq.Dynamic.Core

The Implementation

using System.Linq.Dynamic.Core;

public async Task<List<Product>> GetProductsAsync(string orderByQueryString)
{
    var collection = _context.Products as IQueryable<Product>;
    
    // Example queryString from frontend: "price desc, name asc"

    if (!string.IsNullOrWhiteSpace(orderByQueryString))
    {
        // System.Linq.Dynamic parses a string query directly into a SQL ORDER BY!
        collection = collection.OrderBy(orderByQueryString); 
    }
    else 
    {
        // Always provide a fallback
        collection = collection.OrderBy(p => p.ProductName);
    }

    return await collection.ToListAsync();
}

3. Interview Mastery

Q: "When applying Sorting, Filtering, and Pagination simultaneously in EF Core, does the order in which we append the LINQ methods matter?"

Architect Answer: "Yes, the order is absolutely critical to the output. You MUST sequence the operations in this exact order: 1. Filtering (Where), 2. Searching (Where/Contains), 3. Sorting (OrderBy), 4. Pagination (Skip/Take). If you attempt to Paginate (Skip/Take) before you Filter or Sort, EF Core will only execute the sort logic on the 10 isolated items sitting in the page, completely ignoring the other 50,000 items in the database. The pagination commands must be the absolute final commands attached to the `IQueryable` before invoking `ToListAsync()`."