13

I'm bulding an application and when I want to insert a form into my form table I get the following error:

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

These are my models:

Form model:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [ForeignKey("FormType")]
    public int? TypeId { get; set; }
    public virtual FormType Type { get; set; }

    [ForeignKey("FormStatusType")]
    public int? StatusTypeId { get; set; }
    public virtual FormStatusType StatusTknype { get; set; }

    [ForeignKey("Relation")]
    public int? SupplierId { get; set; }
    public virtual Relation Supplier { get; set; }

    [ForeignKey("Relation")]
    public int? CustomerId { get; set; }
    public virtual Relation Customer { get; set; }

    public String SupplierReference { get; set; }
    public Guid ApiId { get; set; }
    public DateTime DueDate { get; set; }
    public FormFile FormFiles { get; set; }
    public String FormName { get; set; }
    public DateTime UploadDate { get; set; }

Relation model:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [ForeignKey("FormType")]
    public int? TypeId { get; set; }
    public virtual FormType Type { get; set; }

    [ForeignKey("FormStatusType")]
    public int? StatusTypeId { get; set; }
    public virtual FormStatusType StatusTknype { get; set; }

    [ForeignKey("Relation")]
    public int? SupplierId { get; set; }
    public virtual Relation Supplier { get; set; }

    [ForeignKey("Relation")]
    public int? CustomerId { get; set; }
    public virtual Relation Customer { get; set; }

    public String SupplierReference { get; set; }
    public Guid ApiId { get; set; }
    public DateTime DueDate { get; set; }
    public FormFile FormFiles { get; set; }
    public String FormName { get; set; }
    public DateTime UploadDate { get; set; }

My context looks like this:

public class DataContext: DbContext
{
    public DataContext(DbContextOptions<DataContext> options): base(options)
    {

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer();
    }

    public DbSet<Relation> Relation { get; set; }
    public DbSet<Setting> Settings { get; set; }
    public DbSet<Notification> Notification { get; set; }
    public DbSet<FormStatusType> FormStatusType { get; set; }
    public DbSet<File> File { get; set; }
    public DbSet<FormFile> FormFile { get; set; }
    public DbSet<FormType> FormType { get; set; }
    public DbSet<Form> Form { get; set; }
    public DbSet<User> User { get; set; }
    public DbSet<RelationUser> RelationUser { get; set; }
    public DbSet<SupplierCustomer> SupplierCustomer { get; set; }

}

The method I use to add a form looks like this:

 public async Task<Form> AddForm(Form form, int currentUserId)
    {
        try
        {
            if (form != null)
            {
                //huidige gebruiker als supplier aanduiden
                Relation r = await GetCurrentUser(currentUserId);
                form.Supplier = r;
                form.SupplierId = r.Id;

                //form aan de db toevoegen
                _datacontext.Form.Add(form);
                _datacontext.SaveChanges();

                return form;
            }
            else
            {
                return null;
            }
        }
        catch (Exception e)
        {
            LogError(e);
            return null;
        }
    }

The get current user method

 private async Task<Relation> GetCurrentUser(int currentUserId)
    {
        var relation = from r in _datacontext.RelationUser
                       where r.UserId == currentUserId
                       select r.Relation;
        return await relation.FirstOrDefaultAsync();
    }

This is where I call the AddForm method:

 [HttpPost]
    [Route("addform")]
    [Authorize]
    // api/form/addform
    public async Task<IActionResult> AddForm([FromBody] Form form)
    {
        if (ModelState.IsValid)
        {
            Form f = await _formRepository.AddForm(form, GetUserIdFromToken());

            if(f != null)
            {
                QueueObject qo = new QueueObject()
                {
                    ActionTypeId = 1,
                    FormId = f.Id
                };
                await new QueueHandler().SendMessageToQueue(qo);
            }

            return Ok(f);
        }
        else
        {
            return NotFound("model is niet geldig");
        }
    }

I already searched but found nothing that solved the problem

Steven Mollie
  • 133
  • 1
  • 1
  • 7

2 Answers2

6

Another possible reason this may happen, is if you have a timeout in some call to SaveChanges when trying to insert new entities to your database, then try calling SaveChanges again, using the same DbContext instance.

This is reproducible:

using(var context = new MyDbContext())
{
    context.People.Add(new Person("John"));
    try
    {
        // using SSMS, manually start a transaction in your db to force a timeout
        context.SaveChanges();
    }
    catch(Exception)
    {
        // catch the time out exception
    }
    // stop the transaction in SSMS
    context.People.Add(new Person("Mike"));
    context.SaveChanges(); // this would cause the exception
}

This last SaveChanges would cause Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF.

Alisson Reinaldo Silva
  • 10,009
  • 5
  • 65
  • 83
  • This was one of the issues with my port. Apparently we were doing this all over the place on EF6? Weird. – Chaim Eliyah Jan 11 '19 at 19:52
  • 1
    @ChaimEliyah that's right. I still think this is a bug in EF Core, I had some legacy scheduled jobs that would execute database heavy routines where occasionally that timeout would happen for one entry between thousands. That was fine, that entry would be processed next time the job ran, and between each execution of the job I'd reuse the same `DbContext`. That would not be possible with EF Core anymore, I'd be forced to create a new database (idk if there is another workaround for this). – Alisson Reinaldo Silva Jan 11 '19 at 23:09
4

You have multiple errors on your model. The ForeignKey attribute must point to properties in the class, not to the type of the dependent entity:

//FORM MODEL
[ForeignKey("Type")]
public int? TypeId { get; set; }
public virtual FormType Type { get; set; }

[ForeignKey("StatusTknype")]
public int? StatusTypeId { get; set; }
public virtual FormStatusType StatusTknype { get; set; }

[ForeignKey("Supplier")]
public int? SupplierId { get; set; }
public virtual Relation Supplier { get; set; }

[ForeignKey("Customer")]
public int? CustomerId { get; set; }
public virtual Relation Customer { get; set; }

//RELATION MODEL
[ForeignKey("Type")]
public int? TypeId { get; set; }
public virtual FormType Type { get; set; }

[ForeignKey("StatusTknype")]
public int? StatusTypeId { get; set; }
public virtual FormStatusType StatusTknype { get; set; }

[ForeignKey("Relation")]
public int? SupplierId { get; set; }
public virtual Relation Supplier { get; set; }

[ForeignKey("Customer")]
public int? CustomerId { get; set; }
public virtual Relation Customer { get; set; }

Also, if you followed Convention Over Configuration, you could drop the ForeignKeyAttribute completely by just naming the properties conventionally:

public int? StatusTypeId { get; set; }
public virtual FormStatusType StatusType { get; set; }
Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120