Updating a single record in forEach using C#/Entity

Mike Miller
Mike Miller used Ask the Experts™
on
Is there any way I can update a record after doing something in a for each? This is the idea (in bold), but doesn't work.

 using (var db = new PTO_SystemEntities())
            {
                var pto = db.PTO_REQUEST
                    .Where(q => q.Status != "Cancelled" && q.Status != "Processed" && q.Type != "Reverse Leave");
                foreach (var i in pto)
                {
                    Console.Write(i.EMPLID.Trim() + ", " + location.Trim() + ", " + jobcode.Trim() + ", " + i.Hours + Environment.NewLine);
[b]                    db.Entry(i).Property(e => e.Status).CurrentValue = "Processed";
                    db.Entry(i).Property(e => e.Status).IsModified = true;
                    db.SaveChanges();[/b]
                }
            }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Mike;

You state the following, "This is the idea (in bold), but doesn't work.", how does it not work, do you get a run time error or compile error or something else?

Please also post the schema of the PTO_REQUEST table from the database.
Mike MillerSoftware Engineer

Author

Commented:
This is the error I'm getting:

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

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Please post the schema of the database table PTO_REQUEST. Thanks
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Mike MillerSoftware Engineer

Author

Commented:
ID int (primary key, is identity)
Status VarChar (50)
Hours Decimal (5, 2)
EMPLID char(11)
Retired
Distinguished Expert 2017
Commented:
Hi Mike;

OK, the issue is that you have a active reader pulling in data from the query and at the same time trying to update the values to the database in your foreach loop. To correct the issue use the ToList query method to the query as follows
var pto = db.PTO_REQUEST
            .Where(q => q.Status != "Cancelled" && q.Status != "Processed" && q.Type != "Reverse Leave").ToList();

Open in new window

This will cause the query to execute before processing the data.
Mike MillerSoftware Engineer

Author

Commented:
Yup, was just coming on to post my solution...

            using (var db = new PTO_SystemEntities())
            {
                var pos = db.PTO_REQUEST
                    .Where(q => q.Status != "Cancelled" && q.Status != "Processed" && q.Type != "Reverse Leave");
                foreach (var i in pos)
                {
		    //whatever code here
                    using (var dd = new PTO_SystemEntities())
                    {
                        dd.PTO_REQUEST
                            .Where(q => q.ID == i.ID)
                            .ToList()
                            .ForEach(a => a.Status = "Processed");
                        dd.SaveChanges();
                    }                                       
                }}

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Very good Mike, have a great day.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial