search

npgsql / EntityFramework6.Npgsql

Entity Framework 6 provider for PostgreSQL
PostgreSQL License
66 stars 53 forks source link

Error on generating the Model using Visual Studio Design Time Support DDEX Provider #20

Open roji opened 8 years ago

roji commented 8 years ago

From @snehitvelma on June 25, 2015 6:32

Unable to generate the model because of the following exception: '

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:09.5977853.
Generating the model took 00:00:06.7410389.
Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:05.7852829.
Generating the model took 00:02:07.6435015.
Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:06.0284166.
Generating the model took 00:00:05.5605231.

Copied from original issue: npgsql/npgsql#651

roji commented 8 years ago

From @kenjiuno on June 25, 2015 12:29

Hi @snehitvelma Thanks for reporting it! I'll check about this problem.

> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist

What version of PostgreSQL server do you connect to?

roji commented 8 years ago

From @snehitvelma on June 25, 2015 13:24

I'm connecting to Amazon Red Shift.

roji commented 8 years ago

From @kenjiuno on June 26, 2015 4:35

Ok, I'll check the way how to fix this issue. This may take some while...

roji commented 8 years ago

From @kenjiuno on December 4, 2015 0:18

Note to self:

conkey and confkey in pg_constraint are int2[].

They need to be expanded from array to rows by using information_schema._pg_expandarray, unnest or such. However redshift doesn't implement it.

https://github.com/npgsql/npgsql/blob/58ecffc78c10ba0e458b2059e0cdefb23710381c/src/EntityFramework6.Npgsql/NpgsqlSchema.ssdl#L112-L114

An idea is to fully rewrite all SQL queries in ssdl using only information_schema.

roji commented 8 years ago

From @kenjiuno on December 10, 2015 11:56

I have tried using information_schema but it doesn't help... My new NpgsqlSchema.ssdl

It will need another tool in order to scaffold Entity Data Model from Redshift db.

Official schema views are: PG_TABLE_DEF and SVV_TABLE_INFO. Redshift db doesn't rely on pk/fk constraints. Define Primary Key and Foreign Key Constraints

So mark [Key] to the possible primary key column property. Just trick the EF!

Some important schemata cannot be queried: select * from information_schema.key_column_usage

An error occurred when executing the SQL command:
select * from information_schema.key_column_usage

[Amazon](500310) Invalid operation: must be superuser to examine "max_index_keys";

Execution time: 0.12s
1 statement failed.

So, EF's scaffolding cannot be done against Redshift...

Unable to Access information_schema.constraint_column_usage - SuperUser? https://forums.aws.amazon.com/thread.jspa?threadID=133514

We can define primary keys and foreign keys on Redshift db. However there is no good way to query them via information_schema.