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?

Use the `AsQueryable()` extension `var customers = _context.Customers.AsQueryable()` — Nkosi, Oct 25 '19 at 07:58
`IQueryable customers = _context.Customers;` The advantage of this over `AsQueryable` is you will get a compile time check that the thing on the right is actually a `IQueryable` (e.g. not an `IEnumerable`). — mjwills, Oct 25 '19 at 09:46

score 5 · Accepted Answer · edited Jan 02 '20 at 21:31

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;

//...

score 2 · Answer 2 · answered Oct 25 '19 at 08:41

The given answer is correct, I just want to expand with a general tip:

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?)

Whenever you get an error "cannot implicitly convert Foo to Bar" on a given line, e.g.

a = b;

it means that the operands (a, b) have mismatching types (Bar, Foo respectively).

The solution is always to figure out which of these types is different than what you expect them to be, and then figure out why this type is different than what you expect.

In your case, the two operands are customers and customers.Where(), which should clue you in that Where() returns a different type than the type of your customers variable.

Good point. Thanks Flater! I will keep that in mind next time I encounter this issue. — Fabricio Rodriguez, Oct 25 '19 at 08:42

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

2 Answers2