Using for loop on where() method

Question

So I have a search-input and checkboxes that passes the values to the controller when there are inputs. And I want to use these values to get something back from the database. The search-input is a string and it works and intended. Here is the code for the search-input:

public async Task Index(string searchString, List checkedTypes)
{
    var products = from p in _db.Products select p;

    ViewData["CurrentFilter"] = searchString;
    
    if (!string.IsNullOrEmpty(searchString))
    {
        products = products.Where(p => p.Name.ToLower().Contains(searchString));
    }
    
    return View(products);
}

However the checkboxes values are stored in a list. So basically I want to do the same as the code above, but with a list. So basically an idea is like this:

if(checkedTypes != null)
{
    foreach (var i in checkedTypes)
    {
        products = products.Where(p => p.TypeId == i));
    }              
}

If I do it like the code above, I just get the last (i) from the loop. Another solution I did was this:

if(checkedTypes != null)
{
    var temp = new List();
    
    foreach (var i in checkedTypes)
    {
        temp.AddRange(products.Where(p => p.TypeId == i));
    }
    products = temp.AsQueryable();
}

But when I did it like that I get this error:

InvalidOperationException: The provider for the source IQueryable doesn't implement IAsyncQueryProvider. Only providers that implement IAsyncQueryProvider can be used for Entity Framework asynchronous operations.

So anyone have a solution that I can use? Or is there a better way to handle checkboxes in the controller?

user_2501279 · Answer

Assuming you are using EF Core (also the same is true for linq2db) - it supports translating filtering with local collection, i.e. Where(x => checkedTypes.Contains(x.SomeId)).

If you have "and" logic to filter by searchString and checkedTypes than you can conditionally add Where clause:

  if (!string.IsNullOrEmpty(searchString))
  {
       products = products.Where(p => p.Name.ToLower().Contains(searchString));
  }

  if(checkedTypes != null)
  {
       products = products.Where(p => checkedTypes.Contains(p.TypeId));    
  }

P.S.

Also you should be able to change your first line to:

var products = _db.Products.AsQueryable();

Using for loop on where() method

1 Answers