代码之家 › 专栏 › 技术社区 › Brijesh Ray

在.net core 3.0中将postgresql用作数据库时,代码优先错误中的数据迁移

asp.net-core-3.0 ef-code-first postgresql

0

Brijesh Ray · 技术社区 · 6 年前

我正在使用代码优先的方法迁移数据,db是postgresql,在add迁移时运行良好,但update database给出的错误为“42601:语法错误位于或接近“GENERATED”,更多详细信息如下:

> PM> add-migration migration
>     Build started...
>     Build succeeded.
>     To undo this action, use Remove-Migration.
>     PM> update-database
>     Build started...
>     Build succeeded.
>     [15:18:48 Error] Microsoft.EntityFrameworkCore.Database.Command
>     Failed executing DbCommand (298ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
>     CREATE TABLE "Customers" (
>         "CustomerId" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
>         "CustomerName" text NULL,
>         CONSTRAINT "PK_Customers" PRIMARY KEY ("CustomerId")
>     );
>     
>     Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERATED"
>        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 "GENERATED"
>         Position: 63
>         File: src\backend\parser\scan.l
>         Line: 1067
>         Routine: scanner_yyerror
>     42601: syntax error at or near "GENERATED"

(由于这是代码优先的方法,下面是模型) 型号:

     public class Customer1
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
    }


**Update: Got a solution:
in migrationbuilder(created after add-migration command),i simply changed** 
NpgsqlValueGenerationStrategy.IdentityByDefaultColumn 
to 
NpgsqlValueGenerationStrategy.serialcolumn
**and saved it ,and then run the command update-database ,and it worked**

0 回复 | 直到 6 年前

1

0

Belayer 6 年前

说真的!我怀疑这是Postgres3.x的任何东西(91年发布)。那可能是你的.net版本。请运行此查询:select version();
表定义使用“默认生成作为标识”。它最初出现在版本10中。如果版本较低,则将该定义更改为:

 create table Customers (
        CustomerId   serial 
        CustomerName text null,
        constraint pk_customers primary key (CustomerId)
    );

注意:我删除了双引号(“),建议您也这样做。他们只是不值得他们所造成的麻烦。

2

0

Ryan 6 年前

你好像在激活 IDENTITY columns 特性,它只在PostgreSQL 10中引入(当您是9.4.20时)

我建议你可以升级到PostgreSQL的新版本。

参考 https://dba.stackexchange.com/questions/198777/how-to-add-a-postgresql-10-identity-column-to-an-existing-table