Using Attach with Linq To Sql and Stored Procs

Question

Hello,

I am trying to use the attach method to update an entity that was retrieve via a stored proc.

The stored proc is set up to return a specific instance, which is present in my dbml. The retrieval works as expected and returns a fully populated object. The reason I need to use a stored proc is that I need to update a property on that entity at the same time that it is retrieved.

After I have retrieved this entity, I am mapping it using AutoMapper to another model which is used in another tier of the app. This tier performs a few operations, and makes a change to the entity, and passes it back to the repository for updating.

The repository converts this business model back into a database model, and attempts to attach it to the datacontext in order to take advantage of the automagic updating.

No matter what combination of Attach(entity, true) Attach(entity) etc, and it gives me messages like "Row not found or changed" or "Unable to add an entity with the same primary key".

Does anyone have any experience with the Attach method and how it can be used to update entities that did not necessarily come from the data context using query syntax (ie in this case a stored proc)?

Thanks alot

Answer 1

1: is it the same data-context, and 2: is it the same entity instance (or one that looks like it)

This would only happen for the same data-context, I suspect. If it is the same entity, then it is already there; just call SumbitChanges. Otherwise, either use a second data-context or detach the original entity.

Answer 2

So if I retrieved the entity via a stored proc, is it being tracked by the datacontext?

The thing is.. I'm going from the data model, to a another model that is used by another component, and then back. Its not.. really the same instance, but it does have all the same properties.

IE

    public Models.Tag GetEntity()
         {
          var dbTag = db.PROJ_GetEntity((int)EntityStatuses.Created, (int)EntityStatuses.CreatingInApi).SingleOrDefault();
          return FromDb Entity(dbEntity);
         }


var appModel = GetEntity(); // gets an Entity from a stored proc (NOT GetEntity_RESULT)

appModel.MakeSomeChanges();

_Repo.Persist(appModel);

public void Persist(Models.AppModel model)
{
var dbEntity = Mapper.Map(model);
db.Attach(dbEntity);
db.SubmitChanges();
}

This is somewhat pseudo code like.. but it demostrates pretty much exactly what I am doing.

Thanks

Answer 3

First, if you are creating a copy of the object, making changes and then trying to attach the copied object to the same DataContext as the one with the original object in it, then this would probably result in the "Unable to add an entity with the same primary key" message. One way to handle this is: 1. Get object from DataContext 2. Make changes and map object (or vice versa - whatever order) 3. Update the original object with the new values made in the other tier 4. SubmitChanges on the DataContext containing the original object

or

1. Get the object from a DataContext and close the DataContext
2. Make your changes and do your mapping
3. Retrieve the object from the DataContext to which you want to save
4. Update that object with the values from your mapped object
5. SubmitChanges

Alternately, when you say you are using the proc because you need to update a property at the same time that you retrieve it, I'd need to see the proc, but if you are somehow committing this update after retrieving the information, then indeed the message "row not found or changed" is correct. This would be hard to do, but you could do it if you're loading the data into a temp table, doing the update, and then using a select from the temp table to populate the object. One thing you could try is setting that property, in the L2S designer, to AutoUpdate = Never and see if that makes the problem go away. If so, this is your problem.

Answer 4

I'm upvoting weenet's answer because he's right - you can't use Attach to apply the changes.

Unlike Entity Framework, you can only attach an L2S object to a datacontext if it has never been attached before - i.e. it's a newed entity that you want to Insert into a table.

This does cause numerous problems in multi-layered environments - however I've been able to get around many of the issues by creating a generic entity synchronisation system, which uses reflection and expression trees.

After an object has been modified, I run the dynamic delegate against a new object from the DC and the modified object, so that only the differences are tracked in the DC before generating the Update statement. Does get a bit tricky with related entities, though.