Entity Framework With Oracle

Although EF6 is coming soon, it is a big pity that Oracle databases can only be programmed with DB first and Model First, rather than Code First.

Let's take a look at how EF uses DB first and Model First to program Oracle.

First we want to download the ODP. NET data driver, download link: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

After the installation is successful, we can select ODP. NET when connecting to the Oracle database through,

Model First

The model first sets up the data model, then generates the corresponding database script based on the model, and then generates the database based on the script.

Add an ADO. NET Entity model: OracleModel. edmx in the project, select "Empty model", and create two new entities: Destination and Lodging,

To see the data types of the attributes in these two models, I also posted the classes they generated:

View Code

 public partial class Destination    {        public Destination()        {            this.Lodging = new HashSet<Lodging>();        }            public int DestinationId { get; set; }        public string Name { get; set; }        public string Country { get; set; }        public byte Photo { get; set; }        public string Description { get; set; }            public virtual ICollection<Lodging> Lodging { get; set; }    } public partial class Lodging    {        public int LodgingId { get; set; }        public string Name { get; set; }        public string Owner { get; set; }        public bool IsResort { get; set; }        public decimal MilesFromNearestAirport { get; set; }        public int DestinationDestinationId { get; set; }            public virtual Destination Destination { get; set; }    }

Right-click the blank area of the object model and choose properties. In the OracleModel Properties window that opens, set some properties and change the DDL generation template:SSDLToOracle. tt (), Change the database architecture name:GYOUNG(This is the username of the Oracle database I tested. You can change it based on your changes.) change the workflow generated by the database:Generate Oracle Via T4 (TPT). xaml ()

In order to make EF better understand the relationship between the data types in. NET and the data types in Oracle. We can add the following configuration file to app. config.

  <oracle.dataaccess.client>    <settings>      <add name="bool" value="edmmapping number(1,0)" />      <add name="byte" value="edmmapping number(3,0)" />      <add name="int16" value="edmmapping number(4,0)" />      <add name="int32" value="edmmapping number(9,0)" />      <add name="int64" value="edmmapping number(18,0)" />    </settings>  </oracle.dataaccess.client>

Now we can generate the script for the database.

Right-click the blank area and select "generate Database Based on Model"

Then establish a data connection,

Click Next to generate a data script.

View Code

-- Creating table 'Destinations'CREATE TABLE "GYOUNG"."Destinations" (   "DestinationId" NUMBER(9,0) NOT NULL,   "Name" NCLOB NOT NULL,   "Country" NCLOB NOT NULL,   "Photo" NUMBER(3,0) NOT NULL,   "Description" NCLOB NOT NULL);-- Creating table 'Lodgings'CREATE TABLE "GYOUNG"."Lodgings" (   "LodgingId" NUMBER(9,0) NOT NULL,   "Name" NCLOB NOT NULL,   "Owner" NCLOB NOT NULL,   "IsResort" NUMBER(1,0) NOT NULL,   "MilesFromNearestAirport" NUMBER(38,0) NOT NULL,   "DestinationDestinationId" NUMBER(9,0) NOT NULL);-- ---------------------------------------------------- Creating all PRIMARY KEY constraints-- ---------------------------------------------------- Creating primary key on "DestinationId"in table 'Destinations'ALTER TABLE "GYOUNG"."Destinations"ADD CONSTRAINT "PK_Destinations"   PRIMARY KEY ("DestinationId" )   ENABLE   VALIDATE;-- Creating primary key on "LodgingId"in table 'Lodgings'ALTER TABLE "GYOUNG"."Lodgings"ADD CONSTRAINT "PK_Lodgings"   PRIMARY KEY ("LodgingId" )   ENABLE   VALIDATE;-- ---------------------------------------------------- Creating all FOREIGN KEY constraints-- ---------------------------------------------------- Creating foreign key on "DestinationDestinationId" in table 'Lodgings'ALTER TABLE "GYOUNG"."Lodgings"ADD CONSTRAINT "FK_DestinationLodging"   FOREIGN KEY ("DestinationDestinationId")   REFERENCES "GYOUNG"."Destinations"       ("DestinationId")   ENABLE   VALIDATE;-- Creating index for FOREIGN KEY 'FK_DestinationLodging'CREATE INDEX "IX_FK_DestinationLodging"ON "GYOUNG"."Lodgings"   ("DestinationDestinationId");-- ---------------------------------------------------- Script has ended-- --------------------------------------------------

We only need to execute the script in the database to generate the corresponding table. Analyze the generated SQL statement, which has a primary key and a foreign key, but does not set auto-increment for the primary key. Setting auto-growth in Oracle is also a tough problem. We need to set the relevant Sequences and Triggers and get used to the IDENTITY of SQL SERVER. This is really unpleasant. Let's just insert the primary key by ourselves. The following is the test code:

View Code

 using (OracleModelContainer context = new OracleModelContainer())            {                var destination = new Destination                {                    DestinationId=1,                    Country = "Indonesia",                    Description = "EcoTourism at its best in exquisite Bali",                    Name = "Bali"                };                var lodging = new Lodging                {                    LodgingId=1,                    Owner="Jshon",                    Name = "Top Notch Resort and Spa",                    MilesFromNearestAirport = 30,                    IsResort=true,                    Destination=destination                };                context.Lodgings.Add(lodging);                context.SaveChanges();            }

After connecting to Oracle through VS, you can see that the data is successfully inserted.

DB First

DB First, as its name implies, is to First build a database and then program it. We create a new project and program it with the newly generated table.

Add an "ADO. NET Object Data Model" to the new project: DBModel. edmx, and select "generate from database"

Set the connection string

Select Table

Click Finish to generate the corresponding model.

Let's retrieve the inserted data.

View Code

 using (Entities context = new Entities())            {                var des = context.Destinations.FirstOrDefault();                var log = context.Lodgings.FirstOrDefault();                Console.WriteLine("Lodging  Name:" + log.Name + " Owner:" + log.Owner);                Console.WriteLine("Destination   Name:" + des.Name + " Country:" + des.Country);            }

Result.

PS: In DB First mode, you must add the ing configuration file mentioned in Model First to App. config. Otherwise, many data type mappings may fail.