Using the Entity Framework 6 Codefirst in Oracle

The project needs to migrate the system from the SQL Server database to Oracle. Since most of the data access operations were implemented by wrapping the unified Access portal of the Entity Framework, it was necessary to study how the Entity Framework was transferred from SQL Server to Oracle.

Since ef4.x, Oracle has provided driver support for EF, but unfortunately the Codefirst mode is not supported. Fortunately, starting with Odp.net 11.2.0.3.0, Oracle has officially provided EF drivers that support Codefirst's pure managed code. But the following points are needed to know;

1. Odp.net for. NET Framework 4.0 supports the Entity Framework and LINQ to Entities, but odp.net for. NET Framework 2.0 is not supported.
2. Code first features can only be used in versions of the entity Framework above 6.
3. Odp. The NET and Entity Framework support scalar parameter binding. The Entity framework supports parameter binding by name and does not support parameter binding through a location.
4. Supports access to Oracle 10g RELEASE2 and later versions only
5. Using upgradeable and distributed transactions requires Oracle service for Microsoft Transaction Server 12.1. Odp. NET supports only read-level isolation in distributed transactions.

How to use Codefirst

The next thing we really need to be concerned about is how to use Codefirst in a project (this article defaults to what you already know about Codefirst concepts and how to use them).

There are two ways to find the DLLs we need: Download via Oracle website (in \odp.net4\odp.net\managed\common), install through NuGet. I recommend using the NuGet installation here. In the Package management console, enter:

Install-package Oracle.ManagedDataAccess.EntityFramework

Modify the app. config configuration as follows:

<?xml version="1.0" encoding="utf-8"?>

<configuration>

 <configSections>

   <section name="entityFramework"

     type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

     requirePermission="false"/>

 </configSections>

 <connectionStrings>

   <add name="SampleDataSource" providerName="Oracle.ManagedDataAccess.Client"

   connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=**SERVICE_NAME**)));Persist Security Info=True;User ID=**User ID**;Password=**Password**"/>

 </connectionStrings>

 <entityFramework>

   <defaultConnectionFactory

     type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>

   <providers>

     <provider invariantName="Oracle.ManagedDataAccess.Client"

       type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>

   </providers>

 </entityFramework>

 <system.data>

   <DbProviderFactories>

     <remove invariant="Oracle.ManagedDataAccess.Client"/>

     <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"

     type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>

   </DbProviderFactories>

 </system.data>

 <runtime>

   <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

     <dependentAssembly>

       <publisherPolicy apply="no"/>

       <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>

       <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.121.2.0"/>

     </dependentAssembly>

   </assemblyBinding>

 </runtime>

</configuration>

define an Entity object:

 
public class SomeInfo
    {
        public Guid Id { get; set; }
        public DateTime DateTimeField { get; set; }
        public bool BooleanField { get; set; }
        public decimal DecimalField { get; set; }
        public int IntField { get; set; }
        public string StringField { get; set; }
    }

Define the data context:

public class TestDbContext: DbContext
     {
         public TestDbContext ()
             : base ("name = SampleDataSource")
         {
             Database.SetInitializer (new DropCreateDatabaseIfModelChanges <TestDbContext> ());
         }

         public DbSet <SomeInfo> SomeInfos {get; set;}

         protected override void OnModelCreating (DbModelBuilder modelBuilder)
         {
             // The schema needs to be specified here, the default is dbo for sqlserver
             modelBuilder.HasDefaultSchema ("YOURSCHEMA");
             base.OnModelCreating (modelBuilder);
         }
     }

You can write a test code to verify that the connection string is correct. The code for validation is not covered here.

. NET type to Oracle type mapping

Because Oracle and SQL Server have different data types, there is a need to introduce the following. NET data types and mappings for Oracle data types.

type

. NET Data	Oracle data type	mapping method
Boolean	Number (1, 0)	Using EDM mapping, note that you need to use the EDM mapping configuration, refer to the additional Information Document EDM Mapping section.
Byte	Number (3, 0)	Using EDM mapping, note that you need to use the EDM mapping configuration, refer to the additional Information Document EDM Mapping section.
Byte[]	Blob	Default
Int16	Number (5, 0)	By default, note: You need to use the EDM mapping configuration, refer to the additional Information Document EDM Mapping section.
Int32	Number (10, 0)	By default, note: You need to use the EDM mapping configuration, refer to the additional Information Document EDM Mapping section.
Int64	Number (19, 0)	By default, note: You need to use the EDM mapping configuration, refer to the additional Information Document EDM Mapping section.
Decimal	Number (18, 2)	Default
Single	Binary_float	Default
Double	Binary_double	Default
Guid	Raw (16)	Default
Datetime	Date	Default
DateTimeOffset	Timestamp Withtime Zone	Default
String	Nclob	Default
String	Clob	Set Unicode to False using the Isunicode () Fluent API
String	Nvarchar2	Use Hasmaxlength () Fluent API or maxlength tag settings maxlength not greater than 2000
String	Varchar2	Use the Hasmaxlength () Fluent API or maxlength tag settings maxlength not greater than 4000, set Unicode to False using the Isunicode () Fluent API
String	NChar	Use the Hasmaxlength () Fluent API or maxlength tag settings maxlength not greater than 1000, set column type to nchar using the Hascolumntype () Fluent API or the column tag
String	Char	Use the Hasmaxlength () Fluent API or maxlength tag settings maxlength not greater than 2000, set column type to nchar using the Hascolumntype () Fluent API or the column tag
String	Long	Use the Hascolumntype () Fluent API or the column tag to set the column type to long, note that the Long data type is not recommended.
String	rowID	Set column type to ROWID using the Hascolumntype () Fluent API or the column tag
String	Urowid	Set column type to Urowid using the Hascolumntype () Fluent API or the column tag

