Scaffolding Or Reverse Engineering

ODP.NET EF Core supports scaffolding the following tables and views:

Relational tables and views
Materialized views

By convention, ODP.NET EF Core maps an appropriate .NET data type based on the Oracle Database data type and its characteristics.

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. For example, an Oracle NUMBER(4) is scaffolded to a .NET Byte data type by default. If the .NET CLR data type is changed to .NET Short or Int16 data type, then an invalid cast exception at runtime may occur until the HasColumnType("NUMBER(4)") has been removed.

Data Type Mapping

This table shows the default mappings.

Table 5-2 ODP.NET Entity Framework Core Reverse Engineering Data Type Default Mappings

Oracle Database Data Type	.NET Type Alias	.NET Data Type
`NUMBER(1)`	`bool`	`System.Boolean`
`NUMBER(2)` to `NUMBER(4)`	`byte`	`System.Byte`
`NUMBER(5)`	`short/int16`	`System.Int16`
`NUMBER(6)` to `NUMBER(10)`	`int/int32`	`System.Int32`
`NUMBER(11)` to `NUMBER(19)`	`long/int64`	`System.Int64`
`NUMBER(>19)`	`decimal`	`System.Decimal`
`NUMBER(p,s)`	`decimal`	`System.Decimal`
`NUMBER`	`decimal`	`System.Decimal`
`BINARY_FLOAT`	`float`	`System.Float`
`BINARY_DOUBLE`	`double`	`System.Double`
`BOOLEAN`	`bool`	`System.Boolean`
`TIMESTAMP`	`DateTime`	`System.DateTime`
`TIMESTAMP WITH TIMEZONE`	`DateTimeOffset`	`System.DateTimeOffset`
`TIMESTAMP WITH LOCAL TIMEZONE`	`DateTime`	`System.DateTime`
`DATE`	`DateTime`	`System.DateTime`
`INTERVAL DAY TO SECOND`	`TimeSpan`	`System.TimeSpan`
`INTERVAL YEAR TO MONTH`	`string`	`System.String`
`VARCHAR2`	`string`	`System.String`
`JSON`	`string`	`System.String`
`NVARCHAR2`	`string`	`System.String`
`CHAR`	`string`	`System.String`
`NCHAR`	`string`	`System.String`
`CLOB`	`string`	`System.String`
`NCLOB`	`string`	`System.String`
`RAW`	`byte[]`	`System.Byte[]`
`BLOB`	`byte[]`	`System.Byte[]`
`XMLTYPE`	`string`	`System.String`
`ROWID`	`string`	`System.String`
`UROWID`	`string`	`System.String`
`LONG`	`string`	`System.String`
`BFILE`	`byte[]`	`System.Byte[]`
`LONG RAW`	`byte[]`	`System.Byte[]`
`VECTOR`	`string`	`System.String`

Scaffolding Tables from Another Schema

Developers can scaffold tables from other schemas other than the user/schema they are connected with. The connected user requires privileges to access the other schema's objects. Once these privileges are granted, developers can use the Package Manager Console (PMC) tools for Entity Framework Core to perform the scaffolding operation. For example:

Scaffold-DbContext "User Id=scott;Password=<password>;Data Source=myhost:1521/mydb;" 
Oracle.EntityFrameworkCore -Schemas HR -Tables EMPLOYEES

Developers can use the –Schemas and –Tables parameters to specify which schemas and tables/views to scaffold for an Entity Framework Core model. If connecting with one user/schema to create tables from a second schema, the user must have at least SELECT privileges for that second schema.

The following table displays the ODP.NET Entity Framework Core behavior when the –Schemas and/or –Tables parameter is specified or left as default while scaffolding a pre-existing model using the Package Manager Console command, Scaffold-DbContext. All sample command excerpts below use Scaffold-DbContext syntax. Similar functionality is available using the EF Core tools command, dotnet ef dbcontext scaffold.

Table 5-3 Schema and Table Filter Mapping

Mapping of Schema and Table Filters	No Schema Filter	Schema Filter
No Table Filter	Generates all tables/views within current user/schema Box 1	Generates all tables/views in specified user(s)/schema(s) Box 2
Table Filter	Generates specified tables/views within current user/schema Box 3	Generates specified tables/views within current schema and all tables/views in specified user(s)/schema(s) Filtered results include combined Box 2 and Box 3 results.

Mapping of Schema and Table Filters

No Schema Filter

Schema Filter

No Table Filter

Generates all tables/views within current user/schema

Box 1

Generates all tables/views in specified user(s)/schema(s)

Box 2

Table Filter

Generates specified tables/views within current user/schema

Box 3

Generates specified tables/views within current schema and all tables/views in specified user(s)/schema(s)

