PostgresException: 23505: duplicate key value violates unique constraint "PK_country"

asp.net-core-2.0 c# entity-framework entity-framework-core npgsql

Question

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?

1
3
6/10/2019 7:16:30 AM

Popular Answer

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:

  • Npgsql.EntityFrameworkCore.PostgreSQL 2.1.2
  • Microsoft.EntityFrameworkCore.Design 2.2.3
1
6/9/2019 10:28:05 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow