Entity Framework : Part 3 – Query relational data using Entity Framework 4

Background

This is the third post in the Entity Framework 4 learning series. In this post I am going to demonstrate how to query relational data using Entity Framework 4. Like my previous post, I am going to use the Microsoft SQL server sample database Northwind for this example.

Generate Entity Model

Lets start by creating the Conceptual Data Model which is also called the Entity Model.We can follow the steps similar to the ones we did in the getting started example of this series to get the conceptual model automatically generated for us using the Entity Framework designer.

Right click on the project and select “Add New Item”. Select ADO.NET Entity Data Model from the available options and name the edmx file as “Northwind.edmx” as shown below.

Click on Add button and the wizard will pop up. Select Generate from database option and click on next. If we do not want to generate the model from existing database, we could select the other option which is Empty Model. This approach of creating the entity model and then generating the database is called “Model First” development. I’ll cover that in one of the future posts.

Next page in the wizard will prompt us to choose the data connection. For simplicity I copied the database file into the project folder. As a result the wizard picked it up automatically and came up with the required connection string.

We can choose the connection string to whatever we want. If we click on the new connection we are presented with the standard SQL Server connection dialog

I choose to save the connection string in the App.Config file. This gives me the advantage that I can point the database to a different server and make it work by changing only the connection string property at runtime.

In the next step, wizard prompts us to select the database objects that we intent to use as part of our entity model. We can select from Tables, Views as well as Stored Procedures.

I have selected Customers, Orders, Order Details and Products as the database objects. We can also find 2 checkboxes towards the bottom. First one is Pluralize or Singularize generated object names. This is used to automatically singularize or pluralize the entities in the entity model. If selected it will have impact on the way the designer will name the entity classes and the properties.

For e.g. the Customers table is pluralized in the database. When the designer generates the entity corresponding to Customers table it will name the entity as Customer. Similarly the associations which get modelled as collections will be pluralized. A good example of this will be that each customer will have a set of Orders. The collection which holds these orders is named as Orders in the Customer entity but at the same time individual Order is named as Order in the order entity class.

The second checkbox Include Foreign Key columns in the model is used when we want to have a explicit property to represent the foreign key. If I take the same example of Customer and Order, Orders table has the CustomerID as the foreign key reference. So the designer will create an association between the Orders and Customers table. The association will be represented by the Customer property in Order entity. At the same time there will be another property called CustomerID in the Order entity as shown below

The associations that I talked about are shown as Navigation Properties in the designer. It is highly recommended not to uncheck the two checkboxes which are selected by default. My experience says that it helps a lot while developing.

This was one of the problem area in EF version 1 and developers had to manually singularize or pluralize entities and properties. The EF design team has worked on the feedback received from the community and provided a solution pretty quickly :)

Query data using Entity Model

Once we have the model ready we can query it to get the desired results. I want to show two things in this post. Lets see how we can use the Navigation Properties to navigate from a parent to a child relationship. And secondly the reverse way where we navigate to the parent based on the child entity.

From the above diagram of entity model, we can figure out that the Customer and Order entities have a one to many relationship. One customer can have many orders. Lets query the entity model to find all the orders placed by the first customer in the database.

            NORTHWNDEntities context = new NORTHWNDEntities();

 

            Customer firstCustomer = context.Customers.First();

 

            Console.WriteLine("Customer name : {0}", firstCustomer.ContactName);

 

            var orders = firstCustomer.Orders;

 

            foreach (var order in orders)

            {

                Console.WriteLine("Order ID : {0} dated : {1}", order.OrderID, order.OrderDate.Value.ToLongDateString());

            }

First of all we need to create an instance of the ObjectContext. The designer creates a class for us called NORTHWNDEntities based on the options we selected during the creation of entity model. This class has various properties and methods which we can use from our code.

The 2nd line of code gets a reference to the first customer in the database using a LINQ query. We display the name of the customer on the console in the next line. In order to get all the Orders associated with this customer, we make use of the navigation property Orders. Finally we loop over the collection of orders and output the OrderID and the OrderDate to the console window.

We can also use the other side of this relationship and find out the customer details based on the order information.

            Order firstOrder = context.Orders.First();

 

            Customer firstOrderCustomer = firstOrder.Customer;

 

            Console.WriteLine("Customer Name : {0}", firstOrderCustomer.ContactName);

In this case I have got reference to the first order and used the Customer navigation property to access the customer details.

Conclusion

As we can see from the above examples that it is very easy to query the relational data using Entity Framework. Those of you who have worked previously with LINQ2SQL will find the code very similar. The main difference between LINQ2SQL queries and Entity Framework queries is that, Entity Framework queries run against the conceptual data model or the entity model which is a layer above the domain model. LINQ2SQL queries are run directly against the domain model.

The examples I demonstrated here were using the LINQ approach. We can also use another for of queries supported by EF called the Entity SQL. I’ll leave those queries for another blog post.

As usual I have uploaded the complete source code for this demo to drop box which can be downloaded here. Happy Programming until next time :)

Technorati Tags: Entity Framework 4,Northwind,Entity Model,ObjectContext,EDMX,Model First,Navigation Properties,LINQ2SQL,LINQ,Entity SQL

Share: