Replicate Entire Row In Entity Framework Core

Question

I am trying to retrieve a row from database , changing certain columns value in it and adding it as new row (Entity Framework Core),

But it is giving me error

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

This table have a Primary Key "ScheduleId"

Below is my Code

 AuditSchedules _schedules = new AuditSchedules();
 using (var ctx = new QuestionnaireEntities(_configuration))
                {
                    _schedules = ctx.AuditSchedules.Where(x => x.ScheduleId == model.ScheduleID).SingleOrDefault();
                    _schedules.StaffId = model.TransferedAuditorCode;
                    _schedules.StaffName = model.TransferedAuditorName;
                    _schedules.FromDate = _schedules.ToDate = Convert.ToDateTime(model.TransferedScheduleDate);
                    ctx.AuditSchedules.Add(_schedules);
                    ctx.SaveChanges();

                    _subschedules = ctx.AuditSubSchedule.Where(x => x.SubScheduleId == model.SubScheduleID).SingleOrDefault();
                    _subschedules.IsHoliDay = "Y";
                    _subschedules.HolidayType = model.HolidayType;
                    _subschedules.TransferedScheduleId = _schedules.ScheduleId.ToString();
                    ctx.AuditSubSchedule.Update(_subschedules);
                    ctx.SaveChanges();
                }

Error Comes In

ctx.AuditSchedules.Add(_schedules);

First I thought its conflicting in value of Schedule_ID and not able to add duplicate Primary Key , But Schedule_ID is auto generated field so this issue should not occur

I also tried setting it to different value

_schedules.ScheduleId = 0;

but it does not insert . How Can I replicate a row with few changes in it (want to add a new row but modified values)

Answer 1

EF Core behavior with auto generated values on insert is different than EF6.

First, the property must have default value (0) in order to be auto generated. This allows identity inserts which was not possible in EF6.

Second, the entity being added should not be already tracked by the context, because otherwise the context keeps internally some information that the entity key has been set and will include the value (even 0) in the generated INSERT command, which in turn causes the exception you are getting.

To achieve the goal, before calling the Add method:

First make sure the entity is not tracked by either using No-tracking query when obtaining it

_schedules = ctx.AuditSchedules
    .AsNoTracking() // <--
    .Where(x => x.ScheduleId == model.ScheduleID)
    .SingleOrDefault();

or explicitly detaching it

ctx.Entry(_schedules).State = EntityState.Detached;

Then reset the PK

_schedules.ScheduleId = 0;

The do other modifications and finally call

ctx.AuditSchedules.Add(_schedules);

This will work for simple entities w/o navigation properties / FKs. For complex entity graph you should use no tracking query, and then work with it the same way as with detached entity graphs.