Dynamic Querying in C#: Real-World Scenarios and Techniques

Erik Pourali — Thu, 14 Mar 2024 10:42:12 +0000

Imagine crafting a library app where users effortlessly find books by title, author, or genre. Traditional search methods drown you in code. But fear not! Dynamic Querying in C# saves the day.

✅In our tale, crafting separate search methods for each book attribute becomes a headache. The code becomes a labyrinth of nested if or switch case statements, a nightmare to navigate:



public IEnumerable<Book> GetBooks(string propertyToFilter, string keyword)
{
    switch (propertyToFilter)
    {
        case "Title":
            return await _books.Where(e => e.Title == keyword).ToListAsync();
        case "Author":
            return await _books.Where(e => e.Author == keyword).ToListAsync();
        case "Genre":
            return await _books.Where(e => e.Genre == keyword).ToListAsync();
        // More cases for other properties
    }
}

As your library expands, this code morphs into a tangled mess, crumbling under the weight of evolving requirements.

Enter Dynamic Queries, wielding their power alongside generics:



IQueryable<T> TextFilter<T>(IQueryable<T> source, string keyword)
{
    // The instructions and information in the rest of this article
}

You can apply this method to any entity, searching for the keyword within all string properties. Additionally, you have the flexibility to extend the method to support other data types.

Break free from rigid conditions. Seamlessly adapt to changing data structures. Navigate complex filters with ease.

In the dynamic landscape of software development, scenarios often arise where the nature of queries needs to adapt based on runtime conditions. This article explores various techniques in C# for executing different queries depending on runtime states using IQueryable and expression trees. We’ll dive into a real-world scenario and demonstrate how to implement dynamic querying with practical examples.

Download the source code on my GitHub:

Source Code

Understanding IQueryable and Expression Trees

Before delving into real-world examples, let’s briefly understand the fundamentals. An IQueryable in C# consists of two main components:

Expression: A language- and datasource-agnostic representation of the current query’s components, depicted as an expression tree.
Provider: An instance of a LINQ provider, responsible for materializing the query into a value or set of values.
In dynamic querying, the provider remains constant while the expression tree evolves with each query.

Here are various techniques for executing different queries depending on runtime states:

1. Using Runtime State within the Expression Tree

2. Calling Additional LINQ Methods

3. Varying the Expression Tree Passed into LINQ Methods

4. Constructing Expression Trees Using Factory Methods

5. Adding Method Call Nodes to IQueryable’s Expression Tree

6. Leveraging the Dynamic LINQ Library

Real-World Scenario: Managing Employee Data

Consider a scenario where you have an HR application with employee data, each having different attributes such as salary, department, and performance rating. HR administrators want the ability to dynamically filter and analyze employee data based on various criteria. The challenge is to build a flexible querying system that can handle diverse employee attributes and dynamic user inputs.



var employees = new List<Employee>
{
    new(Firstname: "Alice", Lastname: "Williams", Salary: 60000, Department: "IT", PerformanceRating: 4),
    new(Firstname: "Bob", Lastname: "Brown", Salary: 75000, Department: "HR", PerformanceRating: 3),
    new(Firstname: "Charlie", Lastname: "Taylor", Salary: 50000, Department: "Finance", PerformanceRating: 5),
};
var employeeSource = employees.AsQueryable();

record Employee(string Firstname, string Lastname, decimal Salary, string Department, int? PerformanceRating);

Techniques for Dynamic Querying

Now, let’s explore various techniques to handle dynamic querying based on user input.

1. Using Runtime State within the Expression Tree

Consider a scenario where administrators want to filter employees based on dynamic salary ranges:



decimal minSalary = 55000;
decimal maxSalary = 75000;

var employeeQuery = employeeSource
    .Where(x => x.Salary >= minSalary && x.Salary <= maxSalary);

