Aspnetcore: How to use PostgreSQL?

Created on 11 Dec 2019  路  10Comments  路  Source: dotnet/aspnetcore

I have a ASP.NET Core web application with AspNetCore.Identity Logins. It uses SQL Server for user authentication.

I need to use PostgreSQL database I have added some nuget packages (EF.* etc), changed the connection string and code

 public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
            });
//                options.UseSqlServer( Configuration.GetConnectionString("DefaultConnection")));
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseNpgsql( Configuration.GetConnectionString("DefaultConnection")));

            services.AddDefaultIdentity<IdentityUser>()
                .AddEntityFrameworkStores<ApplicationDbContext>();

            services.AddControllersWithViews()
                .AddNewtonsoftJson();
            services.AddRazorPages();
        }

But when I run it, it connects to the PostgreSQL database and tries to create the structure I receive the error, what is wrong? Why EF cannot create the structure? How to solve this problem?

  Executing endpoint 'WebApplication1.Controllers.HomeController.Index (WebA
pplication1)'
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "Index", controller = "Home", page = "", area
 = ""}. Executing controller action with signature Microsoft.AspNetCore.Mvc.IAct
ionResult Index() on controller WebApplication1.Controllers.HomeController (WebA
pplication1).
info: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[1]
      Executing ViewResult, running view Index.
info: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[4]
      Executed ViewResult - view Index executed in 302.6169ms.
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action WebApplication1.Controllers.HomeController.Index (WebAppli
cation1) in 315.3645ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'WebApplication1.Controllers.HomeController.Index (WebAp
plication1)'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 451.9255ms 200 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
      Request starting HTTP/1.1 GET https://localhost:5001/Identity/Account/Logi
