0

I have model like below. While inserting data into SQL Server database, I want that 'id' column will be identity column and rest properties(NPI, Name) will be passed from my GetData() method.

But if I run Add-Migration in package manager console, I am getting the below error "the seed entity for entity type 'EmpModel' cannot be added because a non-zero value is required for property 'id'. Consider providing negative value to avoid collisions with non-seed data". Can you help me to solve the issue please?

public class EmpModel
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int id { get; set; }
        public string NPI { get; set; }
        public string Name { get; set; }
    }

I have context class like below,

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

    }  
    
    protected override void OnModelCreating(ModelBuilder builder)
    {
        EmployeeFile file = new EmployeeFile();
        var response = file.GetData(); // getting data

        builder.Entity<EmpModel>.HasData(
            new EmpModel()
            {
                NPI = response.npiId,
                Name = response.FullName
            }
            );
        

        builder.Entity<EmpModel>().Property(p => p.id).ValueGeneratedOnAdd();
        builder.Entity<EmpModel>().HasKey(x => new { x.id}); //defining the primary key

    }
}
Amar
  • 19
  • 1
  • 2
  • Try moving your seeding after you set the Identity column on the property. – GH DevOps Aug 26 '21 at 14:17
  • do you mean that I need to put .Property() & .HasKey() before seeding data, HasData() method? @GHDevOps – Amar Aug 26 '21 at 14:20
  • Yes you may need to set it in the model builder first before seeding. Also change ValueGeneratedOnAdd() to UseSqlServerIdentityColumn(). – GH DevOps Aug 26 '21 at 14:40
  • BtW if you are using the FluentAPI to set the primary key, you don't need to do the `[key]` on the Model – Camadas Aug 26 '21 at 15:32
  • @GHDevOps, I set the model first and after that trying to seed data but it fails! I changed the ValueGeneratedOnAdd() as well but no luck! – Amar Aug 26 '21 at 16:54
  • @Camadas , I am trying to set the primary key by generating auto increment (Identity) value at database level from code. I tried by removing [Key] as well but it is showing same error. – Amar Aug 26 '21 at 16:56
  • @Amar Seeding with `HasData` **requires** you to provide the PK values even though normally they are auto-generated. This is what error message is trying to tell you, although in their own style. The essential is *"**non-zero value** is required for property 'id'"*. And suggesting to use negative values in order to not mess with the normal auto-increment values. – Ivan Stoev Aug 26 '21 at 19:02
  • @IvanStoev, I cant declare any column as primary key what I am getting from response. I can only set the auto-incremented 'id' as primary key. Pls guide me how that can be solved or give me other suggestion – Amar Aug 27 '21 at 13:50
  • What is unclear? You **must** provide `Id` for `HasData` method. `new EmpModel() { Id = /* something, must be constant value taken from the file with data */, ... }` – Ivan Stoev Aug 27 '21 at 14:23
  • @IvanStoev, I don't have Id field in the response from the file itself. I am getting only NPI and Name from response so while doing new EmpModel() inside HasData(), I can't write Id = /*something*/ because I don't have value for Id. I am expecting value for Id will be auto-incremented and set in seed data – Amar Aug 27 '21 at 16:08
  • @Amar It doesn't matter what are you expecting. I wrote it in bold in my previous comments, don't know how to say it more clear - EF Core is **expecting** / **requiring** the `Id` **when seeding data** - period. Here is the link to the official EF Core documentation - [Limitations of model seed data](https://learn.microsoft.com/en-us/ef/core/modeling/data-seeding#limitations-of-model-seed-data): *"The primary key value needs to be specified even if it's usually generated by the database. It will be used to detect data changes between migrations."* – Ivan Stoev Aug 27 '21 at 17:43

0 Answers0