CODEWITHSUNDEEP
c#mysqlentity-framework
Ronald McBean
Ronald McBean

Reputation: 1427

Entity framework - how to avoid commands out of sync exception

I wrote a small application using entity framework (DbContext), UnitOfWork and repository patterns. the following code 

using (IUnitOfWork testUow = uowFactory.CreateUnitOfWork())
{
    foreach (Transponder transp in testUow.Transponders.GetAll()) // outer sql
    {
        var query = testUow.Animals.GetAll()
                    .Where(anim => anim.TransponderId == transp.Id); // inner sql

        // next line throws EntityCommandException: 
        Animal test = query.Single(); 
    }
}

produces an

EntityCommandException: {"Commands out of sync; You can't run this command now"}

Transponders and Animals are my repositories.

Why do I get this exception?

Isn't it ok to have code that produces nested sql commands like in my example (marked as inner and outer sql in code example).

How can I change my code/design to avoid this exception?

I know I can rewrite the linq query, so it is a one liner that works, but I'm interested in more general solution.

Upvotes: 1

Views: 1138

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

MySql (connector) doesn't support multiple active result sets (MARS). So while executing the outer query (which runs while the loop iterates) it can't read other result sets over the same connection.

So, as you say, rewriting the query is the solution. It's better anyway, because you've got a 1+N problem here.

Upvotes: 1

Related Questions