This tutorial demonstrates how to use Entity Framework (EF) Code First with Oracle Data Provider for .NET (ODP.NET). You will create .NET classes in Entity Framework, which will then create Oracle database tables and add data to those tables. Lastly, you will modify those classes using Code First Migrations and propagate the changes so that they are reflected in the database tables.
Time to Complete
Approximately 20 minutes.
Microsoft Entity Framework abstracts the relational, logical database schema and presents a conceptual schema to the .NET application. It provides object-relational mapping for .NET developers. Code First allows developers to create .NET classes, which in turn can be persisted as database objects. Code First Migrations allows developers to evolve the code-based model so that changes are accurately reflected in the persistent database store.
Starting with Oracle Data Access Components (ODAC) 12c Release 3 (220.127.116.11.1), ODP.NET supports Code First and Code First Migrations from Entity Framework 6. ODP.NET provides the data access to store the EF model inside the Oracle Database and evolve it.
This tutorial will guide you in creating an EF application that creates "Employee" and "Department" classes, then adds data to these class objects. When the application is run, these classes will be persisted as database tables with the data entered as rows. You will then modify the class by adding a new property. That property will then be added to the "Departments" table as a new column via Code First Migrations.
Before starting this tutorial, you should:
Install Microsoft Visual Studio 2013 or later with .NET Framework 4.5 or later.
Install Oracle Database 11g Release 2 or later.
Extract these files into your working directory.
To begin the tutorial, complete the Using NuGet to Install and Configure Oracle Data Provider for .NET OBE. The OBE will guide you in installing and configuring Entity Framework 6 and ODP.NET plus creating a new Visual Studio console project.
Code First doesn't require Oracle Developer Tools for Visual Studio which provides integration with Server Explorer and Visual Studio designers. This tutorial uses Server Explorer to browse the Oracle schema to view Code First database changes. To be able to do this, download and install Oracle Developer Tools for Visual Studio (18.104.22.168) or later from OTN.
This OBE will connect to the Oracle database with the HR schema, though any schema can be used as long as there are no naming conflicts.
In this section, you will create .NET "Employee" and "Department" classes and save data using these classes. When the application is run, EF Code First will create database tables to represent these classes and store a row each time the application saves data to a class instance.
Open the NuGet project that you created in the Using NuGet to Install and Configure Oracle Data Provider for .NET OBE and perform the following steps:
Open the App.config. Modify the user id and password under the <connectionStrings> section to connect to the HR schema. Typically, the HR schema credentials are: User Id=hr and Password=hr. Note the name of the connection string, OracleDbContext. We will be using that name shortly in the Code First application.
Copy the code snippet from the Programcs.txt file and paste it into the Program.cs of the Visual Studio project. The application needs Entity Framework 6 and ODP.NET assemblies and configuration from the NuGet steps.
Let's review the code. The application first connects to an Oracle database using the OracleDbContext connection string that was configured in the App.config.
It then creates an instance of the Employee and Department classes and adds data to them as seen in the screenshot below.
Next, we see how these two classes are defined by the .NET application. They each contain several properties that can be retrieved or saved. One property is commented; it will be added in the last part of this tutorial.
If you do not wish to use the HR schema, then change the last line of the program. Replace the HR in the modelBuilder.HasDefaultSchema("HR"); statement with the name of the schema you wish to use.
Click (Start) to run the application. This will create the "Employees" and "Departments" tables in the database and add one row to each of them. Note that these table names are case-sensitive.
Press any key to exit the terminal.
Let's check whether these tables and data were added. After completing the execution, expand the HR.ORCL connection in Server Explorer. Note this step requires that you have installed and configured Oracle Developer Tools for Visual Studio.
If prompted for a password, enter the password as hr and click OK.
Expand Tables under the HR.ORCL connection. Notice that the HR.Employees and HR.Departments are created as defined by the class definitions.
Right-click Departments and Employees and select Retrieve Data....
Verify that it reflects the same data that was inserted by the application.
Code First Migrations
Classes do not always stay static. They can change based on new business requirements and the database schema must change with the class definition. We will now make a change to the "Employee" class to simulate this type of situation. With a few simple commands, the change will be propagated to the database to keep in sync with the application. This is called Code First Migrations.
When you executed the previous Code First application, you may have noticed that a third table, _MigrationHistory, was created. This table tracks changes to the Code First classes.
For this part of the OBE, you will add a new Location property to the "Employee" class and have this change reflected in your database. Perform the following steps:
Select View > Other Windows > Package Manager Console. The Package Manager console opens. This console is where you enter in Code First Migrations commands to propagate .NET class changes to the the database schema.
Note: Screenshot has been cropped for better readability.
In the console, type Enable-Migrations. This step enables Code First Migrations.
In the Program.cs, uncomment the Location attribute in the Employee class to simulate adding an attribute to the .NET class.
In the Package Manager console, type Add-Migration First. This scaffolds a migration script for the model change.
Type Update-Database in the console. This applies the changes to the database schema.
We should now be able to see the change in the database. In the Server Explorer, double-click Employees.
Verify that the Location column has been added.
You can reset the OBE by deleting the _MigrationHistory, Employees and Departments table.
In this tutorial, you have learned how to:
- Create an EF Code First application with ODP.NET that persists the classes and data in Oracle tables.
- Evolve the database schema using Code First Migrations.
- You may visit the Oracle Technology Network Portal to know the latest developments in Oracle .NET.
- To learn more about Oracle .NET with Visual Studio refer to additional OBEs in the Oracle Learning Library.
- Lead Curriculum Developer: Ashwin Agarwal
- Other Contributors: Alex Keh, Christian Shay
To navigate this Oracle by Example tutorial, note the following:
- Topic List:
- Click a topic to navigate to that section.
- Expand All Topics:
- Click the button to show or hide the details for the sections. By default, all topics are collapsed.
- Hide All Images:
- Click the button to show or hide the screenshots. By default, all images are displayed.
- Click the button to print the content. The content that is currently displayed or hidden is printed.
To navigate to a particular section in this tutorial, select the topic from the list.