sql: support .NET EntityFramework Core and Scaffolding tool

[lemonway]

These are the known issues needed to fully support all EntityFramework Core features

• Database collection (Cannot get collation of a database from pg_database #48726)
• pg_catalog.pg_opclass not implemented (pg_catalog.pg_opclass not implemented #48729)
• pg_catalog.pg_authid not implemented (pg_catalog.pg_authid not implemented #48738)
• add attidentity and attgenerated columns to pg_attribute (sql: add attidentity and attgenerated columns to pg_attribute #48541)
• "char" datatype is not returned correctly in constant expressions (sql/pgwire: "char" datatype is not used correctly if selecting both a constant and from a table #48563)
• "char" should have a typlen of 1 (sql: "char" type should have a typlen of 1 #48565)
• support GENERATED/IDENTITY syntax (sql: support GENERATED ... AS IDENTITY syntax #48532)

---

Original Issue Description

Describe the problem

The EntityFramework has a Scaffolding tool which allow to generate C# (Plain-Old Class Object) from database in the Database first approach.

It works well for a Postgres Database, but not for a CockroachDB.

In this example, I created a "tg" database in Postgres and a "tg" database in CockroachDB using one same SQL Script.

• Then I were able to generate the C# codes from the Postgres database but not from the CockroachDB (see the below error)
• Fortunately the C# codes generated from the Postgres database is useable on the CockroachDB.

To Reproduce

Requirement:

• dotnet core SDK 3.1
• EFCore Scaffolding tool: dotnet tool install --global dotnet-ef
• A cockroach database called "tg" accessible from "Server=127.0.0.1;Port=26257;Database=tg;User Id=root;"

Step to reproduce error:

dotnet tool install --global dotnet-ef

REM create a working directory
mkdir myproject
cd myproject

REM create model project
mkdir model
cd model
dotnet new classlib
mkdir tg

cd ..

REM create generator project
mkdir pgenerator
cd pgenerator
dotnet new console
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add reference ..\model\model.csproj

cd ..

REM generate the model <- it crash on CockroachDB
dotnet ef dbcontext scaffold "Server=127.0.0.1;Port=26257;Database=tg;User Id=root;" Npgsql.EntityFrameworkCore.PostgreSQL -o tg -c TgDataContext -s pgenerator -p model -f

Expected behavior
C# POCO class shoud be generated in the model/tg folder

Error

Build started...
Build succeeded.
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Char'.
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.DbDataReaderExtension.GetValueOrDefault[T](DbDataRecord record, String name)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetColumns(NpgsqlConnection connection, IReadOnlyList`1 tables, String tableFilter, HashSet`1 enums, IDiagnosticsLogger`1 logger)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetTables(NpgsqlConnection connection, Func`3 tableFilter, HashSet`1 enums, IDiagnosticsLogger`1 logger)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(DbConnection dbConnection, DatabaseModelFactoryOptions options)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to cast object of type 'System.String' to type 'System.Char'.

Environment:

• CockroachDB version 19.2.6
• Server OS: Docker on Windows with Linux Container
• Client app C# Entity framework / Scaffolding tool

Additional context

Cannot use database first approach for CockroachDB

Jira issue: CRDB-4362

Epic DXTPT-33

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @ricardocrdb (member of the technical support engineering team)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for the issue! I am still building up my knowledge of EF Core, so sorry for the delay.

@roji do you have any insight into what could be going wrong here? If you would be able to take a look and help us investigate, please let us know if something could be wrong/missing from the CockroachDB side.

[roji]

The Npgsql EF Core provider sends some queries against PostgreSQL catalog tables to inspect existing tables etc. - I'm guessing CockroachDB has some subtle incompatibility with PostgreSQL here.

The query for getting the columns is here. The stack trace seems to indicate that the problem is with a char coming back from the database, and the two char fields in the query are attidentity and attgenerated. I'd check if the type for these two fields is the same in CockroachDB as it is in PG.

[rafiss]

@roji Thank you! That's very helpful.

The pg_attribute catalog table in CockroachDB currently does not contain attidentity or attgenerated columns. It seems like these were added in Postgres versions 10 and 12, whereas our pg_catalog is mostly compatible with Postgres 9.5.

However, we have a project underway to make our catalog tables compatible with newer versions of Postgres, although the details of which versions/columns we are going to be adding soon are still being determined.

@lemonway I've made this issue to track adding these columns #48541 In the meantime, it's possible that Npgsql could account for these missing columns as part of this issue in that project: npgsql/efcore.pg#1360

[roji]

@rafiss yeah, that makes sense. These are already conditional on on the version.

However, I'm a bit curious how the query managed to run at all - at least in PostgreSQL if you try to select a column that doesn't exist (e.g. attidentity) the query will fail, whereas the above error message is about trying to access a string (text) as a char... Maybe something else is going on.

[roji]

As a general rule, would it make sense to say that UseCockroachDb (npgsql/efcore.pg#1360) should set PostgreSQL compatibility mode to 9.5? That would propagate to the relevant places automatically, instead of adding CockroachDB-specific checks.

[rafiss]

However, I'm a bit curious how the query managed to run at all - at least in PostgreSQL if you try to select a column that doesn't exist (e.g. attidentity) the query will fail, whereas the above error message is about trying to access a string (text) as a char... Maybe something else is going on.

That's a good point. I ran the query against CockroachDB myself, and noticed that there is one more char in the query: pg_type.typtype.

So it must be that column that is causing the issue. In retrospect this should have been more obvious to me -- the issue here must be that in CockroachDB, CHAR is an alias for STRING.

As a general rule, would it make sense to say that UseCockroachDb (npgsql/efcore.pg#1360) should set PostgreSQL compatibility mode to 9.5? That would propagate to the relevant places automatically, instead of adding CockroachDB-specific checks.

I think that would be a good starting point. There may be additionally functionality that CockroachDB does support, but if needed, perhaps later on we can add a check for CockroachDB specifically where it would be relevant.

[roji]

in CockroachDB, CHAR is an alias for STRING.

OK, that all makes sense now :) Yeah, PostgreSQL "char" is a weird internal type. Is this something you could implement on your side?

[rafiss]

Ah actually, sorry it doesn't seem like typtype is the issue specifically. In the GetColumns function in that class, it does not look like there is anything in the code that is accessing the typtype column. If I understand correctly, the stacktrace indicates that the error happens when calling GetValueOrDefault on the result set from this GetColumns query. It looks like the only calls are record.GetValueOrDefault<char>("attgenerated") and record.GetValueOrDefault<char>("attidentity").

So the issue is coming from the constant cast expressions in the query: ''::"char" as attidentity and ''::"char" as attgenerated.

And indeed, comparing the results of SELECT ''::"char" as c in PostgreSQL vs CockroachDB shows the inconsistency. PostgreSQL returns a value with a type OID of 18 (the "char" type), whereas CockroachDB uses type OID 25 (the text type). I made an issue to track that here: #48563 I'm not sure yet when we can address it.

[roji]

@rafiss Oh yeah, you're right - I'm assuming @lemonway user configured the provider for an old PostgreSQL (to avoid exactly erroring on attidentity not existing in the database), but my query still generates a fake empty column with the same name and type.

typtype doesn't actually get read by Npgsql so it shouldn't be a problem. I'll simply cast attidentity/attgenerated to text in my query to avoid reading "char" out of the database.

[roji]

One question before I look at this change... these catalog tables have some other somewhat exotic types. For example, pg_constraint uses int2[] to represent the list of referenced columns in a foreign key - does CockroachDB handle that? If not, it may be better to just say that CockroachDB doesn't support database scaffolding specifically - this doesn't affect the runtime capabilities of EF Core, only the ability to scaffold a model from an existing database.

[rafiss]

CockroachDB does handle the int2[] foreign key columns in pg_constraint. I don't know of any other issues that are not expected to work, but if you are able to try it out, definitely let me know if you find anything else! And I'd agree, if there are too many incompatibilities, we should address them on the CockroachDB side instead of adding many workarounds.

[roji]

OK, I'll make the change (npgsql/efcore.pg#1362). Hopefully @lemonway will be able to help us verify there aren't any other issues. We can keep this issue to track any other problems until we see it working.

[roji]

FYI the change has been made on my end for 3.1.4, hopefully that's the only thing blocking scaffolding.

@lemonway (or others), can you please add the Npgsql stable feed and try out version 3.1.4-ci.20200509T114251 of the provider? It would be great to know if everything works now.

[duongphuhiep]

Hello, I'm @lemonway user.. (I've accidentally use the account of my company)

FYI

@rafiss: "I'm assuming @lemonway user configured the provider for an old PostgreSQL" =>

No, as you can see all the steps in the description, there isn't any step which change any configuration in particular.. You can just copy paste these same command lines step by step to create an empty project and got this error.

@roji : it is true that the generator actually uses the latest stable version of Npgsql.EntityFrameworkCore.PostgreSQL which is currently 3.1.3

Where can I find the 3.1.4 dll? (on Nuget I saw only 3.1.3 and 5.0.0-preview..)

It will be my pleasure to try it out.

[roji]

@duongphuhiep 3.1.4 isn't released yet. However, our CI produces nuget packages for every commit we do, which allows you to easily test before 3.1.4 comes out. Take a look at the details in the above comment (#48050 (comment)) and let me know if anything is unclear.

[duongphuhiep]

@roji with Npgsql.EntityFrameworkCore.PostgreSQL 3.1.4-ci.20200509T114251

the error message changed

>> dotnet ef dbcontext scaffold "Server=127.0.0.1;Port=26257;Database=p2p;User Id=root;Password=root" Npgsql.EntityFrameworkCore.PostgreSQL -o p2p -c P2PDataContext -s pgenerator -p model -f

Build started...
Build succeeded.
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetConstraints(NpgsqlConnection connection, IReadOnlyList`1 tables, String tableFilter, List`1& constraintIndexes, IDiagnosticsLogger`1 logger)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetTables(NpgsqlConnection connection, Func`3 tableFilter, HashSet`1 enums, IDiagnosticsLogger`1 logger)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(DbConnection dbConnection, DatabaseModelFactoryOptions options)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')

[roji]

@rafiss the relevant code is here, can you take a quick look?

If nothing comes to mind, how easy is it for me to get access to an instance (or quickly set one up locally)?

[rafiss]

Thanks @roji. I ran the query used by GetConstraints, and got back the results I would expect, I think. Is there a way to see which line number produced the error?

Very excited if you'd like to try locally -- It is very easy to get started:

1. Download the binary for your platform, or get the docker image, as described here.
2. Run the binary: ./cockroach start-single-node --insecure (or start the docker container)
3. Connect with the string: postgresql://root@localhost:26257?sslmode=disable
4. You can also get a SQL shell by running ./cockroach sql --insecure

[roji]

I'll try to give it a go in the next few days.

[roji]

List of issues specifically for scaffolding (@rafiss we can track here or elsewhere):

• Database collection (Cannot get collation of a database from pg_database #48726)
• pg_catalog.pg_opclass not implemented (pg_catalog.pg_opclass not implemented #48729)
• pg_catalog.pg_authid not implemented (pg_catalog.pg_authid not implemented #48738)

[roji]

@rafiss have opened some issues currently blocking scaffolding. The above can be worked around in the driver, i.e. by disabling the specific features (i.e. catching exceptions and moving on). I've opened issues to let you know of the gaps though.

[rafiss]

Thank you @roji! That is very helpful. Working around in the driver seems like the ideal step for now.

We will get to these issues as lower-priority items over the next few months. We can use this issue to track progress (I will rename the title, and move your checklist into the main issue description).

[jordanlewis]

Noting that all of the checkboxes in this issue are now solved. Do we need to re-test with a newer build?

[sghofrani]

I want to use Cockroach with c# and Scaffolding, but this error occurs:

Npgsql.PostgresException (0x80004005): 42883: unknown function: pg_indexam_has_property(): function undefined at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)

anyone knows about it?