ASP.NET Entity Framework DataContext issue
Hi everyone !
I 've built an ASP.NET website using EF. I created a DataContext class which implements the singleton pattern. My DAO classes (singletons too) instanciate this datacontext and store it in a property. They use it in order to query the SQLServer DataBase. This worked ok for 3 months but I suddenly got exception messages like :"Connection must be valid and open / connection already open". It seemed that datacontext was not disposed. The only change, according to me, was the data size and number of users increasing.
I then found multiple posts saying that singleton was a bad idea foe datacontext, so I tried to instanciate datacontext in a using statement in every request and that resolved the problem, except for update queries which had no effects in database. I had to attach the db object to the context and then set its EntityState to "modified" to have my SaveChanges work.
Like this :
public bool DoucheXpsu(as_headers session)
{
using (MyDBEntities MyContext = new MyDBEntities())
{
try
{
as_status status = GetStatus(session);
if (status != null)
{
if (status.mainstatusvalue == 300)
{
status.DateDoucheXpsu = DateTime.Now;
status.DoucheXpsu = 1;
MyContext.as_status.Attach(status);
MyContext.ObjectStateManager.ChangeObjectState(status, EntityState.Modified);
MyContext.SaveChanges();
return true;
}
else
return false;
}
else
return false;
}
catch (OptimisticConcurrencyException)
{
return false;
}
catch (Exception)
{
return false;
}
}
}
The problem is that it actually didn't work for ONE method (which has nothing different from the other update method) !
The exception occured as I tried to attach the object : "The object cannot be attached because it is already in the object context. An object can only be reattached when it is in an unchanged state. " So I had to comment the attach and ChangeObjectState methods to have it work as expected :
public bool SetSessionToDelete(string numSession)
{
using (MyDBEntities MyContext = new MyDBEntities())
{
try
{
view_headerStatus view = (from v in MyContext.view_headerStatus
where v.CodeSession == numSession
where v.lastinserted == 1
select v).First();
if (view != null)
{
as_status status = (from s in MyContext.as_status
where s.jobclsid == view.jobclsid
where s.lastinserted == 1
select s).First();
if (status != null)
{
status.DeleteSession = 1;
//MyContext.as_status.Attach(status);
//MyContext.ObjectStateManager.ChangeObjectState(status, EntityState.Modified);
MyContext.SaveChanges();
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
catch (OptimisticConcurrencyException)
{
return false;
}
catch (Exception)
{
return false;
}
}
}
The question is WHY should this one behave differently ???
I've read many posts about EF and dataContext but I feel I'm missing something. I would be glad if anyone can help.
Thanks.