Entity Framework : First query using Entity Framework

Background

In one of my earlier post, I had demonstrated how to use code first approach with the new Entity Framework 4.0 CTP. In this post I thought of writing simple queries against an existing database table using entity framework 4.0. This is the first post of the Entity Framework 4 learning series.

Pre-requisites

I don’t want to go though the steps of creating the database from the scratch. So I chose to use an existing database so that I can save some time required for populating the data in the tables. I have used the existing Northwind database for this purpose which can be downloaded from Microsoft site. This download also contains other sample database which is Pubs.
Once the installer is executed it creates a directory in the following location “C:\SQL Server 2000 Sample Databases”. I prefer to copy the database into the project folder.

Generate model from existing database schema

I have created a simple console application. In order to generate the conceptual entity model based on persistence model or database schema we can add a new item to the project by selecting ADO.NET Entity Data Model option from the wizard.

In the name box specify name as Northwind.edmx file. The wizard will prompt for choosing the model contents. We should select the Generate from database option. I can select the location of Northwind.mdb file and add it to the output folder of the project. The wizard also provides an option to store the connection string in the App.config file as shown below

We can choose from Tables, Views or Stored Procedures as source of our model objects. I chose Orders table for the purpose of this demo. Click on finish and Visual Studio will do some black magic behind the scenes to come up with a entity model designer window similar to the one shown below

In this example I have chosen only one table, but you can add any number of tables onto the designer and visual studio will automatically figure out the relationships and display them on the designer surface. I’ll cover that in some other post. For the time being I’ll start with querying the Orders table from the database.

 

Query database using entity model

To keep the example simple I would like to do couple of things

• Get the count of orders in the Orders table
• Display the OrderID for the first 20 orders

First and foremost I need an object context which can be used to run LINQ queries against the conceptual model. Once we generate the entity model using the wizard, Visual Studio creates a class which inherits from the ObjectModel class. It also exposes each entity as a property of the object model class. This enables us to use strong types while querying the entities.

NORTHWNDEntities context = new NORTHWNDEntities();

 

Console.WriteLine("Number of orders : " + context.Orders.Count());

 

var orders = context.Orders.Take(20);

 

foreach (var order in orders)

{

      Console.WriteLine("Order ID : " + order.OrderID);

}

As can be seen from above lines of code, I have created the context object which I made use of in the statement writing the count of orders to console output. The Orders property is strongly typed and Count() extension method gives me the count of orders in the database.

Because the number of orders are in excess of 800, I thought of restricting the output to first 20 orders. So I have made use of the Take() extension method of LINQ to filter only the top 20 records. I then loop over the collection and display the OrderID for each of those orders.


That’s all the 5 lines of code needed to get the count and also display the first 20 order ID’s from database. Isn’t it simple compared to the amount of code we would have to write using ADO.NET code? I thought it was faster than preparing Maggi noodles :)

Conclusion

It was fairly easy to get started with Entity Framework. Using LINQ with entity model provided type safe way querying the collections. Hope this helps. I have just touched upon a very basic LINQ methods here. We can run all other methods like Where, Single, SelectMany etc.


As always I have uploaded the complete source code EntityFramework_GettingStarted.zip.


Until next time Happy programming :)

Technorati Tags: Entity Framework,Northwind,ObjectContext,entity model,LINQ

Share: