Problem reading rows from database using EF Core and conditional WHERE clauses

Question

I want to query a MySql database in my ASP .Net Core 3.0 Web API, and conditionally apply some WHERE filters. So I have this in one of my controller actions:

[HttpGet]
public async Task<IEnumerable<Customer>> GetCustomers([FromQuery] bool? isActive, [FromQuery] int? typeId, [FromQuery] bool? isProcessed)
{
    var customers = _context.Customers.Where(c => c.IsDeleted == false);

    if (isActive.HasValue)
        customers = customers.Where(c => c.IsActive == isActive.Value);

    if (typeId.HasValue)
        customers = customers.Where(c => c.TypeId == typeId.Value);

    if (isProcessed.HasValue)
        customers = customers.Where(c => c.IsProcessed == isProcessed.Value);

    return await customers.ToListAsync();
}

That works perfectly, because I have a Where clause in that first line:

var customers = _context.Customers.Where(c => c.IsDeleted == false);

but actually I don't want to put a Where clause in. I just want this:

[HttpGet]
public async Task<IEnumerable<Customer>> GetCustomers([FromQuery] bool? isActive, [FromQuery] int? typeId, [FromQuery] bool? isProcessed)
{
    var customers = _context.Customers;

    if (isActive.HasValue)
        customers = customers.Where(c => c.IsActive == isActive.Value);

    if (typeId.HasValue)
        customers = customers.Where(c => c.TypeId == typeId.Value);

    if (isProcessed.HasValue)
        customers = customers.Where(c => c.IsProcessed == isProcessed.Value);

    return await customers.ToListAsync();
}

But as soon as I remove that first Where clause, I get this exception:

Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<PropWorx.API.Models.Customer>' to 'Microsoft.EntityFrameworkCore.DbSet<PropWorx.API.Models.Customer>'. An explicit conversion exists (are you missing a cast?)

Any ideas?

Answer 1

The original code without var would look like

DbSet<Customer> customers = _context.Customers;

if (isActive.HasValue)
    customers = customers.Where(c => c.IsActive == isActive.Value);
    // Where returns IQueryable<Customer>, hence the error

This is a case where var is working against you in understanding the code being written.

Use the AsQueryable() extension to get the desired behavior

var customers = _context.Customers.AsQueryable(); //IQueryable<Customer>

//...

Or explicitly state the type used

IQueryable<Customer> customers = _context.Customers;

//...