Using Postgres in Asp.net 5 (vnext) with Npgsql

Question

I have configured my app to connect to a Postgres database, but when loading some data to it, it gives me an error.

First, here is what I include in project.json:

"EntityFramework7.Npgsql": "3.1.0-rc1-1",

and in Startup.cs class:

services.AddEntityFramework()
            .AddNpgsql()
            .AddDbContext<ApplicationDbContext>(options =>
                    options.UseNpgsql(Configuration["Data:DefaultConnection:ConnectionString"]));

here is my connection string:

Server=127.0.0.1;Port=5432;Database=test1;User Id=postgres;Password=mypass123

The dnx command to update database works very well, but I had to create manually the "__EFMigrationsHistory" table.

The problem is the following error, when executing context.SaveChanges:

{Microsoft.Data.Entity.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.NpgsqlException: 23502: null value in column "Id" violates not-null constraint em Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) em Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) em Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode) em Npgsql.NpgsqlDataReader.ReadMessage() em Npgsql.NpgsqlDataReader.Init() em Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) em Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior) em Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) em System.Data.Common.DbCommand.ExecuteReader() em Microsoft.Data.Entity.Storage.Internal.RelationalCommand.<>c__DisplayClass17_0.b__0(DbCommand cmd, IRelationalConnection con) em Microsoft.Data.Entity.Storage.Internal.RelationalCommand.Execute[T](IRelationalConnection connection, Func3 action, String executeMethod, Boolean openConnection, Boolean closeConnection) em Microsoft.Data.Entity.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, Boolean manageConnection) em Microsoft.Data.Entity.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) --- Fim do rastreamento de pilha de exceções internas --- em Microsoft.Data.Entity.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) em Microsoft.Data.Entity.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection) em Microsoft.Data.Entity.Storage.RelationalDatabase.SaveChanges(IReadOnlyList1 entries) em Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList1 entriesToSave) em Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess) em Microsoft.Data.Entity.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess) em Microsoft.Data.Entity.DbContext.SaveChanges() em SQLite.Migrations.Seed.CriaEndereco() na F:\Dados\Documents\Visual Studio 2015\Projects\SQLite\src\SQLite\Migrations\Seed.cs:linha 73 em SQLite.Migrations.Seed.Execute(IServiceProvider applicationServices) na F:\Dados\Documents\Visual Studio 2015\Projects\SQLite\src\SQLite\Migrations\Seed.cs:linha 36 em SQLite.Startup.CreateSampleData(IServiceProvider applicationServices) na F:\Dados\Documents\Visual Studio 2015\Projects\SQLite\src\SQLite\Startup.cs:linha 138 em SQLite.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) na F:\Dados\Documents\Visual Studio 2015\Projects\SQLite\src\SQLite\Startup.cs:linha 128}

The Id columns should be assigned by entity, or not ?

Same code works in SqlServer.

update

Here is the SqlServer table schema:

CREATE TABLE [dbo].[Ufs] (
    [Id]    INT           IDENTITY (1, 1) NOT NULL,
    [Nome]  NVARCHAR (70) NOT NULL,
    [Sigla] NVARCHAR (2)  NOT NULL,
    CONSTRAINT [PK_Uf] PRIMARY KEY CLUSTERED ([Id] ASC)
);

and here Postgres:

CREATE TABLE public."Ufs"
(
  "Id" integer NOT NULL,
  "Nome" text NOT NULL,
  "Sigla" text NOT NULL,
  CONSTRAINT "PK_Uf" PRIMARY KEY ("Id")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public."Ufs"
  OWNER TO postgres;

All these codes was automatically generated by migrations.

But i think there is clear information what causes the problem: Npgsql.NpgsqlException: 23502: null value in column "Id" violates not-null constraint em Npgsql.NpgsqlConnector.DoReadSingleMessage. You are providing the null value to the column that is cannot have null values. Can you provide the schema of Postgre table? Maybe SqlServer version of this table has Identity setup for a column. — Rafał Czabaj, Dec 01 '15 at 10:07

score 3 · Accepted Answer · answered Dec 01 '15 at 10:35

3

It looks like you generated you Postgres database using SqlServer migrations.

I recommend you revert and remove all migrations and add migrations again.

answered Dec 01 '15 at 10:35

rabbit

104
4

score 1 · Answer 2 · edited May 23 '17 at 12:22

1

The "Id" Column in your Postgres Database should be a "Serial" type or something similar.

Postgres "Integer" type is not an auto-incremented type. By default it's the database's role to assign ids.

Try adding this annotation above your "Id" Property.

DatabaseGenerated(DatabaseGeneratedOption.Identity)

rel. This is a related question

edited May 23 '17 at 12:22

Community

1
1

answered Dec 01 '15 at 10:15

Anestis Kivranoglou

7,728
5
44
47

Using Postgres in Asp.net 5 (vnext) with Npgsql

update

2 Answers2