EntityFramework failing with multiple SELECT requests from MVC Web Api

Question

Currently I'm building an api using MVC4's Web Api features, using EntityFramework 4 with lazy loading and proxy creation disabled. Since I'm migrating an existing MySQL database I've used the database first strategy.

My server runs on Windows Server 2012 (at the moment still the Datacenter RC) with IIS 8 and Microsoft SQL Server 2012.

I think the problem I've been having isn't of the 'functional' category, but more from the 'performance' category. In example, I have an Person object. This object has a list of children, and a city object (with basic info of the city):

[DataContract(IsReference = true)]
public partial class Person
{
    public Person()
    {
        this.Children = new HashSet<Children>();
    }

    [DataMember]
    public int ID { get; set; }
    [DataMember]
    public int ParentID { get; set; }
    [DataMember]
    public int CityID { get; set; }
    [DataMember]
    public string Name { get; set; }
    [DataMember]
    public int Age { get; set; }

    [DataMember]
    public virtual ICollection<Person> Children { get; set; }
    [DataMember]
    public City City { get; set; }
}

[DataContract(IsReference = true)]
public partial class City
{
    [DataMember]
    public int ID { get; set; }
    [DataMember]
    public string Name { get; set; }
    [DataMember]
    public int Residents { get; set; }
}

Note: These classes are generated by EntityFramework/DBContext

These classes are used in a PersonsController, to retrieve info from a person using httpget:

public class PersonsController : ApiController
{
    private MyEntities db = new MyEntities();

    [HttpGet]
    public Person Get(int id, bool getChildren = false, bool getCity = false)
    {
        Person person = this.db.Persons.SingleOrDefault(p => p.ID == id);
        if (person == null)
        {
            throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
        }

        if (getChildren)
        {
            person.Children = this.db.Persons.Where(c => c.ParentID == person.ID)
        }

        if (getCity)
        {
            person.Customer = this.db.Cities.SingleOrDefault(r => r.ID == person.CityID);
        }

        return person;
    }
}

First of all I hoped someone could give me some information if this it the correct way to use EntityFramework within Web Api, especially the creation of the db object. I've read multiple articles about creating so-called repository classes, but I've chosen not to do so.

Now the problem I'm creating this question for; When I execute a request to get the info for Person with ID 1, all goes well. Also with the GetChildren and getCity parameters set to true. (this works for all persons available in the database). But, this is when executing one request at the time. When I use Fiddler to execute the same request fast after eachother, after a couple request the 500-errors start to appear. Then a couple of requests go okay again, then it fails a couple of times, etc.

When the requests fail it is with one of the following error messages: New transaction is not allowed because there are other threads running in the session. or The transaction operation cannot be performed because there are pending requests working on this transaction.

So what this actually says is he cant create a new transaction, because there are other threads in this session and also it can't add a request to the transaction because there is a queue allready.

Hopefully you understand this is really annoying, since this could also happen when the api goes live and is used by multiple users at the same time.

Does anyone know how the above errors can be resolved? Please check my implementation example above, maybe it's not optimal :)

Thanks in advance!

score 0 · Answer 1 · edited May 23 '17 at 11:56

You can enable MARS by adding 'MultipleActiveResultSets=True' to your connection string to solve this issue. The reason you have to enable MARS is that you are executing multiple queries on a single connection i.e DbContext.

Alternate and better way is to not enable MARS and optimize your action to execute a single query by doing this instead (Entityframework eager loading). This has the advantage of invoking a single query vs 3 queries (worst case).

public class PersonsController : ApiController
{
    private MyEntities db = new MyEntities();

    public Person Get(int id, bool getChildren = false, bool getCity = false)
    {
        var people = this.db.Persons;

        if(getChildren)
        {
            people = people.Include("Children");
        }

        if(getCity)
        {
            people = people.Include("City");
        }

        Person person = people.SingleOrDefault(p => p.ID == id);
        if (person == null)
        {
            throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
        }

        return person;
    }
}

EntityFramework failing with multiple SELECT requests from MVC Web Api

1 Answers1