Only 1 identy column allowed (error), but devart allows creation of migration
Posted: Mon 29 Jul 2019 09:15
Hello dear DevArt team,
In our project we facing the follwoing problem when using devart [dotConnect for Oracle Professional]
We use a model first approache and have to create migrations for Oracle and MSSQL, databases.
When creating migrations for both database types we get no warnings or error messages, for our migration.
The current migration works for MSSQL database.
Oracle fails when executing the migration, with the attached error message 1 output.
Changing the mentioned datatype from DATETIME to INT (for testing purpose) results in attached error message 2
It's very unfortunatly that we don't get a warning/error during creation of the migration.
From looking at the created tables it seems only this one tabe fails, but a second execution (of update-database option in ef core ) is not possible, cause of error messages from already existing objects /tables.
Can we get this tabel with 2 identity columns to work on oracle ?
We appriciate the support for this issue.
Best Regards
Peter Handaric
##############################################
Dev Art Version: 9.7.805 (problem existing before with previous versions)
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
.Net Core version 2.2.104
ef core version 2.2.2-servicing-10034
##################################
##########################
Attachment 1
##########################
PM> update-database
2019-07-29 09:48:27.1628|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)
2019-07-29 09:48:28.9959|Command|ERROR|Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)|ORA-00904: "DATETIME": ungültige ID
(RelationalCommand.ExecuteNonQuery => RelationalCommand.Execute => RelationalLoggerExtensions.CommandError)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-00904: "DATETIME": ungültige ID
######################################################################
#####################
Attachment 2
#####################
PM> update-database
2019-07-29 10:00:46.5059|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
System.InvalidOperationException: The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".
at . (CreateTableOperation )
at . (CreateTableOperation )
at . (MigrationOperation )
at .(IEnumerable`1 )
at .(IEnumerable`1 , )
at Devart.Data.Oracle.Entity.Migrations.OracleEntityMigrationSqlGenerator.Generate(IReadOnlyList`1 migrationOperations, IModel model)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
at .GenerateUpSql(Migration )
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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".
#####################################################################
In our project we facing the follwoing problem when using devart [dotConnect for Oracle Professional]
We use a model first approache and have to create migrations for Oracle and MSSQL, databases.
When creating migrations for both database types we get no warnings or error messages, for our migration.
The current migration works for MSSQL database.
Oracle fails when executing the migration, with the attached error message 1 output.
Changing the mentioned datatype from DATETIME to INT (for testing purpose) results in attached error message 2
It's very unfortunatly that we don't get a warning/error during creation of the migration.
From looking at the created tables it seems only this one tabe fails, but a second execution (of update-database option in ef core ) is not possible, cause of error messages from already existing objects /tables.
Can we get this tabel with 2 identity columns to work on oracle ?
We appriciate the support for this issue.
Best Regards
Peter Handaric
##############################################
Dev Art Version: 9.7.805 (problem existing before with previous versions)
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
.Net Core version 2.2.104
ef core version 2.2.2-servicing-10034
##################################
##########################
Attachment 1
##########################
PM> update-database
2019-07-29 09:48:27.1628|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)
2019-07-29 09:48:28.9959|Command|ERROR|Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)|ORA-00904: "DATETIME": ungültige ID
(RelationalCommand.ExecuteNonQuery => RelationalCommand.Execute => RelationalLoggerExtensions.CommandError)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-00904: "DATETIME": ungültige ID
######################################################################
#####################
Attachment 2
#####################
PM> update-database
2019-07-29 10:00:46.5059|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
System.InvalidOperationException: The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".
at . (CreateTableOperation )
at . (CreateTableOperation )
at . (MigrationOperation )
at .(IEnumerable`1 )
at .(IEnumerable`1 , )
at Devart.Data.Oracle.Entity.Migrations.OracleEntityMigrationSqlGenerator.Generate(IReadOnlyList`1 migrationOperations, IModel model)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
at .GenerateUpSql(Migration )
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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".
#####################################################################