ADO.NET Entity Framework – Querying the Data Model in Code

In my previous post we looked at how to bind ASP.NET controls directly to the Entity Framework (EF) data model using an EntityDataSource object. Whilst this technique offers a very simple and efficient way to databind your pages, it’s usefulness is limited and you may find that for complete control and flexibility you will better served by learning how to access the EF in code. You may find that databinding in code is more complex as there are many techniques and technologies to learn. However, if you are going to use the EF in a real world project you will soon discover handling the EF in your code behind pages, appears to be the most suitable and practical solution.

As there is too much to cover in one post, this article will try to provide an overview of the different ways you can query the data model to get subsets and single instances of data entities.

Overview of Querying Technology

Due to the way the Microsoft technologies have evolved, there are now many different ways to query the data in an Entity Data Model (EDM). I suggest you investigate each technology to find a technique that best suits your coding style and existing skill-set. The main ways to query an EDM are:

  • LINQ to Entities
  • Object Services and Entity SQL
  • LINQ Methods
  • Query Bulder Methods

LINQ to Entities

This method is my favourite way of querying the EDM. It’s pretty easy to read and whilst you are building your query, Visual Studio helps out by providing intellisense on the object field names.


DIM objModel As New PUBS_Context
Dim objTitles = From t In objModel.titles
Where t.pub_id = intPub_ID
Select New With
{t.title_id, t.pub_id, t.title1, t.type, t.price, t.ytd_sales, t.pubdate}

Object Services and Entity SQL

Here is the same query but using object services and Entity SQL with parameters.


DIM objModel As New PUBS_Context
Dim objParam As New System.Data.Objects.ObjectParameter("strPub_ID", strPub_ID)
Dim strSQL As String = "SELECT VALUE t FROM PUBS_Context.titles AS t WHERE t.pub_id = @strPub_ID"
Dim objTitles = New System.Data.Objects.ObjectQuery(Of title)(strSQL, objModel)
objTitles.Parameters.Add(objParam)

LINQ Methods

Again, here is the same titles query but this time using LINQ Methods.


DIM objModel As New PUBS_Context
Dim objTitles = objModel.titles _
.Where(Function(t) t.pub_id = strPub_ID) _
.OrderBy(Function(o) o.pubdate)

Query Bulder Methods

Finally, the same query but using Query Builder Methods.


DIM objModel As New PUBS_Context
Dim objTitles = objModel.titles _
.Where("it.pub_id = '" & strPub_ID & "'") _
.OrderBy("it.pubdate")

 

To see how these methods can be applied in a more realistic environment, I have adapted the Simple Bind project that uses the Pubs database to create a master detail view of publications and their respective titles. You can download the project files here.

Conclusion

As a developer it’s frustrating to have so many choices when it comes to something as simple as writing a query. This is especially the case when working in teams, you could easily find yourself picking up a project that’s been worked on by a number of developers and has a combination of all of these methods. I suggest that before you start implementing the EF into live projects, you take a look at all the techniques available and try to stick to one or two methods.

 

ADO.NET Entity Framework – Binding Entities Directly to ASP.NET Controls

If you have followed the previous post you will now have a web site project with a entity data model mapped to the Pubs database. In this post we shall discover how to bind your data model directly to ASP.NET Controls. This is probably not the way you will use your data model in a production environment; however, it’s a good place to start to demonstrate how quickly you set up a project and have a working prototype in a few hours using the EF.

In this first example we are going to simulate the building of a web application prototype and demonstrate a very simple databinding method using an EntityDataSource control to access the EF, a GridView control to display the master record and a GridView control to display related data to the currently selected master record.

First lets familiarise ourselves with the entities involved:

ADO.NET Entity Data Model Wizard Completion
ADO.NET Entity Data Model Wizard Completion

For the purpose of this exercise we shall be selecting the Publisher entity and the Title entity. We shall display a grid of publishers, each with a select link that when clicked will display a list of titles that are associated to the selected publisher.

Step 1:

Create a new asp.net page.

Step 2:

In design view drag and drop 2 gridview controls, name these grv_Publishers and grv_Titles.

