RevEng: InvalidOperationException scaffolding primary/foreign key columns with different precisions

Start with efcore/src/EFCore/Metadata/Internal/EntityType.cs and trace AddForeignKey into ForeignKey.AreCompatible and ArePropertyTypesCompatible. Reproduce the issue with the Oracle schema and the documented dotnet ef dbcontext scaffold command, then inspect the scaffolding path through RelationalScaffoldingModelFactory. Done means the compatible Oracle foreign-key relationship scaffolds without the InvalidOperationException while existing strict type checks remain covered.

I am working with an application using .NET Core 2.2 and Oracle.EntityFrameworkCore 2.19.60 provider for Oracle DB and running into this issue.

When trying to scaffold an Oracle database (using Oracle's EntityFrameworkCore Provider) which contains a Foreign Key such as NUMBER(12,0) (Scale is Zero) referencing a Principal Key such as NUMBER (No Precision or Scale specified), an error is thrown.

SQL Server does NOT allow creating a database containing a Foreign Key of type NUMERIC(*, 0) that references a Principal Key of type NUMERIC. Hence the behavior of the MS EFCORE seems to be in sync with SQL Server's behavior.
But in Oracle DB, it is possible to have a database containing a Foreign Key of type NUMBER(*, 0) referencing a Principal Key of type NUMBER.

Example of the error being thrown:

The types of the properties specified for the foreign key {'ScaffIssueTestFk'} on entity type 'ScaffIssueTest' do not match the types of the properties in the principal key {'ScaffIssueTestPk'} on entity type 'ScaffIssueTest'.

Oracle DB allows such an FK, PK relationship as these types are considered compatible, and hence scaffolding a database with such a relationship should not throw any errors.

On analyzing the issue a bit I think the root cause of the issue lies here:

efcore/src/EFCore/Metadata/Internal/EntityType.cs

I can see the AddForeignKey() method calls the static ForeignKey.AreComapatible() method which checks for type compatibility. Following the flow, the exception is thrown when the ArePropertyTypesCompatible() method is called which has the following code:

private static bool ArePropertyTypesCompatible(
            IReadOnlyList<IProperty> principalProperties, IReadOnlyList<IProperty> dependentProperties)
            => principalProperties.Select(p => p.ClrType.UnwrapNullableType()).SequenceEqual(
                dependentProperties.Select(p => p.ClrType.UnwrapNullableType()));

Here the ClrType of the principalProperties and dependentProperties is different in the case of the Oracle DB Provider, but nevertheless compatible.
But since in the case of SQL Server these must be STRICTLY the same, the code is not causing any issue.

NOTE: NUMBER type maps to the ClrType System.Decimal and NUMBER(*,0) type maps to the ClrType System.Int64 or System.Int32, etc. depending on the Precision.

Steps to reproduce

1. Connect to an Oracle DB instance and run the SQL statements present in the attached file create_user.txt. Switch to the new user created and run the SQL statement in the attached file create_schema.txt.

2. Open a VS 2017 command prompt.

3. Run the following commands:

 dotnet new globaljson --sdk-version 2.2.402
 dotnet new webapi -n api
 cd api
 dotnet add package Oracle.EntityFrameworkCore -v 2.19.60

4. Modify the following command with your DB schema info and run:

dotnet ef dbcontext scaffold "Data
Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNEC
T_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)));User Id=test_user;Password=pass;" Oracle.EntityFrameworkCore -o Models

Exception Message and Stack Trace

>dotnet ef dbcontext scaffold "Data Source=<data_source>;User Id=test_user;Password=pass;" Oracle.EntityFrameworkCore -o Models
System.InvalidOperationException: The types of the properties specified for the foreign key {'ScaffIssueTestFk'} on entity type 'ScaffIssueTest' do not match the types of the properties in the principal key {'ScaffIssueTestPk'} on entity type 'ScaffIssueTest'.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ForeignKey.AreCompatible(IReadOnlyList`1 principalProperties, IReadOnlyList`1 dependentProperties, EntityType principalEntityType, EntityType dependentEntityType, Boolean shouldThrow)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ForeignKey.AreCompatible(EntityType principalEntityType, EntityType dependentEntityType, MemberInfo navigationToPrincipal, MemberInfo navigationToDependent, IReadOnlyList`1 dependentProperties, IReadOnlyList`1 principalProperties, Nullable`1 unique, Nullable`1 required, Boolean shouldThrow)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityType.AddForeignKey(IReadOnlyList`1 properties, Key principalKey, EntityType principalEntityType, Nullable`1 configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityType.Microsoft.EntityFrameworkCore.Metadata.IMutableEntityType.AddForeignKey(IReadOnlyList`1 properties, IMutableKey principalKey, IMutableEntityType principalEntityType)
   at Microsoft.EntityFrameworkCore.MutableEntityTypeExtensions.GetOrAddForeignKey(IMutableEntityType entityType, IReadOnlyList`1 properties, IMutableKey principalKey, IMutableEntityType principalEntityType)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitForeignKey(ModelBuilder modelBuilder, DatabaseForeignKey foreignKey)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitForeignKeys(ModelBuilder modelBuilder, IList`1 foreignKeys)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String namespace, String language, String contextDir, String contextName, 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_1.<.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)
The types of the properties specified for the foreign key {'ScaffIssueTestFk'} on entity type 'ScaffIssueTest' do not match the types of the properties in the principal key {'ScaffIssueTestPk'} on entity type 'ScaffIssueTest'.

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 2.2.4
Database provider: Oracle.EntityFrameworkCore 2.19.60
Target framework: .NET Core 2.2 (netcoreapp2.2)