Linq modified get a error message New transaction is not allowed because there are other threads running in the session

Question

I want modified SQL data using linq, but when I modified, I got into trouble.

I get this error:

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

and this is my linq in C#

for (int i = 1; i < st.Count(); i++)
{
    string Lesson = i.ToString();

    var query = db.YearCourse_105008
                  .Where(o => o.Day == Day && o.Lesson == Lesson)
                  .Select(o => new
                    {
                        O_GUID = o.OpenCourseGUID,
                        Lesson = o.Lesson
                    });

    foreach (var item in query)
    {
        var PK = db.YearCourse_105008.Find(item.O_GUID);
        PK.Day = Day;
        PK.RC_MAJORCODE = st[i];
        PK.Lesson = i.ToString();

        db.Entry(PK).State = EntityState.Modified;
        db.SaveChanges();
    }                   
}

score 1 · Accepted Answer · edited May 23 '17 at 11:45

Since query is IQueryable that can return objects, certainly you need to realise it using IEnumerable collection (e.g. List) when iterating query results using foreach loop:

foreach (var item in query.ToList()) // --> here the source should be IEnumerable collection
{
    var PK = db.YearCourse_105008.Find(item.O_GUID);
    PK.Day = Day;
    PK.RC_MAJORCODE = st[i];
    PK.Lesson = i.ToString();

    db.Entry(PK).State = EntityState.Modified;
    db.SaveChanges();
}

or convert query results from IQueryable to IEnumerable first:

var query = db.YearCourse_105008
                  .Where(o => o.Day == Day && o.Lesson == Lesson)
                  .Select(o => new
                    {
                        O_GUID = o.OpenCourseGUID,
                        Lesson = o.Lesson
                    }).ToList();

NB: In addition, db.SaveChanges() method may placed outside foreach loop to update all modified entries at once, hence you don't need to save every modified entries per each iteration.

Linq modified get a error message New transaction is not allowed because there are other threads running in the session

1 Answers1