Adding gridview controls
Adding gridview controls

Step 3:

Drag and drop 2 EntityDataSource controls on to the page and name these eds_Publishers and eds_Titles. Configure the data source controls to use your EF as a named connection, select publishers and titles as the EntitySetNames and set them to select all values.

Adding EntityDataSource Controls
Adding EntityDataSource Controls
Configuring the EntityDataSource controls
Configuring the EntityDataSource controls
Configuring the EntityDataSource controls
Configuring the EntityDataSource controls

Step 4:

Wire up the EntityDataSource controls to their respective gridviews by click on the gridview, selecting the tasks fly out menu then using the drop down box to select the correct data source.

Step 5:

On the publishers grid view control, check the box “Enable Selections”, this will give you a selection column. You can also take this opportunity to select a design format for the grid views and edit the columns to only show the fields that you would like the user to see.

Selecting Data Source and enabling selection on grid view controls
Selecting Data Source and enabling selection on grid view controls

Step 6:

Now you are ready to link the 2 gridviews together; unfortunately, there is currently no option to do this in design view so you will have to switch to source view and enter the details manually.

  • In source view check that grv_Publishers has the propery DataKeyNames and its value is pub_id. This instructs the gridview to use the pub_id as its selected value when a user clicks the select button.
  • Locate the eds_Titles EntityDataSource and add the following attribute: Where=”it.pub_id = @pub_id”. This tells the data source to filter the titles by a parameter named pub_id.
  • Finally you need to add the pub_id parameter to the eds_Titles EntityDataSource. Inside the asp:EntityDataSource tags you need to add a child element named WhereParameters that also contains a child element named ControlParameter. The ControlParameter has attributes that will pull the selected value from the publishers gridview and pass it into the where clause inside the titles datasource. Copy the text below into your project.

<asp:EntityDataSource ID="eds_Titles" runat="server"
ConnectionString="name=PUBS_Context" DefaultContainerName="PUBS_Context"
EnableFlattening="False" EntitySetName="titles"
Where="it.pub_id = @pub_id">
	<WhereParameters>
		<asp:ControlParameter ControlID="grv_Publishers"
		DbType="String" DefaultValue="0"
		Name="pub_id" PropertyName="SelectedValue" />
	</WhereParameters>
</asp:EntityDataSource>

Save your files and run the project, you should see a grid view containing all the publishers and when you click on the select link, a list of titles for that publisher should appear underneath the grid view.

Now your basic application is working, you can take some time experimeting with enabling the options to allow paging, sorting, editing, adding and deleting records.

Download project files

Conclusion

As you will find, there is quite a lot of functionality available with the EntityDataSource control; however, you may find that for complete control and flexibility you will better served by learning how to access the EF and handling data binding in code and this is where my next post on the subject will concentrate on.

ADO.NET Entity Framework – Using the Wizard to Generate the Data Model

To get started with Entity Data Model you can generate the model using the built in wizard in Visual Studio 2010. The wizard has the ability to generate a data model from an existing database or you can opt for a model first approach and create the data model in Visual Studio, then initiate a function that will create a database based upon your model.

In this example, I’m going to demonstrate how to generate the data model from an existing database using the Microsoft Pubs database.

Step 1:

Fire up Visual Studio 2010 and create a new web site project.

Step 2:

Download and drop the PUBS.MDF database file into your projects App_Data folder. If you cannot see an App_Data folder in Solution Explorer, right click your parent project icon, select the Add -> Add ASP.NET Folder option and select App_Data from the available results.

Step 3:

Use the Server Explorer panel to set up a Data Connection and check you can access the Pubs database by drilling down through the tables and stored procedures.

Step 4:

To keep things organised, create a new folder for your model, I typically name the folder App_Data_Model so that it sits below the App_Data folder in Solution Explorer.

Step 5:

Right click your model’s folder and select Add -> New Item. Select the Data option in the Installed Templates panel. Select ADO.NET Entity Data Model and then give your model a meaningful name.

adding ado entity data model
Adding ado entity data model

Step 6:

This is where to get to choose if you would like to generate the model from an existing datbase or select an empty model to use the model first technique. In this example we are going to select the first option and click next.

