Building filters on DbContext

Question

I have an ASP .Net Core 2.1 Web Api. I was wondering if there is an "elegant solution" for constructing filters on my DbContext based on query strings? So... say I have one [optional] query string:

    // GET: api/Accounts
    [HttpGet]
    public IEnumerable<File> GetAccount([FromQuery] bool? isActive)
    {
        if (isActive.HasValue)
            return _context.Accounts.Where(a => a.IsActive == isActive.Value);
        else
            return _context.Accounts;
    }

Simple enough... But say I have a number of (optional) query strings:

    // GET: api/Accounts
    [HttpGet]
    public IEnumerable<File> GetAccount([FromQuery] bool? isActive, [FromQuery] string type, [FromQuery] int? agentId, [FromQuery] bool? someOtherFilter)
    {

    }

As you can see, constructing the filters now gets harder, because there can be a combination of filters, depending which ones have been supplied. I could check if the first query string has a value, if it does, perform the filter and save the results into a temp variable. Then I could check the next query string, if that has a value, then perform that filter on the temp variable, and so on and so on. But that sounds like it's going to be slow... Any other suggestions? Thanks...

I think it's Duplicate of [This](https://stackoverflow.com/questions/50748975/most-efficient-way-to-create-a-dynamic-linq-query-that-depends-on-multiple-searc/50749868#comment88516329_50749868) question — Muhammad Vakili, Jun 08 '18 at 09:44
Possible duplicate of [Conditional Linq Queries](https://stackoverflow.com/questions/11194/conditional-linq-queries) — CodeNotFound, Jun 08 '18 at 10:18

SᴇM · Accepted Answer · 2018-06-08T13:08:59.123

You can check all your optional values with if/else:

[HttpGet]
public IEnumerable<File> GetAccount([FromQuery] bool? isActive, [FromQuery] string type, [FromQuery] int? agentId, [FromQuery] bool? someOtherFilter)
{
    var accounts = context.Accounts;
    if(isActive.HasValue) 
        accounts.Where(a => a.IsActive == isActive.Value);

    if(!string.IsNullOrEmpty(type)) 
        accounts.Where(a => a.Type == type);

    if(agentId.HasValue) 
        accounts.Where(a => a.AgentId == agentId.Value);

    . . .

    if(someOtherFilter.HasValue) 
        accounts.Where(a => a.SomeOtherFilter == someOtherFilter.Value);
}

or in linq to sql you can check for null inside your Where() method:

public IEnumerable<File> GetAccount([FromQuery] bool? isActive, [FromQuery] string type, [FromQuery] int? agentId, [FromQuery] bool? someOtherFilter) =>
    context.Accounts.Where(a => 
        (!isActive.HasValue || a.IsActive == isActive.Value) && 
        (string.IsNullOrEmpty(type) || a.Type == type)       &&
        (!agentId.HasValue || a.AgentId== agentId.Value)     &&
        . . .
        (!someOtherFilter.HasValue || a.SomeOtherFilter== someOtherFilter.Value);

momo · Answer 2 · 2018-06-08T12:40:32.473

I would suggest to add an extension to hide all the dirty work and create something like this:

public class AccountQuery
{
    public bool IsActive { get; set; }
    public int? AgentId { get; set; }
    ...
}

public static class Extensions
{
    public static IQueryable<Account> ApplyFilter(this IQueryable<Account> query, AccountQuery queryObj)
    {
        if (queryObj.IsActive)
            query = query.Where(...);

        if (queryObj.AgentId.HasValue)
            query = query.Where(...);

        // some other filters
        return query;
    }
}

[HttpGet]
public IEnumerable<File> GetAccount([FromQuery] AccountQuery queryObj) 
{
    var query = _context.Accounts.AsQueryable();
    query = query.ApplyFilter(queryObj);
    ...
}

Building filters on DbContext

2 Answers2

Linked