Console.WriteLine(string.Join(",", employeeQuery.Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Alice Williams,Bob Brown

Benefits: This method provides a direct way to adapt queries based on simple runtime conditions.

2. Calling Additional LINQ Methods

Administrators might also want to sort employees dynamically based on performance ratings:



bool sortByRating = true;
employeeQuery = employeeSource;

if (sortByRating)
    employeeQuery = employeeQuery.OrderBy(x => x.PerformanceRating);

Console.WriteLine(string.Join(",", employeeQuery.Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Bob Brown,Alice Williams,Charlie Taylor

Benefits: This approach allows for the conditional application of various LINQ methods, tailoring queries to specific runtime scenarios.

3. Varying the Expression Tree Passed into LINQ Methods

With LINQ methods in .NET, you can use different expressions based on runtime state.

In this scenario, administrators want to filter employees based on both department and performance ratings dynamically:



using System.Linq.Expressions;

string targetDepartment = "IT";
int? targetRating = 4;

Expression<Func<Employee, bool>> expr = (targetDepartment, targetRating) switch
{
    ("" or null, null) => x => true,
    (_, null) => x => x.Department.Equals(targetDepartment),
    ("" or null, _) => x => x.PerformanceRating >= targetRating,
    (_, _) => x => x.Department.Equals(targetDepartment) && x.PerformanceRating >= targetRating
};

employeeQuery = employeeSource.Where(expr);

Console.WriteLine(string.Join(",", employeeQuery.Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Alice Williams

Benefits: This technique provides a flexible way to construct expressions dynamically based on multiple runtime conditions.

4. Constructing Expression Trees Using Factory Methods

Until now, we’ve been dealing with examples where we knew the type of element and query at compile time — specifically, using strings and IQueryable. However, you might need to modify a query for different element types or add components based on the element type. You can build expression trees from scratch using methods in System.Linq.Expressions.Expression to customize the expression at runtime for a specific element type.

Before exploring our scenario, let’s introduce the process of constructing an Expression. Follow these steps:

1) Import the necessary namespace:



using System.Linq.Expressions;

2) Create ParameterExpression objects for each parameter in your lambda expression using the Parameter factory method:



ParameterExpression parameter = Expression.Parameter(typeof(string), "x");

3) Build the body of your LambdaExpression using the ParameterExpression(s) you’ve defined and the factory methods provided by Expression. For example, you can construct an expression like x.StartsWith(“a”) as follows:



Expression body = Expression.Call(
    parameter,
    typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
    Expression.Constant("a")
);

4) Enclose the parameters and body within an Expression with compile-time type, using the suitable Lambda factory method overload:



Expression<Func<string, bool>> lambda = Expression.Lambda<Func<string, bool>>(body, parameter);

5) Compile the lambda expression to get the delegate:



Func<string, bool> function = lambda.Compile();

6) Here’s the complete example:



using System;
using System.Linq.Expressions;

class Program
{
    static void Main()
    {
        // Step 2: Define ParameterExpression objects for each parameter
        ParameterExpression parameter = Expression.Parameter(typeof(string), "x");

        // Step 3: Construct the body of your LambdaExpression
        Expression body = Expression.Call(
            parameter,
            typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
            Expression.Constant("a")
        );

        // Step 4: Wrap parameters and body in an Expression<TDelegate>
        Expression<Func<string, bool>> lambda = Expression.Lambda<Func<string, bool>>(body, parameter);

        // Step 5: Compile the lambda expression to get the delegate
        Func<string, bool> function = lambda.Compile();

        // Test the compiled function
        bool result = function("apple");
        Console.WriteLine(result); // Output: True
    }
}

Our scenario:

Consider having two entity types:



record Employee(string Firstname, string Lastname, decimal Salary, string Department, int? PerformanceRating);

record Task(string Title, string Description);

You want to filter and retrieve entities with a specific text in one of their string fields.

For Task, you’d search in Title and Description properties:



string term1 = "Project abc";
var tasksQry = new List<Task>()
    .AsQueryable()
    .Where(x => x.Description.Contains(term1) || x.Title.Contains(term1));

For Employee, in Name and Department properties:



string term2 = "Alice";
var employeesQry = new List<Employee>()
    .AsQueryable()
    .Where(x => x.Firstname.Contains(term2) || x.Lastname.Contains(term2));

Instead of creating separate functions for IQueryable and IQueryable, the following function lets you add this filtering to any existing query, regardless of the specific element type:



using System.Reflection;

string employeeSearchKeyword = "Alice";
string taskSearchKeyword = "Project abc";

IQueryable<T> TextFilter<T>(IQueryable<T> source, string term)
{
    if (string.IsNullOrEmpty(term))
        return source;

    // T stands for the type of element in the query, decided at compile time
    Type elementType = typeof(T);

    // Retrieve all string properties from this specific type
    PropertyInfo[] stringProperties =
        elementType.GetProperties()
            .Where(x => x.PropertyType == typeof(string))
            .ToArray();
    if (!stringProperties.Any())
        return source;

    // Identify the correct String.Contains overload
    MethodInfo containsMethod =
        typeof(string).GetMethod("Contains", new[] { typeof(string) })!;

    // Create a parameter for the expression tree, represented as 'x' in 'x => x.PropertyName.Contains("term")'
    // Define a ParameterExpression object
    ParameterExpression prm = Expression.Parameter(elementType);

    // Map each property to an expression tree node
    IEnumerable<Expression> expressions = stringProperties
        .Select<PropertyInfo, Expression>(prp =>
            // Construct an expression tree node for each property, like x.PropertyName.Contains("term")
            Expression.Call( // .Contains(...) 
                Expression.Property( // .PropertyName
                    prm, // x 
                    prp
                ),
                containsMethod,
                Expression.Constant(term) // "term" 
            )
        );

    // Combine all the resulting expression nodes using || (OR operator).
    Expression body = expressions
        .Aggregate(
            (prev, current) => Expression.Or(prev, current)
        );

    // Encapsulate the expression body in a compile-time-typed lambda expression
    Expression<Func<T, bool>> lambda =
        Expression.Lambda<Func<T, bool>>(body, prm);

    // Because the lambda is compile-time-typed (albeit with a generic parameter), we can use it with the Where method
    return source.Where(lambda);
}

