db.SaveChanges in ForEach causes 'New transaction is not allowed because there are other threads running in the session'

Question

I have an excel file with about 21000 rows . I imported it into a temp Table in my database.

Now I want to do some conversions on my data and then put them into my main table.

When I do SaveChanges() inside a foreach I got the following error:

Microsoft.Data.SqlClient.SqlException: 'New transaction is not allowed because there are other threads running in the session

When I use it after the foreach no error occurs and the table has just 4 records inserted instead of all 21000 records that I expected.

    public ActionResult FeedTempdataToMainDB()
    {
        var L = new Leave();
        //  var leaves = new List<Leave>();
        foreach (var item in db.TempLeaves)
        {
            L.Pcode = Int32.Parse(item.Cod);
            var z = int.Parse(item.LT) - 1;
            if (z == 0) L.LT = Leave.LeaveType.Saati;
            else L.LT = Leave.LeaveType.Roozane;
            var o = int.Parse(item.DLT) - 1;
            if (o == 0) L.DLT = Leave.DLType.Estehghaghi;
            if (o == 1) L.DLT = Leave.DLType.Estelaji;
            else L.DLT = Leave.DLType.Bihoghoogh;
            L.LeaveDayStart = item.LeaveDayStart.Old6digToMiladi();
            L.LeaveDayEnd = item.LeaveDayEnd.Old6digToMiladi();
            L.LeaveTimeStart = StringToHour(item.LeaveTimeStart);
            L.LeaveTimeEnd = StringToHour(item.LeaveTimeEnd);
            L.LeaveDays = int.Parse(item.LeaveDays);
            L.LeaveMinuts = SaatiLengh(item.LeaveMinuts);
            L.RegDate = StringToHour(item.RegTime);
            L.RegDate = item.RegDate.Old6digToMiladi().Date;
            L.RegistrarCode = Int32.Parse(item.RegistrarCode);
            L.HijriYear = L.LeaveDayStart.GetHijriYear();
            var t = IsOk(item.RegTime);
            if (L.DLT == 0 && t == false || L.LT == 0)
            {
                L.Calculate = false;

                L.IsActive = false;
            }
            else { L.Calculate = true; L.IsActive = true; }
            db.Leaves.Add(L);
            db.SaveChangesAsync();
        }
        //db.SaveChanges();
        return RedirectToAction("index");
     }

Answer 1

You have a bug in your code. You declared and created L outside of the loop. Each time you add the same L , only with different data. In the end you have list of the same data that was created during the last foreach loop cicle.

try this:

        foreach (var item in db.TempLeaves)
        {
            var z = int.Parse(item.LT) - 1; 
            var L = new Leave {
                Pcode = Int32.Parse(item.Cod),
                LeaveTimeStart = StringToHour(item.LeaveTimeStart),
                LeaveTimeEnd = StringToHour(item.LeaveTimeEnd),
                LeaveDays = int.Parse(item.LeaveDays),
                LT = z == 0? Leave.LeaveType.Saati : Leave.LeaveType.Roozane
            };     
            db.Leaves.Add(L);
        }

or this

        var leaves = new List<Leave>();
        foreach (var item in db.TempLeaves)
        {
            var z = int.Parse(item.LT) - 1; 
            var L = new Leave {
                Pcode = Int32.Parse(item.Cod),
                LeaveTimeStart = StringToHour(item.LeaveTimeStart),
                LeaveTimeEnd = StringToHour(item.LeaveTimeEnd),
                LeaveDays = int.Parse(item.LeaveDays),
                LT = z == 0? Leave.LeaveType.Saati : Leave.LeaveType.Roozane
            };     
            leaves.Add(L);
        }

        if (leaves.Count > 0)
        {
            db.Leaves.AddRange(leaves);
            db.SaveChanges();
        }

if you want to use async save you have to make async action at first.