CODEWITHSUNDEEP
c#asp.net-mvcpostgresql
Beetlejuice
Beetlejuice

Reputation: 4425

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

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.

Upvotes: 3

Views: 841

Answers (2)

rabbit
rabbit

Reputation: 104

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

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

Upvotes: 3

Anestis Kivranoglou
Anestis Kivranoglou

Reputation: 8164

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

Upvotes: 1

Related Questions