We help IT Professionals succeed at work.

Updating a single record in forEach using C#/Entity

370 Views
Last Modified: 2017-03-30
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

Fernando SotoRetired
CERTIFIED EXPERT
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.

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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please post the schema of the database table PTO_REQUEST. Thanks

Author

Commented:
ID int (primary key, is identity)
Status VarChar (50)
Hours Decimal (5, 2)
EMPLID char(11)
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Very good Mike, have a great day.