I am doing a crud for Destinations table in a asp.net core project. ( using ef core code first method )

when create a new Destinations I got error in debug console like this.

Exception data:
    MessageText: duplicate key value violates unique constraint "PK_Destinations"
    Detail: Key ("Id")=(33) already exists.
    SchemaName: public
    TableName: Destinations
    ConstraintName: PK_Destinations

For some reasons We have already create a Destinations table and added data manually. And now doing a crud for that table. I guess that's why this error Key ("Id")=(33) already exists. occurs.

DbContext.cs

public DbSet<Destination> Destinations { get; set; }

service.Cs

  public async Task<Destination> Create(Destination destination)
        {
            await _catalogDbContext.Destinations.AddAsync(destination);
            await _catalogDbContext.SaveChangesAsync();
            return destination;
        }

My Question is

• I have 100 rows in my table. We can not remove them. So destination id should start from 301 when adding new destination.

• Our development Database has 200 rows in our table. We can not remove them. So destination id should start from 201 when adding new destination.

• Our production Database has 240 rows in our table. We can not remove them. So destination id should start from 241 when adding new destination.

How can I do, when create a destination Id should starting from 101, 201 or 241. And The id should increase automatically after a new one is added.?

Edit: Destination.cs

public class Destination
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; private set; }

        [Required]
        public string Continent { get; set; }

        [Required]
        public string Country { get; set; }

        [Required]
        public string Iso { get; set; }

        [Required]
        public string Name { get; set; }
}

CodePudding user response：

Possible reason for issue is that id column is primary key and record with value 33 is already exist.

there are two possible solution are

1. delete existing record with value 33 and reinsert record.
2. reset seeding to value you want

steps are as below.

1. get current seed value for identity column. check current seed: DBCC CHECKIDENT ('yourtablename');
2. then reseed it to value you want. set new seed ; DBCC CHECKIDENT ('yourtablename', RESEED, 200)

then try to insert again.

CodePudding user response：

When you add a new entity, it's better to let Id value as default value, don't try to assign it. (For example, Id is int so let the value = 0)

public async Task<Destination> Create()
{
     var destination = new Destination
     {
        Continent = "What ever",
        Country = "...."
        // Init all required properties BUT Id
     };

     // At this line, destination.Id == 0 because you didn't assign a value
     await _catalogDbContext.Destinations.AddAsync(destination);
     await _catalogDbContext.SaveChangesAsync();

     // At this line, you should see destination.Id > 0, the value is assigned by EntityFramework
     return destination;
}