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

Created on 4 Feb 2020  ·  6Comments  ·  Source: dotnet/efcore


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
  1. 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