Filtered results include combined Box 2 and Box 3 results.

It is possible to scaffold tables/views in other schemas by appending the schema name in front: -Tables <schema>.<table/view>

For example, the following snippet would scaffold three tables in three different schemas:

-Tables SCHEMA1.TABLEA, SCHEMA2.TABLEB, SCHEMA3.TABLEC

If the schema or table name contains any special characters, such as a period, then use brackets to delimit the schema and table names when using the –Tables option.

-Tables [SCHEMA1].[TABLEA]

Scaffolding Views

Oracle supports scaffolding database relational and materialized views only. These views can be either read-only or updatable. Use the -Tables parameter to specify the views to scaffold. If the parameter is left blank (default), all the user tables, relational views, and materialized views will be scaffolded.

Customizing Data Type Mapping

If default data type mappings are not the best fit for the .NET application, the scaffolded data type mappings can be changed. For example, the NUMBER(1) maps to a .NET bool by default. If the column data contains numbers other than Boolean values, a better mapping would be to a .NET byte.

To customize mappings, Oracle EF Core apps should use reverse engineering templates, or T4 text templates. These templates provide an alternative mapping Oracle EF Core can use during scaffolding. A common set of alternative data type mappings are in the table below. They are a more complementary match between Oracle database types and .NET types for scaffolding.

Table 5-4 ODP.NET Entity Framework Core Scaffolding Default and Alternative Data Type Mappings

Oracle Database Data Type	Default .NET Type Alias	More Complementary .NET Type Alias
`NUMBER(1)`	`bool`	`byte`
`NUMBER(2)` to `NUMBER(4)`	`byte`	`Int16`
`NUMBER(5)`	`Int16`	`Int32`
`NUMBER(6)` to `NUMBER(10)`	`Int32`	`Int64`
`NUMBER(10)` to `NUMBER(19)`	`Int64`	`Decimal`

One advantage of the more complementary .NET data types is they can store all possible values of the mapped Oracle Database data types.

Using T4 Templates for Data Type Mapping Customization

Standard .NET T4 templates provide the data type mapping customization information for EF Core scaffolding. To use the template, follow these steps.

Install the EF Core templates package. For example, you can incorporate the package using the .NET CLI command:
```
dotnet new install Microsoft.EntityFrameworkCore.Templates
```
Add the Oracle EF Core default templates to your project. This too can be done with the .NET CLI as seen here:
```
dotnet new ef-templates
```
You should now see a new project directory Code-Templates/EFCore with two files, DbContext.t4 and EntityType.t4. The DbContext.t4 template is used to scaffold a DbContext class for the database. The EntityType.t4 template is used to scaffold entity type classes for each table and view in the database. We will use the EntityType.t4 template in the next step.

Using the EntityType.t4 template, any CLR Type can be mapped to a specific Oracle database type. The template snippet maps the NUMBER(1) column Col1 to a .NET short data type instead of .NET bool default type:

        //Specify the store type custom mapping to your chosen the CLR type
        var clrType = (property.Name == "Col1" && property.GetColumnType() == "NUMBER(1)")
            ? typeof(short)
            : property.ClrType;
 
        //Add any required ‘usings’ for the clrType
        usings.AddRange(code.GetRequiredUsings(clrType));
 
        var needsNullable = Options.UseNullableReferenceTypes && property.IsNullable && !clrType.IsValueType;
        var needsInitializer = Options.UseNullableReferenceTypes && !property.IsNullable && !clrType.IsValueType;
#>
    public <#= code.Reference(clrType) #><#= needsNullable ? "?" : "" #> <#= property.Name #> { get; set; }<#= needsInitializer ? " = null!;" : "" #>
<#
        firstProperty = false;

The newly added template overrides Oracle EF Core's default type mapping.

Oracle provides sample T4 text templates developers can use as is or customize on the Oracle .NET GitHub sample code site. Snippets can be copied directly into an application's T4 text template with a minimum of customization.

The property name in the template is what scaffolding generates as per the standard property naming scheme. In the above example, the database column, COLl1, becomes the Col1 .NET property name. In the below example, the database column C01_SMALLINT becomes the property name C01SmallInt.

var clrType = (property.Name == "C01SmallInt" && property.GetColumnType() == "NUMBER(38)")
            ? typeof(short)
            : property.ClrType;

An easy way to determine the .NET type name is to perform scaffolding without a template.

If altering some property aspects, such as the CLR Type, ensure the appropriate altered variable is used when printing the property as seen in the example clrType reference, not property.clrType.

public <#= code.Reference(clrType) #><#= needsNullable ? "?" : "" #> <#= 
property.Name #> { get; set; }<#= needsInitializer ? " = null!;" : "" #>