ORACLE – Linq Example

Creating an Entity Data Model using the Entity Data Model Wizard

You are now ready to create our Entity Data Model based on HR’s DEPARTMENTS and EMPLOYEES tables 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.

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

Screenshot for Step


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

Screenshot for Step


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

Screenshot for Step


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.

Screenshot for Step


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

Screenshot for Step

Screenshot for Step

Screenshot for Step


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

Screenshot for Step

Entity Framework Data Retrieval

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.

1. Executing a LINQ query

To execute a LINQ query against an Oracle database, you will add a reference to an Entity Framework assembly. You will then create the code to execute a LINQ query and return the results to the console window.

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

Screenshot for Step


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

Screenshot for Step


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

Screenshot for Step


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

Screenshot for Step


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

Screenshot for Step


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: