Cheap Windows Hosting Review

Using Query Objects to Create Adaptable EF Core Queries

Posted on by Pablo Francesco

Assume you are in charge of an HR portal whose technology stack is Entity Framework. There are numerous user interfaces (UIs) and dashboards on this HR portal that show employee data. or instance, the HR manager might require an Employee List page with several filters, like displaying all IT department personnel. This is how the code may appear:

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

Without the need to create numerous repository methods, query objects offer an easy approach to create adaptable and reusable inquiries. This method lets Entity Framework Core create effective SQL queries while making your code easier to maintain as your application expands.

You may manage changing business needs without developing a lot of specific repository methods by combining IQueryable, reusable query extensions, and dynamic query builders.

Best ASP.NET Core 10.0 Hosting

The feature and reliability are the most important things when choosing a good ASP.NET Core 10.0 hosting. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core , their servers are optimized for PHP web applications such as the latest ASP.NET Core 10.0 version. The performance and the uptime of the ASP.NET Core  hosting service are excellent, and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its data centers are equipped with top equipment like cooling system, fire detection, high-speed Internet connection, and so on. That is why HostForLIFE.eu guarantees 99.9% uptime for ASP.NET Core . And the engineers do regular maintenance and monitoring works to assure its ASP.NET Core  hosting are security and always up.

hostforlifebanner