Choose Model Contents - we select the Generate from database option.
Choose Model Contents – we select the Generate from database option.

Step 7:

Now we have to set up or select an existing connection to the database. Set up or select your project’s Pubs database. Make sure you tick the Save entity connect settings in Web.Config option and use a meaningful entry as this value is also used as a class name for the context.

Choose your Data Connection.
Choose your Data Connection.

Step 8:

The wizard will now attempt to load all the database objects and ask you to select the tables, views and stored procedures that you wish to include in your model. In this instance I have only selected a few tables as a demonstration. Select the check boxes for Pluralize/Singularize object names and include foreign key columns in the model this may assist you understanding of the objects in your model when it comes down to coding. Click the finish button to complete the wizard.

Choosing your Database Objects
Choosing your Database Objects

After a short delay the wizard will complete the building of your data model and it will present you with a graphical representation of your data objects and any relationships.

ADO.NET Entity Data Model Wizard Completion
ADO.NET Entity Data Model Wizard Completion

 

This completes this task, I suggest you take some time to familiarise yourself with the model, you may wish to organise the model and print it for reference, client or peer review purposes. When you are ready to move on to working with the data model, follow my next post to see how we can bind elements of the model to ASP.NET controls.

Further reading and handy resources

ADO.NET Entity Framework 4.1 (EF)

This series of posts will cover the basics of the current version of the ASP.NET data modelling framework. The Entity Framework is now Microsoft’s recommended data access technology for building new applications. EF 4.1 brings a set of powerful productivity features, including “Code First” support, DbContext API, and the ability to work with an existing database or have it created automatically from the classes within your .NET project.

The EF is new concept for traditional software developers as you no longer have to be concerned with the details of how your application’s data is going to be stored and retrieved; the EF framework does all the hard work for you. Think of it like an Object Relational Mapping (ORM) system on steroids.

Why you should consider switching to the EF?

In a nutshell, removing the data store and data retrieval coding from the equation saves you precious time, time that you can spend on the more productive aspects of your project or reduce the costs that hit your client’s budget. As a rough example, we have calculated a conservative cost saving of approximately £20,000+ per year for our 3 man development team.

So what can you do with the EF?

  1. You can create a class model of an existing database and the framework will provide pseudo classes that deal with all the select, create, update and delete data functions for you.
  2. You can create your data model in the model-first mode using the set of design tools that comes with the framework and then let the EF build the database for you.
  3. If you go down the model-first route, you can easily switch data stores without having to recode your application. There are EF compatible providers for alternative databases such as Oracle and MySQL. If you or client decides to switch databases during development you simply plug-in the relevant provider and the EF will build the database and all the queries for you.

How can this help?

The requirements gathering stage of a software project is not an exact science. From experience, it’s a very rare project that has every aspect of the project defined in advance. Often we discover that a client’s requirements are uncertain or indefinable until you get started; this may lead to many changes during the development life cycle, changes that can introduce significant delays and extra work refactoring code. Adoption of the EF makes handling changes to a project’s data storage and retrieval requirements straightforward and quicker to implement.

Getting Started

The Entity Framework 4.1 is built on top of the Entity Framework 4 included in .NET Framework 4. Therefore, to use the EF 4.1 framework you will need Visual Studio 2010, preferably with SP1 installed and you will need to download and install the latest updates to the framework from Microsoft.

Handy Links

Compatibility with MySQL

Our development team have a lot of requests to use MySQL as the data store when building custom web applications or content management systems. Thankfully, from our findings we have discovered that EF is perfectly compatible with MySQL, all you need to do is download and use the latest .NET connector from the MySQL website (at time of writing this is version 6.4.3) http://dev.mysql.com/downloads/connector/net/

Coming Next

In my next series of posts I’ll release some handy code snippets that show how to use the EF on your own web applications. If you have any specific requests then please let me know by using the comments form below.

ASP.NET Entity Framework 4 Video Links

A handy list of tutorial resources for picking up the latest version of the ASP.NET entity framework.

Getting started guide

Video on the top 10 what’s new in EF 4 (1 hour long)