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

Install Microsoft Visual Studio 2010 with .NET Framework 4 or higher.

Install Oracle Database 10.2 or later or Oracle Database XE.

Install Oracle Data Access Components (ODAC) 11.2.0.3 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.

Extract these files into your working directory.

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

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

The project EntityFramework opens up.

Select View > Server Explorer.

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 the User name and a 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 and select a Data source name with the HR schema, such as ORCL. Enter HR for the User name and a Password. Check Save password. Select Default as the Role and click Test Connection.

Click OK in the "Test connection succeeded" dialog box.

The connection has been created. Expand HR.ORCL

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.

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

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

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

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

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.

Select DEPARTMENTS and EMPLOYEES from Tables and INCREASE_SALARY_BY_10 , UPDATE_AND_RETURN_SALARY and OUTPARAM from Stored Procedures 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 in the HR schema under Procedures.

The HRModel EDM has been created and is displayed.

In the Solution Explorer window, right-click References and select Add Reference.

On the .NET tabbed page, select System.Data.Entity version 4 assembly and click OK.

Type the namespace references highlighted in the graphic below. 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.

Click (Start debugging).

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

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, select HRModel.edmx and click Model Browser.

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

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

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.

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 Debugging)

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.

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 Debugging) to view the updated results with the salaries increased by 10.

Note: Press Enter twice to continue while viewing the output.

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 define the resultset metadata in the .NET config file before using the tool.

In the Solution Explorer window, open the App.Config file.

From the working directory where you extracted the files.zip, find the AppConfig.txt file and open it. Copy code for <oracle.dataaccess.client> section and paste it below the </connectionStrings> tag in the App.Config file. Now, you have defined the metadata for retrieving the stored procedure resultset. Here, the required attributes that must be defined for scalar data columns include: Column Name, Native Oracle Data Type, and Provider Type. This is seen in the SALARY column attributes in the config. Additional attributes are optional for scalar columns. The FIRST_NAME column shows some of these optional attributes.

Next, you will use the Function Import tool to map a .NET method to an Oracle stored procedure. Double-click HRModel.edmx and select Model Browser.

In the Model Browser, expand the HRModel.Store, then expand the Stored Proceduresnode. Select the UPDATE_AND_RETURN_SALARY procedure. You imported this procedure earlier when you created the EDM.

Right-click UPDATE_AND_RETURN_SALARY and select Add Function Import.

In the Add 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.

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

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

In the Model Browser, you will now see the UPDATE_AND_RETURN_SALARY under HRModel.edmx > HRModel > EntityContainer: HREntities > Function Imports.

In the Solution Explorerwindow, 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 resulset.

Click (Start Debugging).

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

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

In the Model Browser, expand the HRModel.Store, then expand the Stored Procedures node. Select the OUTPARAM procedure. You imported this procedure earlier when you created the EDM.

Right-click OUTPARAM and select Add Function Import.

In the Add Function Import window, select None in the "Returns a Collection Of" section. The stored procedure returns an output parameter, not a collection in this case. To call the method from .NET, you will use the default OUTPARAM Function Import name. Click OK.

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 now 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 returns the parameter.

Click (Start Debugging).

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

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.

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

The console will describe whether the department was successfully added and deleted.

In the HRModel.edmx, select the EMPLOYEE entity.To create a new property in EMPLOYEE entity, right-click on it and select Add > Scalar Property. Name the property as ADDRESS.

To generate the DDL scripts, open the Properties window of HRModel. Change the Database Schema Name to HR and select SSDLtoOracle.tt for the DDL Generation Template. Make sure that Generate Oracle via T4(TPT).xaml 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.

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

After you select Generate Database from Model, a Custom Workflow Security Warning will appear since Oracle defines a custom workflow. Click OK.

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 that the script creates and deletes database objects. By default, the delete scripts are commented out. If you wish to use them, make sure to uncomment them before executing.