EntityException unhandled

Question

I am getting an error in my code that says

An error occurred while starting a transaction on the provider connection. See inner exception for details

The inner exception is:

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

My Code:

        using (var db = new ProductionContext())
        {
            var objct = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectContext;
            objct.ExecuteStoreCommand("TRUNCATE TABLE [MU By Machine]");
            db.SaveChanges();

            var query =
                db.MU_Reports
                .GroupBy(x => new { x.Date, x.Machine_Number, x.Shift })
                .Select(x => new
                {
                    Date = x.Key.Date,
                    Shift = x.Key.Shift,
                    MachineNum = x.Key.Machine_Number,
                    MU = x.Sum(i => i.MU * 100)
                });

            foreach (var item in query)
            {
                var y = new MU_By_Machine();

                y.Date = item.Date;
                y.Shift = item.Shift;
                y.Machine_Number = item.MachineNum;
                y.MU = item.MU;

                db.MU_By_Machines.Add(y);
                db.SaveChanges();
            }

It happens at the db.SaveChanges portion of my code and when I move it out of the for loop the code works but only saves one line in my table. If additional information is required let me know. I am still new to c#.

Answer 1

Replace

 var query = db.MU_Reports
                .GroupBy(x => new { x.Date, x.Machine_Number, x.Shift })
                .Select(x => new
                {
                    Date = x.Key.Date,
                    Shift = x.Key.Shift,
                    MachineNum = x.Key.Machine_Number,
                    MU = x.Sum(i => i.MU * 100)
                });

with

 var query = db.MU_Reports
                .GroupBy(x => new { x.Date, x.Machine_Number, x.Shift })
                .Select(x => new
                {
                    Date = x.Key.Date,
                    Shift = x.Key.Shift,
                    MachineNum = x.Key.Machine_Number,
                    MU = x.Sum(i => i.MU * 100)
                }).ToList();

It helped me in similar issues. This error happens when you loop over a query result without using the .ToList() method. This method closes the connection and avoids this problem.