Replicate Entire Row In Entity Framework Core

问题:

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)

回答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.

回答2:

The error is simple, it is because you are adding the identity insert as a part of insert. If it is identity, it has to be auto generated. So either turn off before insert and then turn it on. Or make it auto generated.

This error will be the same if you try and insert the same data from sql server. This is basically propagated from sql server.

If you do not want an ID to be database generated, then you should use the DatabaseGenerated attribute on your model, as in

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ScheduleId {get;set;}

If you want the identity insert to be off you can try the technique here