Attention:

Character-based columns, that is: Char,nchar,varchar2,nvarchar2 can store characters that are specified (MaxLength). But constrained by Oracle's design, these columns can only store up to 4000 byte. The stored data and setting of the database character set can cause some characters to require multiple bytes, so although these types of columns are configured as 4000 (MaxLength), they may not store so many characters. If you want to store more than 4000byte of data, you can use CLOB or nclob type columns.

Feature configuration for Oracle data types

The following table lists the data annotation and fluent APIs supported by Oracle:

Data Annotation	Fluent API	Purpose	applied to
Key	Haskey	Sets the primary key.	All Scalar Types
Required	IsRequired	The setting column is not NULL.	All
MaxLength	Hasmaxlength	Set column maximum length	String
notmapped	Ignore	You do not need to map this property	All
Concurrencycheck	Isconcurrencytoken	The column needs to be used as an optimistic concurrency check Note: Do not use the string property with no length limit, because this is mapped to the LOB type. The most concurrent check of columns using LOB types causes ora-00932:inconsistent datatypes error.	All
TimeStamp	Isrowversion	concurrency control fields	Not supported
Column	Hascolumntype	Specifies the corresponding database data column type. Note: You must be a valid compatible type. For example, a date property is not mapped to the number column.	All
N/A	Isunicode	Represents the mapping to a N-type type (NVARCHAR2 or NCLOB), which is true by default.	String
N/A	Hasprecision	Represents the precision of setting decimal.	Decimal

Code First Data migration

The way data is migrated is consistent with SQL Server, but the following two points need to be noted:

1. The only thing that can be customized is the user schema of the change table
2. Code First Auto-migration can only use the DBO schema. So you need to display a code-based data migration using the Add-migration command.

Code First Database initialization

Odp. NET supports several types of database initialization:

• Createdatabaseifnotexists (default)
• Dropcreatedatabasealways
• Dropcreatedatabaseifmodelchanges
• Nulldatabaseinitializer
• Migratedatabasetolatestversion

Because Oracle and SQL Server have different definitions of the database, the database initialization action acts on all Oracle objects in the model. The Oracle database is not created or deleted, and the object that makes up the model is considered an operation on the database.

Oracle Database object creation

In order to support the client application, the ODP. NET to create and maintain the required database objects. The following lists the database objects that provider provides to create and maintain:

• Table
• Table Column
• Primary Key
• Foreign Key
• Index
• Sequence
• Trigger

Attention:

Sequence and trigger are created in versions after Oracle 11g R2, and earlier versions of the database support identity columns.

Directly related to the client, that is, a table for a class, a column corresponding to a property, the names of these objects are provided by the client, the object must be named to meet the object identity length limit in the Oracle database. If the length of the class name is too long, the exception of Ora-00972:identifier is too long will be reported when the object is created.

For the remaining objects, if the provided name length is greater than the database identity length display, then Odp.net takes advantage of the named generation algorithm. If the name provided is not long, then use it directly. In all cases, object names are created as quoted identifiers on the one hand to preserve case, and any special characters can be part of an identifier on the other.

The naming algorithm works in one of the following two ways:

• Intercept a paragraph from the original name from the beginning
• Calculates a numeric suffix from the original name

The following example shows how a class object name is intercepted:

	public class LongSamplePocoTestClassName



	{



	 [Key]



	 public int Id { get; set; }



	





	 [MaxLength(64)]



	 public string Name { get; set; }



	}

The default generated primary key name is:

Pk_longsamplepocotestclassnames

This name contains 31 characters (up to 30 characters) and exceeds the limit. It will be intercepted and finally generated:

pk_longsamplepocotes_730795129

The algorithm is designed to retain as much of the original name as possible.

original articles, reproduced please specify: reproduced from the Xdlysk blog

This article link address: using the entity Framework 6 Codefirst in Oracle

Using the Entity Framework 6 Codefirst in Oracle