Entity Framework Code First and Code First Migrations for Oracle Database

 

Overview

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.

Introduction

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 (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 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.

Prerequisites

Before starting this tutorial, you should:

  1. Install Microsoft Visual Studio 2013 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 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 (12.1.0.2) 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.

 

Code First

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.

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

    Code First: Open App.config

  2. 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.

    Code First: Open Program.cs

    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.

    Code First: Connecting using OracleDbContext

    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.

    Code First: Program.cs class definitions

    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.

    Code First: Modify HR if you wish to use another schema.

  3. 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.

    Code First: Click Start

    Press any key to exit the terminal.

    Code First: Press any key to exit the terminal

  4. 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.

    Code First: Expand HR.ORCL in the Server Explorer

    If prompted for a password, enter the password as hr and click OK.

    Code First: Enter hr as the password

    Expand Tables under the HR.ORCL connection. Notice that the HR.Employees and HR.Departments are created as defined by the class definitions.

    Code First: Notice Employees and Departments are created

    Right-click Departments and Employees and select Retrieve Data....

    Code First: Right-click Departments and select Retrieve Data...
    Code First: Right-click Employees and select Retrieve Data...

    Verify that it reflects the same data that was inserted by the application.

    Code First: Verify data in the table
    Code First: Verify data in the table
 

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:

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

    Code First Migrations: Select Package Manager Console

    Note: Screenshot has been cropped for better readability.

  2. In the console, type Enable-Migrations. This step enables Code First Migrations.

    Code First Migrations: Type Enable-Migrations

  3. In the Program.cs, uncomment the Location attribute in the Employee class to simulate adding an attribute to the .NET class.

    Code First Migrations: Uncomment Location attribute

  4. In the Package Manager console, type Add-Migration First. This scaffolds a migration script for the model change.

    Code First Migrations: Type Add-Migration First

  5. Type Update-Database in the console. This applies the changes to the database schema.

    Code First Migrations: Type Update-Database

  6. We should now be able to see the change in the database. In the Server Explorer, double-click Employees.

    Code First Migrations: Double-click Employees

    Verify that the Location column has been added.

    Code First Migrations: Location column has been added

    7. You can reset the OBE by deleting the _MigrationHistory, Employees and Departments table.

 

Summary

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.

Resources

Credits

  • Lead Curriculum Developer: Ashwin Agarwal
  • Other Contributors: Alex Keh, Christian Shay