Problem With Inserting Record And Assigning Primary Key EF Core 3.0

Question

I have a simple model record with a key "Id" that I'm adding to a EF Entity. It worked through Core 2.1 but now fails with the error:

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

The Model is defined as follows:

namespace WebAppCore.Models
{
    public class SpeakerRec
    {
        [Key]
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string ImageUrl { get; set; }
    }
}

The Code that does the insert is this and it fails on the save changes call.

        foreach (var speaker in speakerRecs)
        {
            _context.SpeakerRecs.Add(speaker);
        }
        _context.SaveChanges();

I see notes about breaking changes in Core 3 and the problem is somehow around ValueGeneratedNever() but can't figure it out. I've not used EF for a while and was not planning on re-learning it. I was hoping my old code would continue to work.

score 2 · Accepted Answer · answered Nov 04 '19 at 03:59

If you want to manually insert the Id , you can use [DatabaseGenerated(DatabaseGeneratedOption.None)] . The None option prevents values from being generated by the database automatically in cases where they would otherwise be created :

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }

If you have created the migration before , when create new migration and update database , you may get exception like To change the IDENTITY property of a column, the column needs to be dropped and recreated , you can :

Delete the actual database
Delete the Migrations folder in your project and clean the project
Create a new migration with dd-migration <migration_name> in the Package Manager Console and then Update-database .

score 0 · Answer 2 · answered Nov 02 '19 at 06:34

0

You are inserting explicit values in the Id column, which is an identity column. To insert value explicitly into the identity column you have to turn on the identity insert.

If your database is automatically inserting value for the Id column, you should not specify them externally from your code.

Update your edmx file to reflect any changes you may have made in the database. If the database automatically assigns the value, you should see the "IsDbGenerated=true" attribute in your designer file under that property. If it's not there, you can add it manually.

Reference

answered Nov 02 '19 at 06:34

Kinjal Parmar

342
1
3
17

I thought Edmx files are no longer used – Peter Kellner Nov 02 '19 at 12:39
if you havent used edmx then you can migrate your database. – Kinjal Parmar Nov 04 '19 at 10:17
1

the other answer is correct. edmx are not part of the asp.net core EF solution – Peter Kellner Nov 05 '19 at 00:59

Problem With Inserting Record And Assigning Primary Key EF Core 3.0

2 Answers2