Entity Framework LINQ insert command failing. MVC3

Question

Hi I'm trying to do a basic update based on an id using Linq and the entity framework. I'm very new to this but I do not see the problem.

My entity class object is declared at the controller level.

gwwbnEntities db = new gwwbnEntities();

The Method grabs a querystring id and updates the user's registration status who is represented by that id.

public ActionResult ConfirmedAccount(int id)
    {

        var q = from u in db.user_registration
                where u.id == id && u.reg_status == null
                select u;

        if (q.Any())
        {
            foreach(var item in q){

            user_registration user = item;
            user.reg_status = 202;
            db.Entry(user).State = EntityState.Modified;
            db.SaveChanges();
            }

            return View();
        }
        else
        {
            return RedirectToAction("RegistrationError");
        }
    }

Any help would be greatly appreciated! Again everything works and populates correctly, but the context object.SaveChanges() method fails everytime.

Thanks guys!

Do you ever expect more than one match? If not why are you using a foreach ? — BrokenGlass
– BrokenGlass, Commented May 4, 2012 at 19:30
The exception is: New transaction is not allowed because there are other threads running in the session. Exception Details: System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session. I was just using a foreach because of ease of coding. If you would advise doing it a different way I am definitely all ears. As I said I am very new to LINQ and the entity framework. Thank you guys so much! — imaginationpluscode
– imaginationpluscode, Commented May 4, 2012 at 21:06
Call SaveChanges outside the loop. Don't set the state to Modified - EF will detect that properties changed and will automatically set the state accordingly. You may want to do .ToList() on the q before doing anything. At the moment you are sending to queries to the database (one for .Any() and one to get entities). If you do .ToList() you will send only one query that brings entities but .Any() would be called on the list not on the database so it will be much faster and there is no trip to the database. — Pawel
– Pawel, Commented May 4, 2012 at 21:10

Pawel · Accepted Answer · 2012-05-04 21:23:04Z

3

The exception you are seeing is because you have an open data reader (foreach) and you are trying to create transaction (EF does it for you) in SaveChanges(). Call SaveChanges outside the loop. In addtion: Don't set the state to Modified - EF will detect that properties changed and will automatically set the state accordingly. You may want to do .ToList() on the q before doing anything. At the moment you are sending to queries to the database (one for .Any() and one to get entities). If you do .ToList() you will send only one query that brings entities but .Any() would be called on the list not on the database so it will be much faster and there is no trip to the database. Also ToList() force query evaluation so your foreach loop will not keep the data reader open as it will iterate on the list.

edited May 4, 2012 at 21:23

answered May 4, 2012 at 21:12

Pawel

31.7k4 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stephen Newman Over a year ago

To expand on this, you could also do this: var q = (from u in db.user_registration where u.id == id && u.reg_status == null select u).ToList(); Then the reader won't be open any longer when you are looping over its results and issuing the .SaveChanges() call.

Pawel Over a year ago

As per my comment above it would also save a trip to the database... I will copy my comments to the answer.

imaginationpluscode Over a year ago

This worked exceptionally well! Thank you for the explanations!!

Collectives™ on Stack Overflow

Entity Framework LINQ insert command failing. MVC3

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related