Entity Framework, LINQ and Model-First for the Oracle Database
Entity Framework, LINQ and Model-First for the Oracle Database
Overview
Purpose
This tutorial covers how to use Entity Framework 5 or 6, Language
Integrated Query (LINQ), and generate Data Definition Language
(DDL) scripts using Model-First for Oracle database.
Time to Complete
Approximately 30 mins.
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.
LINQ is a .NET data querying language which can query a multitude
of data sources, one of which are entities via LINQ to Entities.
Model-First allows the conceptual model to be created first by
the developer. Next, Visual Studio can create Oracle DDL scripts
to generate the relational database model based on the conceptual
model.
In this tutorial, you will learn how to use Oracle Database and
these three technologies with Microsoft Visual Studio and Oracle
Data Provider for .NET (ODP.NET). You will start by creating an
Entity Data Model (EDM) from an existing Oracle Database schema
using Entity Data Model Wizard. Then, you will query the EDM using
three different methods:
LINQ query
LINQ query with a lambda expression and calling a stored
procedure to perform an update against the results
Entity SQL
Next, you will call a stored procedure added to the EDM via a
function import. The stored procedure will modify the database
data and return the results with an implicit resultset. To return
the resultset, you will run a Oracle wizard that provides the
resultset parameter information in a .NET config file. You will
then see how to run a stored procedure returning a scalar
parameter value. Then, you will see how to insert and delete data
to the database without using a stored procedure.
Lastly, you will use Model-First with Oracle database. In a
Model-First scenario, a developer first creates an Entity
Framework object-relational data model. From that data model, the
developer can automatically generate an Oracle relational database
model in the form of DDL scripts.
Prerequisites
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.
Install Oracle Data Access Components (ODAC) 12c Release 3 (12.1.0.2.1) or
later from OTN. The ODAC download includes Oracle Developer
Tools for Visual Studio and ODP.NET that will be used in this
lab.
Note: If you have installed ODAC 12c Release 3 configured on a machine-wide level, you will see an error when trying to generate database scripts using Entity Framework Model-First. To resolve this issue, use an ODAC version later than this release or reinstall ODAC 12c Release 3 with the "Configure ODP.NET at a machine-wide level" box UNchecked.
Creating a new Project in Visual Studio
To create a new .NET console application to run Entity
Framework and LINQ application from, open Visual Studio.
Click File. Select New > Project.
In the Server Explorer window, if you already have a HR
schema connection, expand it to connect to the database. As an
example, the screen shot below identifies the HR schema as
HR.ORCL. Enter HR for both the User name and Password
and click OK. Skip to Step 6.
If you do not have a HR connection, then right-click on Data
Connections in Server Explorer. Choose Add
Connection. Enter HR for both the User name and
Password. Check Save password. Select Data source name
with the HR schema, such as ORCL. Click Test
Connection.
You will now create the stored procedures that will be used
later in this lab to perform updates and data retrieval. To
add the stored procedures into the HR.ORCL data connection,
right-click HR.ORCL and select Query Window.
Open the working directory to where you extracted the files.zip.
Find the INCREASE_SALARY_BY_10, UPDATE_AND_RETURN_SALARY
and OUTPARAM stored procedures. Copy the code
for INCREASE_SALARY_BY_10, UPDATE_AND_RETURN_SALARY
and OUTPARAM into the SQL Query Window and
click Execute Query ()for each of the stored procedures.
Creating an Entity Data Model using the Entity Data Model Wizard
You will now create an Entity Data Model based on HR's
DEPARTMENTS and EMPLOYEES table and the three new stored
procedures that you just added. To do so, you will add an EDM to
our project. The EDM will be automatically generated using the
Entity Data Model Wizard.
Note: If you wish to use the Entity Framework 6 version, perform the following steps first:
Select Build > Rebuild Solution from the Visual Studio menu, which will allow this project to start using Entity Framework 6.
These steps install and configure the ODP.NET Entity Framework 6 and Entity Framework 6 assemblies.
Whether you use Entity Framework 5 or Entity Framework 6, you can continue with the
following steps.
In the Solution Explorer window, right-click the EntityFramework
project and select Add > New Item.
Select Entity Framework 5.0 version and click Next.
Note: You will not receive this window if you are using the Entity Framework 6 version and have performed the Using NuGet to Install and Configure Oracle Data Provider for .NET OBE. You will automatically be redirected to the next step.
Select DEPARTMENTS and EMPLOYEES from Tables
and INCREASE_SALARY_BY_10, UPDATE_AND_RETURN_SALARY
and OUTPARAM from Stored Procedures and
Functions. Rename the Model Namespace to HRModel and click Finish.
Note: If these stored procedures do not appear, make
sure that you added them earlier in the OBE -
INCREASE_SALARY_BY_10, UPDATE_AND_RETURN_SALARY and OUTPARAM
to the HR schema under Procedures.
There are a number of ways to query the EDM, which then
retrieves data from the database. This section will demonstrate
three common EDM querying methods: LINQ, LINQ with lambda
expressions, and Entity SQL.
Executing a LINQ query
To execute a LINQ query against an Oracle database, you will
create the code to execute a LINQ query and return the results
to the console window.
Type the namespace references highlighted in the graphic
below (applicable only for Entity Framework 5 version). Alternatively, you can copy and paste this
information. Open the files folder containing Programcs.txt
from your working directory. Then, copy the code including
the namespace references and paste them at the top of the
Program.cs.
These directives allow access to the ADO.NET, Entity
Framework and Object Services namespaces. It is not
necessary to reference ODP.NET namespaces in this tutorial
as ODP.NET is being used indirectly via Entity Framework.
Type in the .NET code below. The code executes a LINQ
query against the EDM you just created. It then accesses
the result and outputs it to the screen. The LINQ query
retrieves all employee information with EMPLOYEE_ID
less than the max_id variable.
Alternatively, you can copy the code from Programcs.txt
for the LINQ query and paste it on Program.cs
after the Main statement. Make sure you include an end
curly brace to terminate the USING scope.
Executing a LINQ query using a lambda expression and Entity
Framework stored procedure mapping
LINQ queries can include lambda expressions. Lambdas are used
as LINQ arguments to standard query operator methods.
Updates, inserts, and deletes can be executed on an entity by
mapping Oracle stored procedures to these operations in the
EDM.
In the below mentioned steps, you will execute a LINQ query
with a lambda expression against the EMPLOYEE entity, then map
a stored procedure to execute an update against all rows
selected. You will use one of the stored procedures you
imported earlier into the EDM.
First, you will create a stored procedure mapping for
updating the data. When .NET tries to update the data, the
mapped stored procedure will execute for every row
selected by LINQ.
In the Solution Explorer window, double click HRModel.edmx.
Type in the highlighted code below or copy the code from
the Programcs.txt from the section labeled "LINQ
using lambda expressions --" and paste it in Program.cs
after the previous code statements. Click (Start)
View Image
In the application, you will notice that the result.SALARY
is set to 17000. Under the covers, Entity Framework is
executing the stored procedure as well. The salary is set to
17000, then increased by 10 in the stored procedure,
providing custom logic in addition to the default behavior.
In the next section, you will query these same rows to
demonstrate the salaries have indeed been set to 17010. You
may define your own stored procedures to override default
behavior.
Note: Press Enter to continue after viewing the
first resultset.
LINQ retrieves the two rows of data and updates each row
by executing the stored procedure. You should see text
that indicates the salaries have been updated.
In this section, you will query the same rows as in the
previous section to show that the stored procedure
successfully updated the selected rows. You will use another
query method available in Entity Framework called Entity SQL.
Type the highlighted code below or copy code from Programcs.txt
from the section labeled "Entity SQL -- "and paste
it in Program.cs after the previous code
statements. Click
(Start) to view the updated results with the salaries
increased by 10.
Note: Press Enter twice to continue while viewing
the output.
Function Imports and Retrieving Implicit Resultsets | Inserting
and Updating Data Directly
Entity Framework allows developers to define their own .NET
methods to execute database stored procedures. This is done by
creating function imports in the conceptual model that map to
stored procedures. In this section, you will define your own
method that is mapped to an Oracle stored procedure, which
performs an update. The stored procedure will also return the
modified data using an implicit resultset. The resultset metadata
is defined by the developer in a .NET config file so that Entity
Framework can properly retrieve the data. Oracle has introduced a
wizard to automatically generate the resultset metadata.
This section shows you how to modify Oracle database data
programmatically in Entity Framework. You will perform an insert
and a delete on the DEPARTMENTS table.
You will use Visual Studio's Function Import tool to map the
stored procedure to a user-defined .NET method. Since the
stored procedure returns an implicit resultset, you need to
use a wizard to generate the resultset metadata in the .NET
config file before using the tool.
In the Server Explorer window, expand Procedures in
the HR.ORCL connection. Right-click on UPDATE_AND_RETURN_SALARY
and choose Run.
Note: Please use the exact same connection you started
off this OBE with. Do not use an ODP.NET, Managed Driver
connection if you began with an ODP.NET, Unmanaged Driver
connection and vice versa.
The Run Procedure dialog opens up. Since we're not
interested in the stored procedure's result running outside
the application, we can just leave the input parameters
unchanged and click OK.
Click the Show Config button. It displays the
resultset and column metadata that will be added to the
App.config file. Then, click the Add Config to Project
button to add the metadata to the App.config file.
In the Edit Function Import window, select Complex
in the Returns a Collection Of section. The stored
procedure returns a result set with two columns, not a
fully-defined entity nor a scalar value.
Click Get Column Information. The column information
will be retrieved from the .NET config file.
To call the method from .NET, you will use the default UPDATE_AND_RETURN_SALARY
Function Import name.
In the Solution Explorer window, open the Program.cs
file. Type or copy the following code from the Programcs.txt
- "Update salary using a stored procedure function import"
after the previous code statements. Notice that the entity
context now has an UPDATE_AND_RETURN_SALARY method
defined. This method will call the mapped stored procedure and
return the implicit resultset.
Retrieving Output Parameters from Stored Procedures
This section demonstrates how to retrieve output parameters from
stored procedures within Entity Framework.
The output parameter needs only to be declared explicitly in .NET and map
to an output parameter in the stored procedure. This is different
from older Entity Framework versions when a function import was
required for stored procedure output parameters.
In the Solution Explorer window, open the Program.cs
file. Type or copy the following code from the Programcs.txt
- "Retrieving output parameters from stored procedures"
after the previous code statements. Notice that the entity
context has an OUTPARAM method defined. An ObjectParameter
is bound to the OUTPARAM method to retrieve the output
parameter. This method will call the mapped stored procedure
that return the parameter.
This section demonstrates how LINQ can be used to insert and
delete data programmatically.
Type or copy the code that inserts and deletes the new
department entry in your Program.cs file. You can copy
from the Programcs.txt file and paste it to the Program.cs
file after the previous code statements.
The program comments describe what each code segment does.
Click (Start).
Note: Press Enter six times to continue while
viewing the resultset.
In this section, you will add a new property to the EMPLOYEE
entity. To reflect the new property in the Oracle database schema
as a column, Visual Studio will generate Oracle DDL scripts from
the new EDM. These scripts can then be run against the Oracle
database to update the relational model.
In the HRModel.edmx, select the EMPLOYEE entity. To
create a new property in EMPLOYEE entity, right-click on it
and select Add New > Scalar Property. Name
the property as ADDRESS.
To generate the DDL scripts, right-click on the HRModel
in the Model Browser and select Properties. Change the
Database Schema Name to HR and select SSDLtoOracle.tt
(VS) for the DDL Generation Template. Make sure
that Generate Oracle via T4(TPT).xaml (VS) is selected
in the Database Generation Workflow property to ensure
table per type DDL will be generated.
These selections ensure that Oracle DDL is created for the HR
schema where each type represents a separate database table.
The Generate Database Wizard generates DDL scripts for Oracle
database to execute. These scripts can be saved to a file to
be run later, such as through the built-in SQL*Plus execution
engine that is part of Oracle Developer Tools for Visual
Studio.
Note: The script creates and deletes database objects.
By default, the deleted scripts are commented out. If you wish
to use them, make sure to uncomment them before executing.
)for each of the stored procedures.
(Start).
Overview