Best practices when filtering a query with Entityframework

Question

I have a web api method that receives a model from a search form and according to its values, it starts to filter the result of this search, as an example below.

    public HttpResponseMessage Buscar([FromBody]BuscarModel model)
    {
         Chamados resultado = chamadoServico.Buscar();

         if(!string.IsNullOrWhiteSpace(model.Foo))
              resultado = resultado.Where(e => e.Foo == model.Foo);
         if(!string.IsNullOrWhiteSpace(model.Bar))
              resultado = resultado.Where(e => e.Bar == model.Bar);

         return Request.CreateResponse(HttpStatusCode.OK, resultado);
    }

The code works as expected until you add a filter to a navigationProperty. For example

 if(model.Foobar != null && !string.IsNullOrWhiteSpace(model.Foobar.Foo))
        resultado = resultado.Where(e => e.Foobar.Foo == model.Foobar.Foo);

At this point, the exception EntityCommandExecutionException is raised with the following message:

There is already an open Datareader associated with this Command that should be closed first.

Is this the right way to create a search engine through filters? What can be done to prevent the exception from being raised? There is another way besides executing a toList() before the call to navigationProperty?

Answer 1

This is the correct way to create a search engine through filters?

It is. In fact it takes an explanation for the understanding of error.

When you mount a query in two different places through the Entity Framework, what happens is that a connection stays open waiting for its resolution, which usually happens by calling ToList() or AsEnumerable(), or even something that solves the object of IQueryable<> for something else.

By doing the same thing on the filter, what you do (unintentionally remembering) is to mount a filtering detached from the first, which already has an open connection waiting for execution. That’s why the mistake.

What can be done to prevent the exception from being raised?

The bad way to solve it is by using MultipleActiveResultSets in Connection string, but that doesn’t solve every case.

The good way is by solving research with ToList() or AsEnumerable(). Unfortunately it is not possible to generate SQL with Where from two different places. It is a limitation of the framework.

There is another way besides to execute a toList() before the call to navigationProperty?

By the above explained, no, but do not see this as something bad. Filtering in Controller is an SQL mount, and the filter an in-memory filter. It will only be bad if the SQL result is too large.