Ef Code First: Cannot Insert Explicit Value For Identity Column In Table '' When Identity_insert Is Set To Off

I have issue with EF code first when I am trying to insert new record into table I recieve message. Cannot insert explicit value for identity column in table '' when IDENTITY_INSER

Solution 1:

Ok, I have found it. The database has been set up correctly, but my mapping has been incorrect.

publicclasstblResponsMap : EntityTypeConfiguration<tblRespons>
{
public tblResponsMap()
{
    // Primary Keythis.HasKey(t => new { t.lngResponseLineID});

    // Propertiesthis.Property(t => t.lngResponseLineID)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); <-- here

    this.Property(t => t.lngRequestLineID);

    // Table & Column Mappingsthis.ToTable("tblResponses");
    this.Property(t => t.lngResponseLineID).HasColumnName("lngResponseLineID");
    this.Property(t => t.lngRequestLineID).HasColumnName("lngRequestLineID");
    this.Property(t => t.fAdhoc).HasColumnName("fAdhoc");
    this.Property(t => t.memXMLResponse).HasColumnName("memXMLResponse");
}
}

Solution 2:

You don't need to change mapping code, instead you should change this line:

lngRequestLineID = 1001233

to this:

lngRequestLineID = 0

In my expirence decorating de Id entity's property with [KeyAttribute] is enought.

Solution 3:

I was encountering this error when I was adding a relationship to EF core entity model by pointing it to an entity model that was fetched outside of the current context and was no longer being tracked. I was doing this to avoid an extra read operation which initially felt awkward to me.

publicstaticvoidAddMatches(List<Match> matches, Profile p)
{
    using (var db = new DbContext())
    {
        foreach (var match in matches)
        {
            match.Profile = p;
            db.Match.Add(match);
        }
        db.SaveChanges();
    }
}

I solved it by doing another read operation. After consulting the Microsoft docs Here, I was reassured that this is relatively normal especially in the MVC world. Fix:

publicstaticvoidAddMatches(List<Match> matches, Profile p)
{
    using (var db = new DbContext())
    {
        var profile = db.Profile.Find(p.ProfileId);
        foreach (var match in matches)
        {
            match.Profile = profile;
            db.Match.Add(match);
        }
        db.SaveChanges();
    }
}

Solution 4:

EF Core 5, Explicitly setting the 'Id' property to default solved this issue in my case:

publicclassPurchaseOrder
    {

        publicint Id { get; set; }
        publicstring FileName { get; set; }
        publicstring FilePath { get; set; }
        ...
    }

By default, Id column is identity if you follow the naming convention ('Id' as column name), otherwise use like below

protectedoverridevoidOnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        ...

        builder.Entity<BranchLocationBin>(entity =>
            {
                entity.Property(e => e.BId).UseIdentityColumn();
            });
    }

Now while creating instances, explicitly set the Id column to default (0 in case of int):

var model = new PurchaseOrder {
                Id = 0  <---- Like this
                ...
            };
                        
           await dbContext.PurchaseOrders.AddAsync(model);
           await dbContext.SaveChangesAsync();