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 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( _
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 " & _
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 " & _
This example projects into related data. The preceding query returns DbDataRecords because you have not defined a class to match the schema of CompanyName
, and ShipCity
Dim o = NWEntities.CreateQuery(Of DbDataRecord) _
("SELECT o.Customers.CompanyName,o.OrderDate," & _
"o.ShipCity FROM orders as o " & _
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 > " & _