I am using EF Core 2.0 and Postgres 9.6. Whenever I insert data I get the error
PostgresException: 23505: duplicate key value violates unique constraint "PK_country"
By tracing it looks like EF doesnt generate AutoIncrement column
My Model
public partial class Country
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int CountryId { get; set; }
[Display(Name="Country Name")]
public string CountryName { get; set; }
}
My Controller
if (ModelState.IsValid)
{
_context.Add(country);
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Index));
}
Exception
> PostgresException: 23505: duplicate key value violates unique > constraint "PK_country" > > Npgsql.NpgsqlConnector+<DoReadMessage>d__148.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult() > Npgsql.NpgsqlConnector+<ReadMessage>d__147.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > Npgsql.NpgsqlConnector+<ReadMessage>d__147.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult() > Npgsql.NpgsqlDataReader+<NextResult>d__32.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > Npgsql.NpgsqlDataReader+<<NextResultAsync>b__31_0>d.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > Npgsql.NpgsqlCommand+<Execute>d__71.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult() > Npgsql.NpgsqlCommand+<ExecuteDbDataReader>d__92.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult() > Npgsql.NpgsqlCommand+<>c__DisplayClass90_0+<<ExecuteDbDataReaderAsync>b__0>d.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand+<ExecuteAsync>d__17.MoveNext() > System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() > System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task > task) > System.Runtime.CompilerServices.TaskAwaiter.GetResult() > Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch+<ExecuteAsync>d__32.MoveNext()
What could be the problem?
Even though the solution here was already found, the question does not specify that you load the data from some .sql
file. If you had been loading the data in the following way, this would have also generated the same error:
_context.Add(new Country { CountryId = 0, CountryName = "MyCountry" });
_context.Add(new Country { CountryId = 1, CountryName = "MyCountry" });
The reason is, that (I don't know why) the first row will have CountryId = 1
and the second one also CountryId = 1
. Which leads to duplicate key value.
This happens with: