linq-to-sql "Cannot remove an entity that has not been attached"

Question

I'm getting the error Cannot remove an entity that has not been attached. when I try to delete a record. I've googled around and although there's plenty of places to find the solution to fix this problem, the suggested fix doesn't get me further:

using (MyDataContext TheDC = new MyDataContext())
{
  TheDC.MyTable.Attach(ARecord); //added this line but doesn't fix it.
  TheDC.MyTable.DeleteOnSubmit(ARecord);
  TheDC.SubmitChanges();

My bigger question is this: does this issue ONLY affect delete queries or does it affect other kinds of queries too and I just haven't bumped into this problem yet. I've written an update query and it seems to work without running into this error.

Thanks.

Answer 1

Please refer to this SO Post: How to delete in linq to sql?

...about attaching, if you already have the primary key. If you don't have the primary key then I have always just done a fetch then a delete, but, whenever I do a fetch I tend to store the primary key for updates and deletes.

It will delete off of the primary key, but if you have that then just attach as I do below and call delete. I don't pass around the object needed by DLINQ as I want to be able to change it if I want, so I pass in a different User object and just pull the PK from the business class and put it into the DAO class.

var db = new MeatRequestDataContext();            
if (input.UserID > 0)
{
     entity = new User()
     {
         UserID = input.UserID
     };
     db.Users.Attach(entity);
     db.Users.DeleteOnSubmit(entity);
 }

Answer 2

For me, the fix was just searching for the database object first.

var db = new MeatRequestDataContext();
if (input.UserID > 0)
{
    var existing = db.Users
        .Single(user => user.UserID == input.UserID);

    db.Users.DeleteOnSubmit(existing);
}

In my mind, I could only delete what was already there, so I needed to go get the items in the database that I wanted to delete first. This also works with collections and DeleteAllOnSubmit().