Entity Framework, LINQ and Model-First for the Oracle Database

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:

1. LINQ query
2. LINQ query with a lambda expression and calling a stored procedure to perform an update against the results
3. 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:

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

4. Extract these files into your working directory.

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.

1. To create a new .NET console application to run Entity Framework and LINQ application from, open Visual Studio. Click File. Select New > Project.

   

2. Select Visual C#:Windows > Console Application. Rename the project as EntityFramework. Click OK.

   

3. The project EntityFramework opens up.

   

Before creating the Entity Data Model, you need an Oracle database connection that uses ODP.NET. To create an Oracle Connection:

1. Select View > Server Explorer.

   

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

   

   

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

   

   

4. Click OK in the "Test connection succeeded" dialog box. Click OK in the Add Connection dialog box.

   

   

5. The connection has been created. Expand HR.ORCL

   

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

   

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

   

   

   

8. Make sure that the newly added stored procedures appear beneath the HR.ORCL > Procedures node after refreshing.

   

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:

1. Perform the steps mentioned in the NuGet ODP.NET Installation and Configuration section of the Using NuGet to Install and Configure Oracle Data Provider for .NET OBE.

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

1. In the Solution Explorer window, right-click the EntityFramework project and select Add > New Item.

   

2. In the Add New Item window, choose ADO.NET Entity Data Model and rename it to HRModel.edmx and click Add.

   

3. In the Entity Data Model Wizard, select Generate from database and click Next.



4. Select HR.ORCL as the data connection. Select Yes, include the sensitive data in the connection string and name it as HREntities and click Next.

   

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

   

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

   

   If you are using Entity Framework 6 version, you may receive a Security Warning popup on clicking Finish. Click Ok and continue.

   

7. The HRModel EDM has been created and is displayed.

   

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.

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

   

   However, if you are using Entity Framework 6 version, change the namespace references to reflect the graphic highlighted below.

   

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

   Click (Start).

   

3. The output of the LINQ query appears, thereby successfully executing a LINQ query against the Oracle database.

   

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.

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

   

2. In the HRModel.edmx, right-click on EMPLOYEE entity and select Stored Procedure Mapping.

   

3. In the Mapping Details - EMPLOYEE, select ,<Select Update Function> and select the stored procedure, INCREASE_SALARY_BY_10.

   

4. Parameter data types must now be mapped between the entity and Oracle database. Select EMPLOYEE_ID for ID and SALARY for SAL from the drop down lists.

   

5. 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)

   

   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.

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

   

Executing an Entity SQL query

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.

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

   

   

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.

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

   

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

   

3. The procedure runs successfully. In the Out Parameters section of the window, select the Select For Config option for the NEW_SALARY parameter.

   

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

   

5. The App.config file opens with the added REF CURSOR information.

   Note: A window might pop up asking if you want to reload the App.config file. Click Yes.

   

6. Next, you will use the Function Import tool to map a .NET method to the Oracle stored procedure. Double-click HRModel.edmx in the Solution Explorer.

   

   Right-click anywhere in the HRModel.edmx window, and select Model Browser.

   

7. In the Model Browser, expand the HRModel, then expand the Function Imports node. Double click to open the UPDATE_AND_RETURN_SALARY procedure.

   Note: You imported this procedure earlier when you created the EDM.

   

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

   

9. Click Create New Complex Type in the Stored Procedure / Function Column Information section and click OK.

   

   To call the method from .NET, you will use the default UPDATE_AND_RETURN_SALARY Function Import name.

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

    

    

    Click (Start).

    Note: Press Enter thrice to continue while viewing the resultset.

11. The .NET method returns the employee name and updated salary.

    

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.

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

   Click (Start).

   

   Note: Press Enter four times to continue while viewing the resultset.

   

This section demonstrates how LINQ can be used to insert and delete data programmatically.

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

   

2. The console describes whether the department was successfully added and deleted.

   

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.

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

   

   

   

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

   

   

3. Right-click HRModel.edmx and select Generate Database from Model.

   

4. After you select Generate Database from Model, a Custom Workflow Security Warning will appear. Click OK.

   

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

   

In this tutorial, you have learned how to:

• Create a new Project in Visual Studio.
• Create an Oracle connection.
• Create an Entity Data Model using the Entity Data Model Wizard.
• Execute queries for Entity Framework Data Retrieval.
• Map stored procedures to EDMs.
• Use Entity Framework Function Imports for creating user-defined methods mapped to stored procedures.
• Retrieve Output Parameters from Stored Procedures.
• Programmatically modify Entity Framework data.
• Use Model-First to generate Oracle DDL scripts.

Resources

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

Credits

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