Update Data using Linq2Sql

Question

I have this in my mvc ActionResult

    [HttpPost]
    public ActionResult _ChangeDetails( [Bind(Prefix="ContactDetails")] userDetail UserDetail )
    {
        MemberChangeDetailsFormViewModel fvm = new MemberChangeDetailsFormViewModel();

        if (ModelState.IsValid)
        {
            //save
            UserDetailRepository repository = new UserDetailRepository();
            repository.Save(UserDetail);
            return RedirectToAction("Index", "Member");
        }

        fvm.ContactDetails = UserDetail;

        return View(fvm);
    }

Then in my repository I have;

if (userDetail.id != Guid.Empty)
{
    userDetail orig = dc.userDetails.Where(x => x.id == userDetail.id).Single();
    dc.userDetails.Attach(userDetail, orig);
    dc.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, userDetail);
    dc.SubmitChanges();
}

However the "attach" is generating an error; Cannot add an entity with a key that is already in use.

I understand that it's caused because L2S already has the object attached. I have tried doing this with and without the orig object but get the same message.

What would be the best practice to update the data in the table from the model?

score 3 · Accepted Answer · edited May 23 '17 at 09:58

3

Linq-to-SQL can't deal with two objects with the same key in the same data context. Some options:

Option 1: Use two different data contexts:

public void Update(UserDetail modifiedUser)
{
    using (UserDetailDataContext dc1 = new UserDetailDataContext())
    using (UserDetailDataContext dc2 = new UserDetailDataContext())
    {
        UserDetail originalUser = dc1.UserDetails.Single(u => u.id == modifiedUser.id);
        dc2.UserDetails.Attach(modifiedUser, originalUser);
        dc2.SubmitChanges();
    }
}

Option 2: Don't pull the original object; annotate the new object as an update:

public void Update(UserDetail modifiedUser)
{
    using (UserDetailDataContext dc = new UserDetailDataContext())
    {
        dc.UserDetails.Attach(modifiedUser);
        dc.Refresh(RefreshMode.KeepCurrentValues, modifiedUser);
        dc.SubmitChanges();
    }
}

There's more discussion in this question, and Rick Strahl has a blog entry that covers a versioning-based strategy, if you're willing to modify the table schema to add a timestamp.

edited May 23 '17 at 09:58

Community

1
1

answered Mar 27 '11 at 02:36

Oren Trutner

23,752
8
54
55

the error happens at the "Attach" so option 2 won't work and I'm not sure I want a second context. Should I take originalUser, update the fields and then submit the changes? Seems a little backward to me – griegs Mar 27 '11 at 05:52
1

Griegs, the code you put in the question fails because it tries to to load two objects with the same key into the context: orig (with the Where/Single query) and userDetails (with Attach.) Option 2 works because it loads only one such object: userDetails with Attach. If you haven't actually tried it yet, you might want to give it a go. – Oren Trutner Mar 27 '11 at 06:26
I have already tried it with only a single object and I get the same result. I tried both with and without the orig object. – griegs Mar 27 '11 at 06:38
@OrenTrutner, I confirm that only option 1 works. 2nd option throws the same "Cannot add an entity with a key that is already in use". – Dmitry Fedorkov Aug 31 '12 at 16:37

Update Data using Linq2Sql

1 Answers1