.NET Core  

Building Flexible EF Core Queries with Query Objects

Introduction

Imagine you are maintaining an HR portal that uses Entity Framework as its technology stack. This HR portal has many different UIs and dashboards that display employee information.

For example, the HR manager may need an Employee List page with different filters, such as showing all employees from the IT department. The code might look like this:

public string GetNameByDepartment(string did)
{
    dbcontext.employee.Where(p => p.DepartId > did).ToList();
}

Then the business logic keeps adding. One day, the CEO needs to see employees with a salary greater than 5k. Then we have to add something like this:

public List<string> GetNameBySalaryRange(int rangeFrom, int rangeTo)
{
    return dbcontext.employee
        .Where(p => p.salary >= rangeFrom &&
                    p.salary <= rangeTo)
        .Select(p => p.Name)
        .ToList();
}

But the business logic keeps adding and changing, and you may need to keep creating new methods:

  • GetByAge()

  • GetByNationality()

  • GetBySalary()

  • GetByNationalityAndGender()

  • GetByNationalityAndAgeRange()

This will cause the code maintainability to break.

There are a few approaches to tackle the issue:

  • Return IQueryable

  • Use Query Objects

Let's start with returning IQueryable.

Return IQueryable

First, just return an IQueryable of employees.

public IQueryable<Employee> GetEmployees()
{
    return dbContext.Employees.AsQueryable();
}

An IQueryable<Employee> is not an object or data itself; it is more like a query definition that has not been executed yet.

Then, in the main code, we can apply filters to get the desired result.

var name = GetEmployees()
    .Where(e => e.Id == id)
    .Select(e => e.Name)
    .FirstOrDefault();

var people = GetEmployees()
    .Where(e => e.DepartmentId == did)
    .ToList();

This approach avoids method explosion, and the query remains flexible. We just need to compose the logic where it is needed.

Use Query Objects

Suppose we have five business requirements:

  • GetById()

  • GetByDept()

  • GetByAge()

  • GetBySalary()

  • GetByNationality()

So instead of writing five different methods, we can create five small, reusable query-building blocks and compose them like Lego pieces.

First, we create an employee repository.

public class EmployeeRepository
{
    private readonly AppDbContext _db;

    public EmployeeRepository(AppDbContext db)
    {
        _db = db;
    }

    public IQueryable<Employee> Query()
    {
        return _db.Employees.AsQueryable();
    }
}

Then we create query objects for the five business requirements.

Get Employee by ID

public static IQueryable<Employee> ById(this IQueryable<Employee> query, string id)
{
    return query.Where(e => e.Id == id);
}

Get Employee by Department

public static IQueryable<Employee> ByDepartment(this IQueryable<Employee> query, int deptId)
{
    return query.Where(e => e.DepartmentId == deptId);
}

Get Employee by Age

public static IQueryable<Employee> ByAge(this IQueryable<Employee> query, int age)
{
    return query.Where(e => e.Age == age);
}

Get Employee by Salary Range

public static IQueryable<Employee> BySalaryRange(this IQueryable<Employee> query, decimal min, decimal max)
{
    return query.Where(e => e.Salary >= min && e.Salary <= max);
}

Get Employee by Nationality

public static IQueryable<Employee> ByNationality(this IQueryable<Employee> query, string nationality)
{
    return query.Where(e => e.Nationality == nationality);
}

Using Query Objects

Get by ID

var emp = _repo.Query()
    .ById("E001")
    .FirstOrDefault();

Get by Department

var emps = _repo.Query()
    .ByDepartment(2)
    .ToList();

Get by Salary Range

var richEmps = _repo.Query()
    .BySalaryRange(5000, 10000)
    .ToList();

You might argue that you still have to create five different query objects. What is the difference compared to creating five different methods? The amount of code is not reduced, so what is the point?

Well, first of all, we can combine the five query objects to form 31 (2⁵ − 1) combinations, such as:

var result = _repo.Query()
    .ByDepartment(3)
    .BySalaryRange(4000, 8000)
    .ToList();

var result = _repo.Query()
    .ByDepartment(2)
    .ByAge(30)
    .ByNationality("MY")
    .ToList();

instead of creating:

GetByDepartmentAndSalaryRange()
GetByDepartmentAndAgeAndNationality()
// and another 29 different combinations of hardcoded methods

Why Query Objects Are More Flexible

Another advantage of this approach is that the previous methods are "final actions", where each method executes SQL immediately.

return db.Employees.Where(...).ToList();

This makes it difficult to perform multiple database calls in a controlled way, limits query optimization, and makes composing logic harder.

With query objects, nothing is executed until the final call, such as:

.ToList()
.FirstOrDefault()

This allows Entity Framework Core to build a single SQL query with an optimized execution plan.

Building Dynamic Queries

Actually, you can also build queries dynamically based on input, instead of chaining:

.ByDept()
.ByAge()
.BySalary()

First, create the filter model for the Employee class.

public class EmployeeFilter
{
    public string? Id { get; set; }
    public int? DeptId { get; set; }
    public int? Age { get; set; }
    public decimal? MinSalary { get; set; }
    public decimal? MaxSalary { get; set; }
    public string? Nationality { get; set; }
}

Then create one dynamic builder.

public IQueryable<Employee> BuildQuery(EmployeeFilter filter)
{
    var query = _dbContext.Employees.AsQueryable();

    if (!string.IsNullOrEmpty(filter.Id))
        query = query.Where(e => e.Id == filter.Id);

    if (filter.DeptId.HasValue)
        query = query.Where(e => e.DepartmentId == filter.DeptId);

    if (filter.Age.HasValue)
        query = query.Where(e => e.Age == filter.Age);

    if (filter.MinSalary.HasValue)
        query = query.Where(e => e.Salary >= filter.MinSalary);

    if (filter.MaxSalary.HasValue)
        query = query.Where(e => e.Salary <= filter.MaxSalary);

    if (!string.IsNullOrEmpty(filter.Nationality))
        query = query.Where(e => e.Nationality == filter.Nationality);

    return query;
}

Then in the main code:

var filter = new EmployeeFilter
{
    DeptId = 2,
    MinSalary = 3000,
    Nationality = "MY"
};

var result = BuildQuery(filter).ToList();

Benefits of Dynamic Query Building

Using a dynamic query builder provides several advantages:

  • Reduces the number of repository methods.

  • Keeps filtering logic centralized.

  • Makes it easier to add new filters.

  • Allows Entity Framework Core to generate optimized SQL.

  • Improves maintainability as business requirements grow.

Conclusion

Query Objects provide a simple way to build flexible and reusable queries without creating countless repository methods. As your application grows, this approach makes your code easier to maintain while allowing Entity Framework Core to generate efficient SQL queries.

By combining IQueryable, reusable query extensions, and dynamic query builders, you can handle evolving business requirements without creating a large number of specialized repository methods.

Membership not found