employeeQuery = TextFilter(employeeSource, employeeSearchKeyword);
Console.WriteLine(string.Join(",", employeeQuery.Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Alice Williams

var taskQuery = TextFilter(taskSource, taskSearchKeyword);
Console.WriteLine(string.Join(",",
    taskQuery.Select(x => $"Task Detail:\n\tTitle: {x.Title}\n\tDescription: {x.Description}\n")));
// Output: Task Detail:
//              Title: Project abc Status Report
//              Description: give a quick summary of how the project has gone before the time period

Benefits: This method enables the creation of complex queries dynamically, accommodating various search criteria.

5. Adding Method Call Nodes to IQueryable’s Expression Tree

If you’re working with IQueryable instead of IQueryable, you can’t easily use the generic LINQ methods. One way around this is to construct the inner expression tree as mentioned earlier and then use reflection to call the right LINQ method while giving it the expression tree.

Another option is to copy what the LINQ method does by putting the whole tree in a MethodCallExpression that acts like a call to the LINQ method.

In a scenario where administrators want to filter employees based on dynamic conditions and handle untyped queries:



IQueryable TextFilter_Untyped(IQueryable source, string term)
{
    if (string.IsNullOrEmpty(term))
        return source;

    Type elementType = source.ElementType;

    // Retrieve all string properties from this specific type
    PropertyInfo[] stringProperties =
        elementType.GetProperties()
            .Where(x => x.PropertyType == typeof(string))
            .ToArray();
    if (!stringProperties.Any())
        return source;

    // Identify the correct String.Contains overload
    MethodInfo containsMethod =
        typeof(string).GetMethod("Contains", new[] { typeof(string) })!;

    // Create a parameter for the expression tree, represented as 'x' in 'x => x.PropertyName.Contains("term")'
    // Define a ParameterExpression object
    ParameterExpression prm = Expression.Parameter(elementType);

    // Map each property to an expression tree node
    IEnumerable<Expression> expressions = stringProperties
        .Select<PropertyInfo, Expression>(prp =>
            // Construct an expression tree node for each property, like x.PropertyName.Contains("term")
            Expression.Call( // .Contains(...) 
                Expression.Property( // .PropertyName
                    prm, // x 
                    prp
                ),
                containsMethod,
                Expression.Constant(term) // "term" 
            )
        );

    // Combine all the resulting expression nodes using || (OR operator).
    Expression body = expressions
        .Aggregate(
            (prev, current) => Expression.Or(prev, current)
        );
    if (body is null)
        return source;

    Expression filteredTree = Expression.Call(
        typeof(Queryable),
        "Where",
        new[] { elementType },
        source.Expression,
        Expression.Lambda(body, prm!)
    );

    return source.Provider.CreateQuery(filteredTree);
}

var eQuery = TextFilter_Untyped(employeeSource, "Charlie");

Console.WriteLine("5. Adding Method Call Nodes to IQueryable's Expression Tree:");
Console.WriteLine(string.Join(",", eQuery.Cast<Employee>().Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Charlie Taylor

In this scenario, when you don’t have a compile-time T generic placeholder, utilize the Lambda overload that doesn’t necessitate compile-time type information. This results in the creation of a LambdaExpression instead of an Expression.

Benefits: This approach facilitates the dynamic application of filtering logic to IQueryable without compile-time type information.

6. Leveraging the Dynamic LINQ Library

Making expression trees using factory methods is hard. It’s simpler to put together strings. The Dynamic LINQ library has extra methods for IQueryable that match regular LINQ ones, but they use strings with a special format instead of expression trees. The library turns the string into the right expression tree and gives back the translated IQueryable.

Get the Dynamic LINQ library from NuGet:



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

Import the necessary namespace:



using System.Linq.Dynamic.Core;

In a scenario where administrators want a simpler way to compose queries using string syntax:



IQueryable TextFilter_Strings(IQueryable source, string term) {
    if (string.IsNullOrEmpty(term)) 
     return source; 

    var elementType = source.ElementType;

    // Retrieve all string properties from this specific type
    var stringProperties = 
        elementType.GetProperties()
            .Where(x => x.PropertyType == typeof(string))
            .ToArray();
    if (!stringProperties.Any()) { return source; }

    // Build the string expression
    string filterExpr = string.Join(" || ",
        stringProperties.Select(prp => $"{prp.Name}.Contains(@0)"));

    return source.Where(filterExpr, term);
}

var qry = TextFilter_Untyped(employeeSource, "HR");

Console.WriteLine("6. Leveraging the Dynamic LINQ Library:");
Console.WriteLine(string.Join(",", qry.Cast<Employee>().Select(x => $"{x.Firstname} {x.Lastname}")));
// Output: Bob Brown

Benefits: The Dynamic LINQ library simplifies the construction of dynamic queries by accepting string expressions.

Conclusion

Dynamic querying in C# offers powerful tools for adapting queries to varying runtime conditions. By understanding IQueryable and expression trees, developers can create flexible and efficient systems that respond dynamically to user input. The real-world scenario of an employee management system demonstrates the practical application of these techniques in building robust and adaptable software solutions. Choose the appropriate method based on the complexity of your scenario, and empower your applications with dynamic querying capabilities.

The whole source on my GitHub: