Can't scaffold database with expression indices

[roji]

From @adopilot on December 5, 2015 19:57

Npqsql sometimes fail to Reverse engineering with exception which is unreadable to user.

I was trying to Scaffold database of Discourse forum.

In VS2015 console app created targeting .NETFramework,Version=v4.5.1 with EntityFramework7.Npgsql.Design.3.1.0-rc1-3 when try to Scaffold database using Package Manager Console by command like this

Scaffold-DbContext -provider EntityFramework7.Npgsql -connection "Port=5432;Server=xx.xx.xx.xx;Database=discourse;UserId=xx;Password=xx;"

user get generated exception like this

PM> Scaffold-DbContext -provider EntityFramework7.Npgsql -connection "Port=5432;Server=172.16.0.17;Database=discourse;UserId=;Password=;"
System.InvalidCastException: Column is null
   at Npgsql.BackendMessages.DataRowMessage.CheckNotNull()
   at Npgsql.NpgsqlDataReader.ReadColumnWithoutCache[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.ReadColumn[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.GetString(Int32 ordinal)
   at Microsoft.Data.Entity.Scaffolding.NpgsqlDatabaseModelFactory.GetColumns()
   at Microsoft.Data.Entity.Scaffolding.NpgsqlDatabaseModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.RelationalScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.NpgsqlScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GetMetadataModel(ReverseEngineeringConfiguration configuration)
   at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GenerateAsync(ReverseEngineeringConfiguration configuration, CancellationToken cancellationToken)
   at Microsoft.Data.Entity.Design.DatabaseOperations.ReverseEngineerAsync(String provider, String connectionString, String outputDir, String dbContextClassName, List`1 schemas, List`1 tables, Boolean useDataAnnotations, CancellationToken cancellationToken)
   at Microsoft.Data.Entity.Design.OperationExecutor.<ReverseEngineerImpl>d__19.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_0`1.<Execute>b__0()
   at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
Column is null

That is prryt unreadibile and hard to debug what is wrong with database

Here is gist dump of scema Discourse database I am trying to scaffold.

Copied from original issue: npgsql/npgsql#892

[roji]

One problem I see is arbitrary-expression indices, opened dotnet/efcore#3986. For now I will make sure Npgsql at least drops these indices and logs a warning.

[roji]

@adopilot, I've fixed the issue with expression indices, they are currently ignored, at least until the EF7 core issue is resolved.

Curiously though, after doing that reverse engineering worked just fine - I didn't get the "Column is null" error you posted above.

I have some other issues so I'll wait before releasing another nuget though.

[roji]

From @adopilot on December 11, 2015 14:18

Any chances to get new nugets soon (please 👍 ).
Is that bug fixed here on GitHUB to try pull source and compile by myself ?

[roji]

@adopilot promise to try to do this later today.

Leaving this issue to track actual scaffolding of expression indices once dotnet/efcore#3986 is implemented.

[roji]

@adopilot have pushed 3.1.0-rc1-4 which should properly ignore expression indices, please let me know if you run into any trouble.

[roji]

From @adopilot on December 16, 2015 11:34

I am sory, But I am unable to Scaffold-DbContext
I am tring it on .NETFramework,Version=v4.5.1
Whenever I try to Scaffold-DbContext
I get

Unable to find design-time provider assembly. Please install the EntityFramework7.Npgsql.Design NuGet package and ensure that the package is referenced by the project.

 Install-Package EntityFramework7.Npgsql.Design
Install-Package : Unable to find package 'EntityFramework7.Npgsql.Design'
At line:1 char:1
+ Install-Package EntityFramework7.Npgsql.Design
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], Exception
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

PM> Install-Package EntityFramework7.Npgsql.Design -pre
Package 'EntityFramework7.Npgsql.Design.3.1.0-rc1-4' already exists in project 'npgToDsicurce'

[roji]

From @eBerdnA on December 24, 2015 14:24

@roji Did you push version 3.1.0-rc1-4 only for "EntityFramework7.Npgsql.Design" or also for "EntityFramework7.Npgsql" because right now I can only get version 3.1.0-rc1-3 for the later through NuGET.

So basically I do have the following dependecies in my project.json:

"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework7.Npgsql": "3.1.0-rc1-3",
"EntityFramework7.Npgsql.Design": "3.1.0-rc1-4"

If I try to scaffold my local database with this setup I do get the same error as @adopilot which he got when he opened this issue. I'm using a DB which I created myself but which is very simple (only 4 tables with some foreign keys, so nothing special).

UPDATE: I have just created a dump of the db which is causing this effect. Maybe this helps to find the reason for this issue.
https://gist.github.com/eBerdnA/e5f790dc205e6dc07818

[roji]

@adopilot, @eBerdnA sorry for not looking at this sooner. #914 has been opened for this, I'll continue the conversation there.

[roji]

From @eBerdnA on December 27, 2015 11:53

@roji Just to make sure I'm not causing confusion here I don't have any problem running the command as it is described in #914 because I simply don't use it but I do get the error "Column is null".

I'm using the command dnx ef dbcontext scaffold "Host=localhost;Username=postgres;Password=test1234;Database=Homepage" EntityFramework7.Npgsql --outputDir Models which is mentioned in the documentation of ASP5 (here).

[roji]

@eBerdnA sorry I thought you had the same issue as @adopilot.

I just created a database with your SQL dump and then successfully reverse-engineered it with no warnings or errors whatsoever...! Your dependencies look OK, can you please double-check things (do dnu restore one more time), and if things are still not working, open a separate issue with a full stack trace please?

[roji]

From @eBerdnA on December 27, 2015 14:18

@roji As a dnu restore did not change anything I opened a separate issue for this. #917

[roji]

From @adopilot on December 28, 2015 9:57

@roji unfortunately I still can't scaffold database
I am trying do it in VS2015 vlass asp.net 4.5.1 project.
When I do Scaffold-DbContext i get Coumn is null
when I look at my packages.config i see that there is

<package id="EntityFramework7.Npgsql" version="3.1.0-rc1-3" targetFramework="net451" />
<package id="EntityFramework7.Npgsql.Design" version="3.1.0-rc1-5" targetFramework="net451" />

Should EntityFramework7.Npgsql also be at verison version="3.1.0-rc1-5"
I can't find way to install it

here is what I get, maybe this is different issue

Scaffold-DbContext -provider EntityFramework7.Npgsql -connection "Port=5432;Server=172.16.0.17;Database=discourse;UserId=xx;Password=xx;"
System.InvalidCastException: Column is null
   at Npgsql.BackendMessages.DataRowMessage.CheckNotNull()
   at Npgsql.NpgsqlDataReader.ReadColumnWithoutCache[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.ReadColumn[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.GetString(Int32 ordinal)
   at Microsoft.Data.Entity.Scaffolding.NpgsqlDatabaseModelFactory.GetColumns()
   at Microsoft.Data.Entity.Scaffolding.NpgsqlDatabaseModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.RelationalScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.NpgsqlScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
   at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GetMetadataModel(ReverseEngineeringConfiguration configuration)
   at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GenerateAsync(ReverseEngineeringConfiguration configuration, CancellationToken cancellationToken)
   at Microsoft.Data.Entity.Design.DatabaseOperations.ReverseEngineerAsync(String provider, String connectionString, String outputDir, String dbContextClassName, List`1 schemas, List`1 tables, Boolean useDataAnnotations, CancellationToken cancellationToken)
   at Microsoft.Data.Entity.Design.OperationExecutor.<ReverseEngineerImpl>d__19.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_0`1.<Execute>b__0()
   at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
Column is null

[roji]

@adopilot thanks for testing, yes this is something new. Can you please open a separate issue and like to a gist of the SQL dump of the database (use pg_dump --schema-only)?

[roji]

From @adopilot on December 28, 2015 12:34

@roji I'll open new issue as soon as I be able to reproduce problem.
Odd thing is that when I did pg_dump --schema-only and restore to new database
Scaffolding of database wen't smoothly without problems, few warnings but looks like i have most of DbContext created well.
I am trying to reproduce this issue on restored database.
As soon I be able to reproduce this I am going to open new issue.

[roji]

@adopilot in that case it seems almost identical to what @eBerdnA encountered in issue #917... weird!

If you manage to reproduce anything please let me know via a new issue!

[roji]

From @eBerdnA on December 28, 2015 21:22

@roji It is getting more weird every day. Today I dropped a foreign key from one table and added a new column. Afterwards I have been unable to scaffold any more.

Then I restored a new instance from a dump as @adopilot described and scaffold was possible again.

But I'm still unable to reproduce it.

[roji]

From @eBerdnA on December 28, 2015 22:26

@roji @adopilot I think I found a way to reproduce this effect. The following steps should lead to the problem.

1 - First of all create two tables.

CREATE TABLE "Article"
(
  "ArticleId" serial NOT NULL,
  "Title" character varying,
  CONSTRAINT "Article_pkey" PRIMARY KEY ("ArticleId")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Article"
  OWNER TO postgres;

CREATE TABLE "Status"
(
  "StatusId" serial NOT NULL,
  "Title" character varying(255),
  CONSTRAINT "Status_pkey" PRIMARY KEY ("StatusId")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Status"
  OWNER TO postgres;

Scaffolding should be possible now.

2 - Add a new column to table Article and a constraint

ALTER TABLE "Article"
   ADD COLUMN "StatusId" integer;

ALTER TABLE "Article"
  ADD CONSTRAINT "fkey_Status" FOREIGN KEY ("StatusId") REFERENCES "Status" ("StatusId")
   ON UPDATE NO ACTION ON DELETE NO ACTION;

Scaffolding should still be possible.

3 - Remove the added constraint and the column from table Article.

Now scaffolding should fail with Column is null.

Hopefully this helps.

UPDATE: Should I just reopen #917? Stacktrace is same as before.

[roji]

@eBerdnA thanks for the repro, I'll try it later today!

The Npgsql reverse engineering provider queries the PostgreSQL internal catalog tables to figure out the database schema, it's possible that there's some subtle bug there that only manifests itself after doing ALTER or something... I'll let you know what I find.

[roji]

@eBerdnA yes please open #917 and include the details in it.

[roji]

From @eBerdnA on December 29, 2015 11:28

@roji Done.

[roji]

Closing this as it was more about a bug caused by having an expression index (the fix was to ignore it).

Opened #119 to track expression index support.