RevEng: InvalidOperationException scaffolding primary/foreign key columns with different precisions #19793
Comments
EF can't deal with C# entity types that have different primary and foreign key types. For example, this cannot be mapped by EF: public class Item
{
public int Id { get; set; }
public ICollection<Tag> Tags { get; set; }
}
public class Tag
{
public int Id { get; set; }
public decimal ItemId { get; set; }
public Item Item { get; set; }
}So it's not the validation that is wrong, but rather EF can't handle the model that is being created. As a workaround, make the CLR types match but map one to a different column type. For example: public class Item
{
public int Id { get; set; }
public ICollection<Tag> Tags { get; set; }
}
public class Tag
{
public int Id { get; set; }
[Column(TypeName = "NUMBER")]
public int ItemId { get; set; }
public Item Item { get; set; }
} |
|
@ajcvickers Thanks for your prompt reply.
I understand that the workaround would be to change the mapping in the model, owing to the limitations of EF in dealing with different primary and foreign key types. But for my particular use case, the issue occurs when I am trying to scaffold an existing database. The exception is thrown during the scaffolding process and hence, the model is never created in the first place. Would there be a way to apply this workaround or any other way to achieve the same effect, before the model is created, i.e., when scaffolding (reverse engineering)? |
|
@rkaushik15 We'll discuss the impact on scaffolding and get back to you. |
|
@rkaushik15 We discussed this and we plan to either:
For now, the only workaround the scaffolding aspect is probably to exclude those tables that are like this and then write the code for those manually. You could also try duplicating the schema and the modifying it just so scaffolding will complete. |
bricelam
changed the title
|
Any updates on this? Running into this after converting a db from mssql to postgresql. bigint -> integer |
|
@kevin-shelaga This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. |
ajcvickers
added
propose-punt
punted-for-7.0
|
Hello. Any updates on this? I'm have this exact same issue (trying to scaffold a database with two tables that has different data types relationship). It's a PostgreSQL database. Thank you. |
|
Any update on this? |
|
Can T4 templates be a possible work around? |
|
What I did to solve MY problem:
|
|
@danarrib Thanks for the response! Not a bad approach at all but I don't have the ability/privileges to create a local database to do this :/ |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
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 asNUMBER(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 typeNUMERIC. 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 typeNUMBER.Example of the error being thrown:
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:
I can see the
AddForeignKey()method calls the staticForeignKey.AreComapatible()method which checks for type compatibility. Following the flow, the exception is thrown when theArePropertyTypesCompatible()method is called which has the following code:Here the
ClrTypeof theprincipalPropertiesanddependentPropertiesis 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:
NUMBERtype maps to the ClrTypeSystem.DecimalandNUMBER(*,0)type maps to the ClrTypeSystem.Int64orSystem.Int32, etc. depending on the Precision.Steps to reproduce
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.
Open a VS 2017 command prompt.
Run the following commands:
Exception Message and Stack Trace
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)
The text was updated successfully, but these errors were encountered: