How to work with EF PostgreSQL if it throw null value in column "ReportId" (Pimary key) violates not-null constraint

0

I'm trying to change database from SQLite to PostgreSQL. But i'am getting error about non null primary key while creating new record in db. Though with SQLite it works fine.

I did remove and regenerate migration for new database.

ASP.NET Core 2.1.1 EF 2.1.1 Npgsql.EntityFrameworkCore.PostgreSQL 2.1.1

SQL in pgAdmin


-- DROP TABLE public."Reports";

CREATE TABLE public."Reports"
(
    "ReportId" integer NOT NULL,
    "ObjectName" text COLLATE pg_catalog."default",
    "PartName" text COLLATE pg_catalog."default",
    "Description" text COLLATE pg_catalog."default",
    "Image" bytea,
    "Date" timestamp with time zone,
    CONSTRAINT "PK_Reports" PRIMARY KEY ("ReportId")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."Reports"
    OWNER to postgres;
//Model
 public class Report
    {
        [Key]
        public int ReportId { get; set; }
        public string ObjectName { get; set; }
        public string PartName { get; set; }
        public string Description { get; set; }
        public byte[] Image { get; set; }
        public DateTimeOffset? Date { get; set; }
    }

//Migration
  public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Reports",
                columns: table => new
                {
                    ReportId = table.Column<int>(nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
                    ObjectName = table.Column<string>(nullable: true),
                    PartName = table.Column<string>(nullable: true),
                    Description = table.Column<string>(nullable: true),
                    Image = table.Column<byte[]>(nullable: true),
                    Date = table.Column<DateTimeOffset>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Reports", x => x.ReportId);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Reports");
        }
    }
//Snapshot
   [DbContext(typeof(ReportDbContext))]
    [Migration("20190710072320_InitialCreate")]
    partial class InitialCreate
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn)
                .HasAnnotation("ProductVersion", "2.1.11-servicing-32099")
                .HasAnnotation("Relational:MaxIdentifierLength", 63);

            modelBuilder.Entity("BridgesWebApp.Models.Report", b =>
                {
                    b.Property<int>("ReportId")
                        .ValueGeneratedOnAdd();

                    b.Property<DateTimeOffset?>("Date");

                    b.Property<string>("Description");

                    b.Property<byte[]>("Image");

                    b.Property<string>("ObjectName");

                    b.Property<string>("PartName");

                    b.HasKey("ReportId");

                    b.ToTable("Reports");
                });
#pragma warning restore 612, 618
        }
    }
//Code in Db Repository
public async Task CreateReport(Report report)
        {
            context.Reports.Add(report);
            await context.SaveChangesAsync();
        }

As i expect it should just set and icrement ReportId with generated migrations. But it throw error logs with:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (59ms) [Parameters=[@p0='?' (DbType = DateTimeOffset), @p1='?', @p2='?' (DbType = Binary), @p3='?', @p4='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Reports" ("Date", "Description", "Image", "ObjectName", "PartName")
      VALUES (@p0, @p1, @p2, @p3, @p4)
      RETURNING "ReportId";
Npgsql.PostgresException (0x80004005): 23502: null value in column "ReportId" violates not-null constraint
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'BridgesWebApp.Data.ReportDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23502: null value in column "ReportId" violates not-null constraint
c#
postgresql
entity-framework
asp.net-core
database-migration
asked on Stack Overflow Jul 10, 2019 by Denis Efimcev

1 Answer

0

In pgAdmin i found that there is one of old migration was used it making problem for creating new report. Don't forget to check which migration are applied to database.

answered on Stack Overflow Jul 12, 2019 by Denis Efimcev

User contributions licensed under CC BY-SA 3.0