Is it possibile to customize the default mappings in Oracle.EntityFrameworkCore for Scaffold-DbContext ?

[fededim]

I read the documentation of Oracle.EntityFrameworkCore, yet it does not contain any information about how to customize the default mappings. Is is possible somehow ? .NET version allowed the customization through app.config, what about .NET CORE ?

[fededim]

Oops sorry I put a wrong link to documentation, the right one is here. This documentation contains an advice should you wish to change manually the default mapping in the file, e.g.

"When scaffolding, developers may wish to change the default data type mappings between Oracle Database and .NET. When modifying .NET CLR types of the generated entity's property, remove the auto-generated HasColumnType() Fluent API in the model."

The problem with this solution is that if I change manually the auto-generated files, all the changes will be overwritten whenever a new Scaffold-DbContext operation is done (for example after an update to the tables structure).

Is it possible somehow to define a custom default mapping to be used by Scaffold-DbContext ?

[alexkeh]

Not at this time. I filed Bug/ER 32633191 to track this request with my Oracle team.

As a workaround, when the underlying table changes, you can modify the mapping manually rather than using Scaffold-DbContext. Alternatively, you can generate a new mapping with Scaffold-DbContext, then apply only the changes to the existing mapping for the DB schema changes added.

Are these capable workarounds for your use case?

[fededim]

Yes, I can manage meanwhile with the second option. What I mainly wanted was to point out this limitation and to get it addressed in future releases.

Thank you for the prompt reply.

[fededim]

A requirement came to my mind which you may take into consideration: besides allowing the mapping of a general Oracle datatype (e.g. NUMBER(3)) to .NET type (e.g. short) the configuration file should also allow the mapping of a Oracle datatype of a particular field of a table to .NET type. This is useful to manage particular exceptions, for example usually NUMBER(3) is mapped to byte (unsigned), but for the field x of table y it should be mapped to sbyte (signed).

[loftusjl]

Just ran into this same issue after our project upgraded from .Net Core 3.1 to 6.0.
Most of our tables rely on type NUMBER(38).

warn: Microsoft.EntityFrameworkCore.Model.Validation[30003]
If the configured precision and scale don't match the column type in the database, this will cause values to be silently truncated if they do not fit in the default precision and scale. Consider using a different property as the key, or make sure that the database column type matches the model configuration and enable decimal rounding warnings using 'SET NUMERIC_ROUNDABORT ON'.

Was a solution ever found?

[GrimmT]

We have the same Problem as @loftusjl
After Migrating from .NET Core 3.1 to 6.0 we get this warning for column of type Number(24,0).
Is there any solution for this?

[alexkeh]

@loftusjl @GrimmT Are you looking for an warning suppression solution or to have a different default mapping during the scaffolding process? The Oracle dev team is working on changing the default data type mappings for scaffolding to provide a more natural alignment between corresponding Oracle DB and .NET data types. We think that would address many of the customer concerns around managing Oracle EF Core scaffolding data type mapping.

[GrimmT]

Hi @alexkeh
Thank you for your fast reply!

I am not sure if this issue here is really the right one for our problem. (I posted it here since @loftusjl seems to have the same problem)
Here I have a minimal example of our Setup:

public class ExampleEntry
{
	[Required]
	public decimal Id { get; set; }

	[Required]
	public string Type { get; set; }
}

public class ExampleDbContext : DbContext
{
	public ExampleDbContext( [NotNull] DbContextOptions<ExampleDbContext> options ) : base( options )
	{
	}

	public DbSet<ExampleEntry> ExampleEntries { get; set; }

	protected override void OnModelCreating( ModelBuilder modelBuilder )
	{
		var entityTypeBuilder = modelBuilder.Entity<ExampleEntry>();
		entityTypeBuilder.HasKey( c => new { c.Id, c.Type } );
		entityTypeBuilder.Property( c => c.Id ).HasColumnType( "Number(24,0)" );
	}
}

