EF Core: Column with ValueGeneratedOnAdd trows "Cannot insert explicit value for identity column..."

Question

I have ASP.NET MVC 5 application that uses Entity Framework 6.2. This app creates some records in MS SQL server.

I have to process this records in AWS lambda function. For this purpose, I created c# .NET Core v2.0 lambda function. There I generate models from existed DB as described in the next article - Getting Started with EF Core on ASP.NET Core with an Existing Database.

Generated models look like:

public partial class Request
{
    public int EntityId { get; set; }   
    public Result Result { get; set; }
}
public partial class Result
{
    public int EntityId { get; set; }
    public Request Entity { get; set; }
}

And OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Request>(entity =>
    {
        entity.HasKey(e => e.EntityId);
        entity.ToTable("Request", "mySchema");
    });

    modelBuilder.Entity<Result>(entity =>
    {
        entity.HasKey(e => e.EntityId);
        entity.ToTable("Result", "mySchema");
        entity.HasIndex(e => e.EntityId).HasName("IX_EntityId");
        entity.Property(e => e.EntityId).ValueGeneratedOnAdd();
        entity.HasOne(d => d.Entity)
            .WithOne(p => p.Result)
            .HasForeignKey<Result>(d => d.EntityId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_mySchema.Result_mySchema.Request_EntityId");
    });
}

In the lambda function, I am processing a request and setting result:

using (var db = new DataContext(connection))
{
    var request = db.Request.First();    
    request.Result = new Result();
    db.SaveChanges();
}

On the save operation I got the exception:

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

I checked different way to solve this issue. But there are already set ValueGeneratedOnAdd function in model creating method.

Also, I tried to add [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to Result.EntityId - without any success.

What did I miss in my implementation?

Can you show the existing Request table you are trying to map? There is something wrong with PK and FK configuration, but can't say exactly w/o seeing the table columns and constraints. — Ivan Stoev
Hey RredCat, where you able to resolve this problem? I have same one... — Borbea
@Borbea, Yes. But I don't work anymore at this company and I don't have access to the source code and solution. As I remember, it was a pretty simple solution. — RredCat
@rredCat I solved my problem using ValueGeneratedOnAddOrUpdate because I was receiving above error on record update. Thanks anyway! — Borbea

Riddhi Riddhi · Accepted Answer · 2018-03-14T08:00:02

You are trying to initialize request.Result with new Result() which has no values. That may cause this error.

EF Core: Column with ValueGeneratedOnAdd trows "Cannot insert explicit value for identity column..."

1 Answers