Entity framework core: Cannot insert explicit value for identity column in table 'Relation' when IDENTITY_INSERT is set to OFF

Question

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

Answer 1

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.

Answer 2

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; }