5

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)

Tanwer
  • 1,503
  • 8
  • 26
  • 41
  • Much cleaner to make a new object and copy a few more fields. Also, your SingleOrDefault() should become just Single() . – H H Mar 20 '18 at 07:46
  • The error is clear enough, you need to clear out the PK of both tables. – H H Mar 20 '18 at 07:49
  • You didn't tell EF that `ScheduleId` is an identity column so it tries to insert a value into it. – Gert Arnold Mar 20 '18 at 07:58
  • @GertArnold , Than how SQL generate value of it when we use Add() Method , – Tanwer Mar 20 '18 at 08:47
  • You know what an identity column is, do you? Any insert generates a next value. `Schedule_ID` shouldn't be in the insert statement EF generates, that's the point. – Gert Arnold Mar 20 '18 at 08:51
  • @GertArnold , https://stackoverflow.com/questions/25720803/entity-framework-6-clone-object-except-id , its an old post but solve the same problem , neither he or me touching the Primary Key field . Funny thing is it does not work in my case – Tanwer Mar 20 '18 at 08:54
  • The difference is that in your case EF doesn't know that the column is an identity column. – Gert Arnold Mar 20 '18 at 09:35

2 Answers2

8

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.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • AsNoTracking() does not come in intellisence – Tanwer Mar 20 '18 at 12:38
  • 1
    Then you need `using Microsoft.EntityFrameworkCore;` (many EF Core methods are provided via extension methods) – Ivan Stoev Mar 20 '18 at 13:02
  • 1
    I get this working by using `UseSqlServerIdentityColumn()` on the Id property, using `AsNoTracking` and setting the id value of the added object to 0. When you mention that the property needs a default value, do you mean it must be a (CLR) datatype having a non-null default? In my test, `HasDefaultValue(0)` wasn't required (and redundant). BTW nice to know of yet another area where EF-core differs from EF6. This may be very useful in replication to other databases. Now it can be done without having to tamper with the mappings. – Gert Arnold Mar 21 '18 at 13:07
  • Hi @Gert I was assuming the property is already identity column, otherwise OP won't get that exception. And yes, I meant setting the auto generated property of the object to the CLR `default` value. The main difference with EF6 is that EF6 always ignores the current value of the identity column of the entity marked as `Added` as you know, while EF Core respects it. Indeed this allows identity inserts w/o changing the mappings :) – Ivan Stoev Mar 21 '18 at 13:33
0

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

Praneet Nadkar
  • 823
  • 7
  • 16