Entity Framework error - New transaction is not allowed

Question

I am currently getting this error in an EF method that is being called (having got Elmah workking)

New transaction is not allowed because there are other threads running in the session.

I have looked at this :

SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

and similar questions, but these all refer to savechanges being called within a foreach loop. My code doesn't have a foreach loop, so i'm struggling to find the issue.

Controller (inherited from ApiController)

static readonly IMyRepository myRepository = new MyRepository();

public HttpResponseMessage PutObject(int id, int id2)
{
    if(!myRepository.Update(id,id2))
    {
        return Request.CreateErrorResponse(HttpStatusCode.NotFound);
    }
    else
    {
        return Request.CreateResponse(HttpStatusCode.OK);
    }
}

Repository

MyEntities _myEntities;

public MyRepository(MyEntities context)
{
    _myEntities = context;
}

public MyRepository()
{

}

    public bool Update(int id,int id2, string id3, int id4)
    {
        _myEntities = _myEntities ?? new MyEntities();

        //update by id if id2,id3 and id4 are zero
        if (id2 == 0 && id3 == "0" && id4 == 0)
        {
            var myobject = _myEntities.MyObjects.Where(x => x.id == id);
            if (myobject.Count() > 0)
            {
                MyObject temp = myobject.SingleOrDefault();
                temp.Processed = true;
                _myEntities.SaveChanges();
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            var myobject = _myEntities.MyObjects.Where(x => x.SourceID == id && x.ExternalID == id2 && x.InternalID == id3 && x.Code == id4 && x.Processed == false);
            if (myobject.Count() > 0)
            {
                myobject temp = myobject.SingleOrDefault();
                temp.Processed = true;
                _myEntities.SaveChanges();
                return true;
            }
            else
            {
                return false;
            }
        }
    }

is this because the IQueryable returned by the linq, still holds an open connection?

Don't store `_myEntities` in the field. Instead, create new context in the beginning of `Update` and dispose in the end (with `using`). — Evk, Dec 06 '17 at 10:39
@Evk - I've updated my code, purely on the basis that I thought that any call to the controller would create a new repository object, and hence a new connection. once the Update method has completed then the controller is no longer needed, the object/connection is disposed of. Or is the controller always instantiated/ready, with the same connection once running? — kolin, Dec 08 '17 at 07:47
Your repository is static field. That means one connection is reused by all requests, which of course just cannot work. — Evk, Dec 08 '17 at 08:03
(it's inherited code by the way, as is always the case) so would it be better to create a new respository object on each "Put" action? — kolin, Dec 08 '17 at 08:14
What is the difference between `_MyEntities` and `_myEntities`? Where is `_MyEntities` defined? — grek40, Dec 08 '17 at 10:26
You should definitely try to create a fresh context within the scope of your `Update` and see if it solves your problem. Context creation is cheap, a shared context only makes sense if you need the same cached data in many locations. — grek40, Dec 08 '17 at 10:57

Entity Framework error - New transaction is not allowed

0 Answers0