Entity Framework

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.

 

Published by

Mark Taylor

Web developer, based in Bourne, Lincolnshire, UK. builder of web applications, ASP.NET, Web Standards HTML, CSS & JavaScript. SQL Server & MySQL DB developer.

Leave a Reply

Your email address will not be published. Required fields are marked *