Best practices when filtering a query with EntityFramework

Question

Best practices when filtering a query with EntityFramework

Navigation

#1 by (3 votes)

4

I have a method in web api that receives a model from a search form and according to its values, it starts to filter the result of this search, as shown 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 EntityCommandExecutionException exception 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? Is there another way besides executing a toList() before the call to navigationProperty?

c# entity-framework-6 asp.net-web-api

asked by anonymous 04.01.2016 / 19:44

1 answer

JavaScript canvas builder Mount a two-dimensional array from another array

score 3 · Accepted Answer

Is this the right way to create a search engine through filters?

Yes. In fact, an explanation is needed for understanding the error.

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

When doing the same thing in the filter, what you do (inadvertently, remembering) is to mount a filtering detached from the first, which already has an open connection waiting for execution. So the mistake.

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

The bad way to resolve is by using MultipleActiveResultSets in the connection string , but this does not solve all cases.

The good way is solving the search with ToList() or AsEnumerable() . Unfortunately you can not generate SQL with Where from two different places. It is a limitation of the framework.

Is there any way other than executing a toList() before calling navigationProperty ?

By the above explained, no, but do not see this as something bad. The Controller filtering is an SQL assembly, and the filtering is a filtering in memory. It will only get bad if the SQL result is too large.