When we run our asp.net core web server on .NET Core 3.1 and Oracle.EntityFrameworkCore Version=3.19.80 there are no warnings in our loggs.
But after migrating to .NET 6 and Oracle.EntityFrameworkCore Version=6.21.61 we see the following warning inside the loggs at startup time:

warn: Microsoft.EntityFrameworkCore.Model.Validation[30003]
      The decimal property 'Id' is part of a key on entity type 'ExampleEntry'. If the configured precision and scale don't match the column type in the database, this will cause values to be silently truncated if they do not fit in the default precision and scale. Consider using a different property as the key, or make sure that the database column type matches the model configuration and enable decimal rounding warnings using 'SET NUMERIC_ROUNDABORT ON'.

I think the corresponding ef core breaking change is the following one: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/breaking-changes#decimals
In the Mitigations section, they suggest specifying a type with precision and scale (which we allready have Number(24,0)) and as an alternative use the Fluent Api .HasPrecision(24,0).
But none of these Mitigations resolve this warning.
So we have basically the following questions:

1. We can make sure on our application side that the decimal number will always have a precision of 24 and a scale of 0. Is this warning still a problem?
2. If this is a problem, is there any solution for this?
3. If this is not a problem, is there any suggested way to suppress this warning?

[loftusjl]

We have the same Problem as @loftusjl After Migrating from .NET Core 3.1 to 6.0 we get this warning for column of type Number(24,0). Is there any solution for this?

@GrimmT We ended up having to change the precision of the suspect columns based on the Data Type Mapping table found in the Scaffolding Or Reverse Engineering section of the Oracle documentation.
By reducing the precision based on the field, we were able to have everything scaffold they way we needed.

Similarly, we ran into an issue a bit later where the database had been developed with NUMBER(1) which was defaulting to boolean when we required integers. We had to increase the precision to 5 within the database for them to scaffold correctly.

@loftusjl @GrimmT Are you looking for an warning suppression solution or to have a different default mapping during the scaffolding process? The Oracle dev team is working on changing the default data type mappings for scaffolding to provide a more natural alignment between corresponding Oracle DB and .NET data types. We think that would address many of the customer concerns around managing Oracle EF Core scaffolding data type mapping.

@alexkeh My team was looking for different mapping in the scaffolding process. Looking forward to the scaffolding changes.

[alexkeh]

@GrimmT If you are verifying the data sizes fit within the target data types you are saving to already, this warning message shouldn't be a problem. I don't enough about how this validation works for why you are still seeing a warning.

.NET provides a few ways you can suppress specific warnings.
https://docs.microsoft.com/en-us/dotnet/fundamentals/code-analysis/suppress-warnings

[GrimmT]

Hi @alexkeh @loftusjl

We verified, that the smallest and biggest data points we can have in our system, are correctly be read and written to and from the database.
Therefore, we will now be suppressing this warning. (Since the warning happens at runtime at the startup of our system, we will suppress this warning only in Production through our appsettings.Production.json)
Thank you both for the information and fast feedback on this topic.

[Soundman32]

Just to add another scenario. The Oracle system I'm currently working on, has primary keys of NUMBER(10) and foreign keys of NUMBER(19). This is currently worked around because it's .NET framework (4.5.1 !) and using the in app.config. At the moment, I'm unable to get the DBA to agree to synchronise the types.

Trying to use Scaffold-DbContext generates the error.

[jcracknell]

I'm sorry but these default type mappings are incredibly awful for the simple reason that if I'm scaffolding a DbContext from an existing database, I expect to be able to read the existing data.

There's an argument to be made for mapping all NUMBER columns with s != 0 to System.Decimal on the basis that it's unlikely the column will actually contain a number which will not fit in a System.Decimal; however to map NUMBER(1) to System.Boolean and any NUMBER value to System.Byte (an unsigned value) is just wrong.

The insertion-safety rationale isn't even consistently applied! NUMBER(4) maps to System.Byte so I can't exceed the column size, but NUMBER(11) (correctly) maps to System.Int64 which has a maximum value of 9,223,372,036,854,775,807? If I try and insert that, I rightly earn the ensuing DoingItWrongException and can fix my code or the column.