EntityFramework SaveChanges is aborting the exception due to "pending requests working on this transaction"
I wrote a service that starts SchedulerCallback once a day and does various jobs and updates MS SQL database using EntityFramework 6.1.3, ApplicationUserManager and LINQ
After each task, it saves the changes using SaveChanges in a global context that lives inside the class, but every few weeks one or more of the SaveChanges fails with: " The transaction cannot be completed because there are pending requests working on this transaction .
The planner looks like this:
Timer Scheduler = new Timer(new TimerCallback(SchedulerCallback))
All SaveChanges are executed after any loops and I couldn't find out what I was doing wrong in the Entity Framework documentation or on Stack Overflow.
A very simplified example of my service class, ignoring only one task scheduling and executing and SaveChanges:
public partial class MyService : ServiceBase
{
private MyContext myContext { get; set; }
private ApplicationUserManager UserManager { get; set; }
public MyService()
{
MyContext = new MyContext();
UserManager = new ApplicationUserManager(new UserStore<ApplicationUser>(MyContext));
}
private void SchedulerCallback()
{
var users = (from u in MyContext.Users where u.Status = StatusFlag.PlaceOrder select u.User).ToList();
foreach (var user in users)
{
myContext.Order.Add(new MyOrder());
myContext.Order.User = user;
OrderTransaction transaction new OrderTransaction();
order.Transactions = new List<OrderTransaction>();
order.Transactions.Add(transaction);
user.Status = StatusFlag.OrderPlaced;
}
try
{
myContext.SaveChanges();
}
catch (Exception e)
{
Logger.LogError("Exception", e);
}
}
}
In my service, SchedulerCallback does a lot more and calls the SaveChanges () context in the context multiple times, but not in a loop, but rather for different tasks. It's not the first time I've called SaveChanges that the error occurs, at least so far, but again it doesn't happen very often, so it's hard to set any template.
What am I doing wrong?
source to share
The timer callback runs on different threads ( similar issue ), but the DbContext is not thread safe, so create the context just before doing any work
public partial class MyService : ServiceBase
{
private void SchedulerCallback()
{
using (var myContext = new MyContext())
{
var UserManager = new ApplicationUserManager(new UserStore<ApplicationUser>(MyContext));
var users = (from u in myContext.Users where u.Status = StatusFlag.PlaceOrder select u.User).ToList();
foreach (var user in users)
{
myContext.Order.Add(new MyOrder());
myContext.Order.User = user;
OrderTransaction transaction = new OrderTransaction();
order.Transactions = new List<OrderTransaction>();
order.Transactions.Add(transaction);
user.Status = StatusFlag.OrderPlaced;
}
try
{
myContext.SaveChanges();
}
catch (Exception e)
{
Logger.LogError("Exception", e);
}
}
}
}
source to share