LINQ TO SQL error: An attempt has been made to Attach or Add an entity that is not new...

Question

"An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported."

I have scene a lot of solutions dealing with the Attach() method but I'm just trying to add in a new record. Not sure what is going on.

Here is my code, It is failing on the star'd line.:

try
            {
                LINQDataContext datacontext = new LINQDataContext();


                TrackableItem ti = datacontext.TrackableItems.FirstOrDefault(_t => _t.pkId == obj.fkTrackableItemId);
                arcTrackableItem ati = new arcTrackableItem();
                ati.barcode = ti.barcode;
                ati.dashNumber = ti.dashNumber;
                ati.dateDown = ti.dateDown;
                ati.dateUp = ti.dateUp;
                ati.fkItemStatusId = ti.fkItemStatusId;
                ati.fkItemTypeId = ti.fkItemTypeId;
                ati.partNumber = ti.partNumber;
                ati.serialNumber = ti.serialNumber;
                ati.archiveDate = DateTime.Now;

                datacontext.arcTrackableItems.InsertOnSubmit(ati);
                datacontext.SubmitChanges();


                arcPWR aItem = new arcPWR();
                aItem.comments = obj.comments;
                aItem.fkTrackableItemId = ati.pkId;
                aItem.fkPWRStatusId = obj.fkPWRStatusId;
                aItem.PwrStatus = obj.PwrStatus;


                **datacontext.arcPWRs.InsertOnSubmit(aItem);**
                datacontext.SubmitChanges();

Answer 1

What is "PwrStatus"? If this is an Linq2SQL object then you attach that object to your new datacontext by setting a reference in aItem.

EntityFramework does definitely walk down your object tree an attached all objects to it's context. I think Linq2Sql does it also.

Answer 2

Looks like obj was built using a different dataContext and that needs to be created using the same dataContext rather than instantiating a new one.

A quick solution might be to pass in a dataContext rather than instantiating a new one inside this method.

Answer 3

A quick solution might be to pass in a dataContext rather than instantiating a new one inside this method

If you do this then you won't be able to drop(undo) changes in case there is an error thrown when you hit SubmitChanges().

You can set foreign key of any relationship manually and immediately invoke SubmitChanges. If the relationship already has a defined foreign key then you will get an error and will need to assign object. In your case ati.ItemStatus = newItemStatus. But in this case you need to save in the same data context, which won't let you undo changes. :(

Answer 4

The fields that represent foreign keys (ati.fkItemStatusId = ti.fkItemStatusId, for example) need to exist in the current context. Try something like this:

ati.fkItemStatus = dataContext.ItemStatuses.SingleOrDefault(
    d=>d.ItemStatus.ItemStatusId.Equals(ti.fkItemStatusId);

If you do that, the foreign key object will exist in the context because you will have pulled it. Don't worry about a performance hit- it will get wrapped up into a simple SQL statement.