EntityFramework Core - Copying an entity and placing it back into the database

Question

Is there a best practice for doing a copy of an entity, making some changes to it based on user input, and then re-inserting it into the database?

Some other Stackoverflow threads have mentioned that EF will handle inserting new objects for you even if the same primary key exists in the database, but I'm not quite sure that's how EF Core is handling it. Whenever I try and copy an object I get an error of

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Basically I just need a clean way to copy an object, make some changes to it based on user input, and then insert that copy back into the database, and have the Id auto-increment properly. Is there a best practice or simple way of doing this without having to manually set properties to null or empty?

EDIT: Example code for retrieving the object from the database:

    public Incident GetIncidentByIdForCloning(int id)
    {
        try
        {
            return _context.Incident.Single(i => i.IncidentId == id);
        }
        catch
        {
            return null;
        }
    }

Code after retrieving object (As some fields are auto-generated like RowVersion which is a Timestamp):

public IActionResult Clone([FromBody]Incident Incident)
    {
        var incidentToCopy = _incidentService.IncidentRepository.GetIncidentByIdForCloning(Incident.IncidentId);
        incidentToCopy.IncidentTrackingRefId = _incidentService.IncidentRepository.GetNextIdForIncidentCategoryAndType(
            Incident.IncidentCategoryLookupTableId, Incident.IncidentTypeLookupTableId).GetValueOrDefault(0);
        incidentToCopy.RowVersion = null;
        incidentToCopy.IncidentId = 0; //This will fail with or without this line, this was more of a test to see if manually setting would default the insert operation, such as creating a brand new object would normally do.
        incidentToCopy.IncidentCategoryLookupTableId = Incident.IncidentCategoryLookupTableId;
        incidentToCopy.IncidentTypeLookupTableId = Incident.IncidentTypeLookupTableId;
        var newIncident = _incidentService.IncidentRepository.CreateIncident(incidentToCopy);
...

I realize I could just make an entirely new object and do left-hand copying, but that seems terribly inefficient and I want to know if EF Core offers better solutions.

Possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — Curiousdev, Apr 24 '17 at 11:45
Can you share the code which retrieves the entity and tries to insert back to the dba after modifying it? Basically based in error I would suggest to set the id property to default zero or null before reinserting it to the db. — Chetan, Apr 24 '17 at 11:45
I have tried setting it to 0, and when it tries to insert the error is still thrown, and inspecting it in locals gives -2147483647 (which I assume comes from the database erroring out the insert). I will edit in some code information in my OP in a second. As for curious dev, I would prefer not to set IDENTITY_INSERT to on ever, as this will end up in a production environment, and that seems like a really big potential can of worms if something goes wrong. — Robert McCoy, Apr 24 '17 at 11:51

score 12 · Accepted Answer · answered Apr 24 '17 at 20:00

So I went through the "Possible duplicate" thread a bit more than I did when I initially stumbled upon it before creating this one, and there was a not-so-highly upvoted solution that I overlooked that essentially just grabs all of the values at once when retrieving the object from the database - and it doesn't retrieve a reference to that object in the process. My code now looks something like this:

try
{
    var incidentToCopy = _context.Incident.Single(i => i.IncidentId == id);
    return (Incident) _context.Entry(incidentToCopy).CurrentValues.ToObject();
}

You can also call `_context.Entry(incident).State = EntityState.Added` to change the state of the object to Added. Which will cause EF to ignore the key value, insert it, and update object's key with new DB generated key value. — John C, Sep 27 '22 at 20:26

dkmann · Answer 2 · 2017-04-27T15:44:55.250

6

In your IncidentRepository class try getting the Incident by using AsNoTracking and it should get tracked as a new entity when it is added.

public void Clone(int id)
{
    // Prevent tracking changes to the object.
    var incident = _context.AsNoTracking().SingleOrDefault(i => i.Id == id);

    // Setting back to 0 should treat the object Id as unset.
    incident.Id = 0;

    // Add the Incident while it is untracked will treat it as a new entity.
    _context.Incidents.Add(incident);
    _context.SaveChanges();
}

edited Apr 27 '17 at 15:44

answered Apr 24 '17 at 14:36

dkmann

611
2
8
18

I believe this caused issues when trying to insert it back into the database, as it knew it was not being tracked and did not like that very much. I went ahead and grabbed all of the CurrentValues of the object in my retrieval method and now it appears to be working (see my response to this thread for more info) – Robert McCoy Apr 24 '17 at 19:58
@RobertMcCoy DbContext does not know about non-tracked entities. Calling Add enables tracking on the entity so it is queued to be added to the database. What exception did you receive upon running the code? – dkmann Apr 24 '17 at 20:14
This wont work with auto-incrementing key, as it'll interpret the 0 as an explicit id set – Captain Prinny Dec 10 '20 at 15:01
@CaptainPrinny this does work. As it was explained that when using the Add() method, it will change the State of the entity to Added. In this state, and if the key is configured as auto incremental, EF will ignore the value of the key and let the db auto assign it. This is tried and true. You can also use `_context.Entry(incident).State = EntityState.Added` to duplicate a tracked object. – John C Sep 27 '22 at 20:23

score 0 · Answer 3 · answered Apr 25 '17 at 12:33

I believe what is happening here is the following:

When you retrieve a value from the database, it gets stored in something like

context.ChangeTracker.Entries<Incident>

Which is a collection of Incident entries being tracked. When you change the id property of the incident object you've retrieved you are sort of abusing the ChangeTracker in the name of efficiency. The ChangeTracker does not believe you have created a new object. You might be able to try something like finding the entry in the ChangeTracker and set it's state to detached then after you've set the id to 0 add the object back to the context.DbSet but at that point you have probably made things way more complicated than simply copying the object.

EntityFramework Core - Copying an entity and placing it back into the database

3 Answers3

Linked