Entity Framework Code First and Code First Migrations for Oracle Database


Options



Before You Begin

Purpose

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.

Background

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. EF 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 (12.1.0.2.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 the schema.

This tutorial will guide you in creating an EF application that generates "Employee" and "Department" classes, and 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.

What Do You Need?

Before starting this tutorial, you should:
  1. Install Microsoft Visual Studio 2015 or later with .NET Framework 4.5 or later.
  2. Install Oracle Database 11g Release 2 or later.
  3. Extract these files into your working directory.
  4. 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.
  5. Code First doesn't require Oracle Developer Tools for Visual Studio, which provide 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 (12.1.0.2) or later from OTN.

This OBE will connect to an Oracle database with the HR schema, though any schema can be used as long as there are no naming conflicts.

Code First

In this section, you will create .NET "Employee" and "Department" classes and save data using these classes. When the application is executed, 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.

  1. Open Visual Studio 2015. Open File > Open > Project/Solution.

    Oracle OBE
    Description of this image
  2. Open the NuGet project that you created in the Using NuGet to Install and Configure Oracle Data Provider for .NET OBE.

    Oracle OBE
    Description of this image
  3. Goto Solution Explorer. Open the App.config file.

    Oracle OBE
    Description of this image
  4. 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.

    Modify the Data Source in the same section and the datasource alias and descriptor in the <dataSources> section for your local database. Note the name of the connection string, OracleDbContext. We will be using that name shortly in the Code First application.

    Oracle OBE
    Description of this image
  5. Open program.cs file.

    Oracle OBE
    Description of this image
  6. The program.cs file opens up. Copy the code snippet from the Programcs.txt file, which you downloaded earlier and paste it into the Program.cs file.

    Oracle OBE
    Description of this image
  7. Review the class Program . 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.

    Oracle OBE
    Description of this image
  8. The classes are defined later on in the C# file. Review the class Employee.

    Oracle OBE
    Description of this image
  9. Review the class Department.

    Oracle OBE
    Description of this image
  10. Review the class OracleDbContext : DbContext.

    Oracle OBE
    Description of this image
  11. Build the code. Select Build > Build Solution.

    Oracle OBE
    Description of this image
  12. Run the code. Select Debug > Start Without Debugging.

    Oracle OBE
    Description of this image
  13. Observe the output.

    Oracle OBE
    Description of this image
    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.
  14. Let's verify these tables were created and the data was inserted. Go to Server Explorer and expand Data Connections.

    Oracle OBE
    Description of this image
  15. Expand HR.ORCL connection. Expand Tables > Relational Tables. Observe the two new created tables, Departments and Employees.

    Oracle OBE
    Description of this image
  16. Right click Employees table and click Retrieve Data.

    Oracle OBE
    Description of this image
  17. Verify that the Employee table reflects the structure and data that was inserted by the application.

    Oracle OBE
    Description of this image
    Note: The Name field might not display completely. There is a known tools bug with data display and EF Code First if you are using ODAC 12c Release 4.
  18. Right click Departments table and click Retrieve Data.

    Oracle OBE
    Description of this image
  19. Verify that the Department table reflects the structure and data that was inserted by the application.

    Oracle OBE
    Description of this image
    Note: The Name field might not display completely. There is a known tools bug with data display and EF Code First if you are using ODAC 12c Release 4.

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 definitions. 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:

  1. 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 database schema.

    Oracle OBE
    Description of this image
  2. In the Package Manager Console, type Enable-Migrations. This step enables Code First Migrations.

    Oracle OBE
    Description of this image
  3. In the Program.cs, uncomment the Location attribute in the Employee class to simulate adding an attribute to the .NET class.

    Oracle OBE
    Description of this image
  4. In the Package Manager Console, type Add-Migration First. This step scaffolds the migration based on model changes since the last migration.

    Oracle OBE
    Description of this image
  5. In the Package Manager Console, type Update-Database. This step applies the pending migration to the database.

    Oracle OBE
    Description of this image
  6. In the Server Explorer, right click Employees table and click Retrieve Data.

    Oracle OBE
    Description of this image
  7. Verify that the Location column has been added to the Employee table.

    Oracle OBE
    Description of this image
    Note: The Name field might not be displayed completely. There is a known tools bug with data display and EF Code First if you are using ODAC 12c Release 4. You can reset the OBE by deleting the _MigrationHistory, Employees and Departments table.

Want to Learn More?