RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Introducing ADO.NET Entity Framework : Page 6

The ADO.NET Entity Framework is core technology in Microsoft's evolving data platform that bridges the gap between data structures and objects in your applications.

Querying the Data Through the ADO.NET Entity Framework
In most cases, you probably do not want to retrieve the entire contents of a database table as the previous examples did. While the ADO.NET Entity Framework does allow you to leverage existing stored procedures and views, there are a number of other ways to query data through the ADO.NET Entity Framework.

When you write queries against the entities in your Entity Data Model, the ADO.NET Entity Framework's ObjectContext will translate the queries into the query language of the data store (e.g., T-SQL) and execute the query. When the data store returns the data to the ObjectContext, the ObjectContext will then take the tabular data, convert it into the objects representing the entities, and pass those populated entities back to you.

Methods of Querying Entities
There are three ways to query the Entity Data Model:

Figure 6: Entity Framework allows a number of paths to get data returned in the format that works best for your application's needs.
  • EntityClient Provider using the Entity SQL Query syntax.
  • Object Services' ObjectQuery using Entity SQL query syntax.
  • LINQ to Entities.
Depending on how you use the above methods, the query will return different data types such as DataReaders, entities, or even anonymous types. Figure 6 provides a map of the type of data returned by the various methods of querying.

This introductory article provides a very high-level view of each querying method, along with a few examples, but Part II of this article will dig into querying entities in more depth.

EntityClient Provider
EntityClient follows the pattern of the other data providers such as SQLClient, OledbClient, and OracleClient. Like the other clients, you first create a connection, but instead of connecting to the physical data store, you make the connection to the EDM. Then you create a command using a syntax devised for querying entities that is very similar to T-SQL. This querying syntax is called Entity SQL. Lastly you would execute the command. Again, like the other clients, the result is a DbDataReader.

EntityClient is not only familiar, but allows you to plug the ADO.NET Entity Framework into existing solutions that already return a DbDataReader (such as a SqlDataReader). Additionally, if you want the benefits of the EDM, but the performance of a DataReader, EntityClient gives you the best of both worlds.

Here's a simple example of using EntityClient to query Entity Data Models. This example asks for a particular column, rather than an entire Category entity:

   Using con As New _
      Dim cmd As New EntityCommand( _
         "SELECT c.CategoryName " & _
         "FROM NorthwindEntities.Categories AS c " & _
         "ORDER BY c.CategoryName", con)
      Dim r As DbDataReader = cmd.ExecuteReader( _
   End Using
Much of the ADO.NET Entity Framework's functionality is provided through Object Services, which is in the API System.Data.Objects. Using the ObjectServices CreateQuery method, you can pass in an Entity SQL query and return a set of Entity Objects:

   Dim custs As ObjectQuery(Of Customers) _
      = NWEntities.CreateQuery(Of Customers) _
      ("SELECT VALUE c FROM Customers AS c " & _
       "WHERE c.Country='Spain'")
The VALUE keyword in the query forces objects to be returned; in this case it will be a list of Customer objects. If the VALUE keyword were not used, the result would be rows and columns. You can then use the resulting ObjectQuery for data binding or enumerate it through to perform additional functions on the returned Customer entities.

Because the Entity Model is aware of relationships, you can also build queries that drill into related data. Here's an example that filters on related data:

   Dim ord = NWEntities.CreateQuery(Of Orders) _
      ("SELECT VALUE o FROM orders as o " & _
       "WHERE o.Customers.Country='Spain'")
This example projects into related data. The preceding query returns DbDataRecords because you have not defined a class to match the schema of CompanyName, OrderData, and ShipCity:

   Dim o = NWEntities.CreateQuery(Of DbDataRecord) _
      ("SELECT o.Customers.CompanyName,o.OrderDate," & _
      "o.ShipCity FROM orders as o " & _
      "WHERE o.Customers.Country='Spain'")
Querying into child collections (such as OrderDetails collection in an Order) requires a bit more work and serves to remind you of Entity SQL's foundation—T-SQL:

   Dim custs = NWEntities.CreateQuery(Of Customers) _
     ("SELECT VALUE c FROM Customers as c " & _
      "WHERE c.CustomerID IN " & _
      "(SELECT VALUE o.Customers.CustomerID " & _
         "FROM Orders as o " & _
         "WHERE o.OrderDate > " & _
         "DATETIME'2007-07-01 00:00:00')")

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date