n
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint '/Account/Login'
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[3]
      Route matched with {page = "/Account/Login", area = "Identity", action = "
", controller = ""}. Executing page /Account/Login
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[101]
      Executing handler method Microsoft.AspNetCore.Identity.UI.V4.Pages.Account
.Internal.LoginModel.OnGetAsync - ModelState is Valid
info: Microsoft.AspNetCore.Authentication.Cookies.CookieAuthenticationHandler[11
]
      AuthenticationScheme: Identity.External signed out.
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[102]
      Executed handler method OnGetAsync, returned result .
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[103]
      Executing an implicit handler method - ModelState is Valid
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[104]
      Executed an implicit handler method, returned result Microsoft.AspNetCore.
Mvc.RazorPages.PageResult.
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[4]
      Executed page /Account/Login in 104.6291ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint '/Account/Login'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 157.5038ms 200 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
      Request starting HTTP/1.1 POST https://localhost:5001/Identity/Account/Log
in application/x-www-form-urlencoded 264
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint '/Account/Login'
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[3]
      Route matched with {page = "/Account/Login", area = "Identity", action = "
", controller = ""}. Executing page /Account/Login
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[101]
      Executing handler method Microsoft.AspNetCore.Identity.UI.V4.Pages.Account
.Internal.LoginModel.OnPostAsync - ModelState is Valid
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.0 initialized 'ApplicationDbContext' using provi
der 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (182ms) [Parameters=[@__normalizedUserName_0='?
'], CommandType='Text', CommandTimeout='30']
      SELECT a."Id", a."AccessFailedCount", a."ConcurrencyStamp", a."Email", a."
EmailConfirmed", a."LockoutEnabled", a."LockoutEnd", a."NormalizedEmail", a."Nor
malizedUserName", a."PasswordHash", a."PhoneNumber", a."PhoneNumberConfirmed", a
."SecurityStamp", a."TwoFactorEnabled", a."UserName"
      FROM "AspNetUsers" AS a
      WHERE a."NormalizedUserName" = @__normalizedUserName_0
      LIMIT 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for cont
ext type 'WebApplication1.Data.ApplicationDbContext'.
      Npgsql.PostgresException (0x80004005): 42P01: relation "AspNetUsers" does
not exist
         at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__Rea
dMessageLong|0>d.MoveNext()
      --- End of stack trace from previous location where except
area-identity question
Source
picture Oleg26Dev

All 10 comments

relation "AspNetUsers" does not exist

@ajcvickers Seems like the database didn't get created?

blowdart picture blowdart on 11 Dec 2019

@Oleg26Dev You need to create (for Postgres, the SQL server one won't do) and run the proper "Migration" to create the database. This doesn't just happen by itself if you've taken an existing SQL Server-based project and simply changed the connection string/provider.

This is probably helpful: https://medium.com/@RobertKhou/asp-net-core-mvc-identity-using-postgresql-database-bc52255f67c4

willdean picture willdean on 11 Dec 2019
👍1

@blowdart btw, I have tried to create the db structure via cmd, it gives me an error
It seems it is not for PostgreSQL or it is an error
WHERE [NormalizedName] IS NOT NULL;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (31ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>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.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "["
    Position: 79
    File: scan.l
    Line: 1086
    Routine: scanner_yyerror
Oleg26Dev picture Oleg26Dev on 11 Dec 2019

btw, can I run script on the db and create the structure manually? will it work in that case?

Oleg26Dev picture Oleg26Dev on 11 Dec 2019

@Oleg26Dev You're trying to run a "migration" (the script which creates the tables) which was generated for SQL Server, not Postgres. It has a bunch of SQL Server specific stuff which doesn't run on Postgres.

You need to recreate the migration in such a way as to make a Postgres migration rather than a SQL server one.

Have a look at this comment (and the rest of that issue), which explains what you're seeing and how to deal with it: https://github.com/npgsql/efcore.pg/issues/404#issuecomment-392942176

willdean picture willdean on 11 Dec 2019
👍1

@willdean
Yes, you are right. It was Migration from the previous running for Sql Server.
What I did next:

  1. Deleted the folder Migrations.
    2.> dotnet ef migrations add mig01
    Build started...
    Build succeeded.
    info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
    Entity Framework Core 3.1.0 initialized 'ApplicationDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
    Done. To undo this action, use 'ef migrations remove'
    -
    Folder Migrations was created, it is ok.

  2. Run the App, click on Login entered some data and afer Submit:

A database operation failed while processing the request.
PostgresException: 42P01: relation "AspNetUsers" does not exist
Applying existing migrations for ApplicationDbContext may resolve this issue
There are migrations for ApplicationDbContext that have not been applied to the database

20191212080541_mig01
Apply Migrations

In Visual Studio, you can use the Package Manager Console to apply pending migrations to the database:

PM> Update-Database
Alternatively, you can apply pending migrations from a command prompt at your project directory:

dotnet ef database update

  1. Ok, I have clicked Apply Migrations button and receive the "An error occurred applying migrations, try applying them from the command line"

  2. dotnet ef database update

There is an error on
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,

but I see thet npgsql used and postgreSQL script used too like
character varying etc.

What am I doing wrong again?

I can attach my project, may be it can help to see what is incorrect.

...
MessageText: relation "AspNetUsers" does not exist
Position: 294
File: parse_relation.c
Line: 1159
Routine: parserOpenTable
Npgsql.PostgresException (0x80004005): 42P01: relation "AspNetUsers" does not ex
ist
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|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.ExecuteReaderAsync(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, Ca
ncellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnu merator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken can cellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStra tegy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifyS ucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnu
merator.MoveNextAsync()
Exception data:
Severity: ERROR
SqlState: 42P01
MessageText: relation "AspNetUsers" does not exist
Position: 294
File: parse_relation.c
Line: 1159
Routine: parserOpenTable
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[4]
Executed page /Account/Login in 2246.33ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
Executed endpoint '/Account/Login'
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeo
ut='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_name
space n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
Request finished in 2885.6528ms 500 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
Request starting HTTP/1.1 POST https://localhost:5001/ApplyDatabaseMigrati
ons application/x-www-form-urlencoded 135
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.0 initialized 'ApplicationDbContext' using provi
der 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeou
t='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_name
space n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeo
ut='30']
CREATE TABLE "__EFMigrationsHistory" (
"MigrationId" character varying(150) NOT NULL,
"ProductVersion" character varying(32) NOT NULL,
CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeou
t='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_name
space n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeou
t='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20191212080541_mig01'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (34ms) [Parameters=[], CommandType='Text', CommandTimeo
ut='30']
CREATE TABLE "AspNetRoles" (
"Id" text NOT NULL,
"Name" character varying(256) NULL,
"NormalizedName" character varying(256) NULL,
"ConcurrencyStamp" text NULL,
CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeo
ut='30']
CREATE TABLE "AspNetUsers" (
"Id" text NOT NULL,
"UserName" character varying(256) NULL,
"NormalizedUserName" character varying(256) NULL,
"Email" character varying(256) NULL,
"NormalizedEmail" character varying(256) NULL,
"EmailConfirmed" boolean NOT NULL,
"PasswordHash" text NULL,
"SecurityStamp" text NULL,
"ConcurrencyStamp" text NULL,
"PhoneNumber" text NULL,
"PhoneNumberConfirmed" boolean NOT NULL,
"TwoFactorEnabled" boolean NOT NULL,
"LockoutEnd" timestamp with time zone NULL,
"LockoutEnabled" boolean NOT NULL,
"AccessFailedCount" integer NOT NULL,
CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (220ms) [Parameters=[], CommandType='Text', Com
mandTimeout='30']
CREATE TABLE "AspNetRoleClaims" (
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"RoleId" text NOT NULL,
"ClaimType" text NULL,
"ClaimValue" text NULL,
CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("Role
Id") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);
fail: Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMid
dleware[7]
An error occurred while applying the migrations for 'WebApplication1.Data.
ApplicationDbContext'. See InnerException for details:
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERATED
"
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken canc
ellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(Re
lationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(
IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor .ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connecti
on)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String
targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(D
atabaseFacade databaseFacade)
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMid
dleware.Invoke(HttpContext context)
Exception data:
Severity: ERROR
SqlState: 42601
MessageText: syntax error at or near "GENERATED"
Position: 62
File: scan.l
Line: 1086
Routine: scanner_yyerror
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: An error occurred while applying the migration
s for 'WebApplication1.Data.ApplicationDbContext'. See InnerException for detail
s.Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERAT
ED"
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken canc
ellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(Re
lationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(
IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor .ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connecti
on)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String
targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(D
atabaseFacade databaseFacade)
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMid
dleware.Invoke(HttpContext context)
Exception data:
Severity: ERROR
SqlState: 42601
MessageText: syntax error at or near "GENERATED"
Position: 62
File: scan.l
Line: 1086
Routine: scanner_yyerror
---> Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GEN
ERATED"
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken canc
ellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(Re
lationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(
IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor .ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connecti
on)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String
targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(D
atabaseFacade databaseFacade)
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMid
dleware.Invoke(HttpContext context)
Exception data:
Severity: ERROR
SqlState: 42601
MessageText: syntax error at or near "GENERATED"
Position: 62
File: scan.l
Line: 1086
Routine: scanner_yyerror
--- End of inner exception stack trace ---
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMid
dleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMidd
leware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMidd
leware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(H
ttpContext context)
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
Request finished in 879.2697ms 500 text/plain

Oleg26Dev picture Oleg26Dev on 12 Dec 2019

There is an error on
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY, - i have PostgreSQL 9.6.
It seems EF generates scripts for PG>=10.

Ok, I have 11, will try it.
But the question: I need to deploy the app which works with PostgreSQL 9.6 in AWS.
What to do in that case?

Oleg26Dev picture Oleg26Dev on 12 Dec 2019

@Oleg26Dev Have a look at the 'breaking changes' section here: https://www.npgsql.org/efcore/release-notes/3.0.html which has a work-around for this problem (read the WHOLE Breaking Changes section, because it specifically addresses pre 10 versions right at the bottom.)

This isn't really a problem with ASP.NET Core, and npgsql is not an MS product. You might well find that StackOverflow is a better place to ask for this sort of help - this github project is about deficiencies with ASP.NET core, not for end-user support - I'm not an MS person, but I suspect they're about to close this.

willdean picture willdean on 12 Dec 2019

ok, thnx, @willdean , sorry for the questions.

Oleg26Dev picture Oleg26Dev on 12 Dec 2019

@willdean Thanks for answering the questions here. Much appreciated.

ajcvickers picture ajcvickers on 12 Dec 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

fayezmm picture fayezmm  路  3Comments
groogiam picture groogiam  路  3Comments
UweKeim picture UweKeim  路  3Comments
Kevenvz picture Kevenvz  路  3Comments
rynowak picture rynowak  路  3Comments