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.

Add a Default Value to a Databound DropDownList

3 ways to add a default value to a databound ASP.NET dropdownlist control

When you directly bind an asp.net dropdownlist to a data source you lose any list items that already exist. As a general rule of thumb most web applications that use dropdownlists have a default list item such as “Please Select”.

ASP.NET offers a number of ways to either overrule this behaviour or programatically add your default list items at runtime. Here are my 3 favourite techniques.

1. Append Databound Items Option

The dropdownlist control has an attribute named AppendDataBoundItems, set this to true and .NET will add the new items without removing any existing items. The only drawback with this method is that you need to be careful if you have some business logic that rebinds the control, you could easily end up with duplicate list items.

<asp:DropDownList 
ID="ddl_MyDropDownList" 
AppendDataBoundItems="true" 
runat="server">
        <asp:ListItem Value="" Text="Please Select" Selected="True" /> 
</asp:DropDownList>

 

2. Items Collection Add Command

In your business logic you can programatically access the dropdownlist’s items collection and add a new list item. You must do this after the item has been databound, otherwise it will be lost. The pre-render event is a likely candidate for this type of control manipulation.

ddl_MyDropDownList.Items.Add( _
New ListItem("Please Select", String.Empty))

 

3. Items Collection Insert Command

In your business logic you can programatically access the dropdownlist’s items collection and insert an item at a given index. This is far more useful than the “Items.Add” method. As you will see in the code below, we can simply insert an item at index 0 this will ensure that the new list item will appear at the top of the list. As a further measure I’ve set the new item’s selected property to True, this is entirely optional.

ddl_MyDropDownList.Items.Insert( _
0, New ListItem("Please Select", String.Empty))
ddl_MyDropDownList.Items.Item(0).Selected = True