Programming Against the ADO.NET Entity Framework (Cont.) Issuing Queries The previous snippets showed examples of enumerating the top-level properties in the object context class to query for all entities in a particular entity set. In your application, you will likely want to issue other types of queries as well. The ADO.NET Entity Framework’s Object Services layer supports this via the ObjectQuery<T> class. The following snippet shows ObjectQuery<T> used to query for all event entities that are of the sub-type, RunEvent. Here, the query is expressed using Entity SQL. // Object Query
ObjectQuery<Event> runEventsQuery =
entities.CreateQuery<Event>(@"
SELECT VALUE e
FROM TriEventsEntities.Events AS e
WHERE e IS OF (ONLY TriEventsModel.RunEvent)");
foreach (Event e in runEventsQuery)
{
Console.WriteLine("Event Name: {0}",
e.EventName);
}
The code calls the CreateQuery<T>() method on the object context class to obtain an ObjectQuery<T> object. CreateQuery<T>() takes a string representation of the query. This example illustrates the use of the IS OF operator in Entity SQL, which filters entities based on type. When you execute the query, the ADO.NET Entity Framework translates the Entity SQL query representation into a query in terms of the database schema. To do this, the ADO.NET Entity Framework consults the mapping information in the mapping files referenced in the connection string. The Object Services layer materializes results of the query as objects of the Event class. Entity SQL provides powerful query capabilities. Because it is a text-based language, it lends itself well to scenarios in which you construct queries dynamically. For more information on Entity SQL, see the MSDN reference documentation. As an alternative to Entity SQL, some developers may choose to express queries with LINQ. LINQ offers the advantages of strong compile-time checking and IntelliSense support in Visual Studio. The ADO.NET Entity Framework supports LINQ over its Object Services layer, as illustrated in the following snippet: // LINQ Query
var longEventsQuery =
from e in entities.Events
where e.StartDate != e.EndDate
select e;
foreach (Event e in longEventsQuery)
{
Console.WriteLine("Event Name: {0}",
e.EventName);
}
Here I use LINQ to formulate a query for events whose end dates are different than their start dates. Notice that I expressed the query in native C# language constructs and it is not enclosed in quotes. Had this query contained a syntactic error, the compiler would have caught it at compile time. For more information on LINQ, see Elisa Flasko’s article LINQ to Relational Data, also in this issue of CoDe Focus. As with the Entity SQL example, the ADO.NET Entity Framework translates this query into the appropriate form for sending to the database. The database evaluates the query and the Object Services layer materializes the results as objects. Performing Inserts, Updates, and Deletes So far, all the examples have been read operations. The ADO.NET Entity Framework’s Object Services layer also supports the inserting, updating, and deleting entities. The following code snippet shows how you can create a new RunEvent entity, along with two related Participants: // Insert new entities and relationships
RunEvent runEvent = new RunEvent();
runEvent.EventName = "Seattle Marathon";
runEvent.StartLocation = "Memorial Stadium";
runEvent.StartDate = new DateTime(2007, 11, 25);
runEvent.EndDate = new DateTime(2007, 11, 25);
runEvent.Distance = 26.2M;
runEvent.ElevationGain = 4000;
entities.AddToEvents(runEvent);
Participant p1 = new Participant();
p1.ParticipantName = "Colin";
entities.AddToParticipants(p1);
Volunteer v1 = new Volunteer();
v1.ParticipantName = "Carl";
entities.AddToParticipants(v1);
runEvent.Participants.Add(p1);
runEvent.Participants.Add(v1);
entities.SaveChanges();
Simply create a new instance of the RunEvent class and set the properties. In this model, the database server generates the EventID property, so it is not necessary to set it here. After creating the RunEvent object, add it to the list of objects that the object context is tracking by calling the AddToEvents() method. The generated object context class has methods for adding objects to each entity set, named AddTo[EntitySetName](). By calling AddToEvents(), you make the object context aware of the new Event entity for the purposes of change tracking and communicating with the database. In addition to creating the RunEvent object, the code also creates two Participant objects in much the same way. One of the participants is a volunteer, so it creates an instance of the Volunteer derived class. For both objects, the code calls the context’s AddToParticipants() method to include them in the set of objects the context is tracking. Finally, the code adds the participant objects to the Participants collection on the RunEvent object. This establishes the relationship between the event and the participants. At this point the new objects and their relationships only exist in memory. To persist them, you call the SaveChanges() method on the context. This method propagates all changes it has tracked to the database. The ADO.NET Entity Framework’s mapping engine determines the appropriate database tables to modify as a result of the operations you performed with the objects. In this example, the operations add rows to the tables that store information about events and participants, as well as the link table used to relate the two. Though this example just showed inserting new objects, you can perform updates and deletes in much the same way. For updates, the code simply modifies the properties of previously read objects. The object context will keep track of the changes and persist them when you call SaveChanges(). For deleting entities, the object context provides a DeleteObject() method, which you can use to mark an object as deleted. Again, when you call SaveChanges(), the ADO.NET Entity Framework propagates the delete operation to the database. Programming with EntityClient By design, programming with EntityClient follows the same patterns familiar to ADO.NET 2.0 developers. You use a connection object to establish a connection to a model, a command object to specify a query, and a data reader object to retrieve query results. EntityClient represents the results as data records, shaped according to the Entity Data Model. Unlike Object Services, the EntityClient layer supports only Entity SQL as a query language. There is no LINQ support at this layer because there are no strongly-typed objects over which queries could be expressed. Also, EntityClient does not support write operations. To perform inserts, updates, or deletes, you must use the Object Services layer. Despite these restrictions, EntityClient is a remarkably useful and efficient way to program against entities. This section looks at some code examples that illustrate EntityClient features. Basic Querying with EntityClient The following code snippet shows EntityClient used to retrieve all Event entities of type RunEvent (notice that this is the same Entity SQL query used in an earlier Object Services example-EntityClient supports exactly the same Entity SQL syntax): using (EntityConnection conn = new
EntityConnection("name=TriEventsEntities"))
{
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT VALUE e
FROM TriEventsEntities.Events AS e
WHERE e IS OF (ONLY TriEventsModel.RunEvent)";
// Use data reader to get values
using (DbDataReader reader =
cmd.ExecuteReader(
CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
Console.WriteLine(
"Event Name: {0}",
(string)reader["EventName"]);
}
}
}
The first line creates a new EntityConnection within a using block (this ensures the connection is disposed properly). The EntityConnection constructor takes a connection string. Here I’ve used the same named connection string used under the hood in the preceding Object Services examples. Alternatively, you could have specified any other connection string, as long as it consisted of the three parts described earlier. After creating the connection object, the code calls the Open() method to open the connection. Next it creates an EntityCommand object associated with this connection by calling the connection’s CreateCommand() method. Then it sets the command text to an Entity SQL string. Finally, it calls ExecuteReader() to execute the query and obtain a data reader to iterate over the results. This part of the code should look identical to results processing code you may have written against ADO.NET 2.0 providers. It uses a while loop to read each record and prints the value of one of its fields. EntityClient uses the same mapping engine as the rest of the ADO.NET Entity Framework for performing query translation. The engine translates the query written here into database terms and reassembles the results into the shapes specified by the Entity Data Model. Because EntityClient does not materialize objects for the results, the overhead incurred is lower than in the Object Services layer. Accessing Metadata from EntityClient While the results that come back from EntityClient queries may appear similar to those returned by other data providers, there is one important difference: EntityClient records come with rich metadata that describes the entity types that define the record structure. You can use this to your advantage when writing code that processes the result data. Notice in the previous example that you had to hardcode the name of the field (“EventName”) that you wanted to access in the result record. If you wanted to build a more dynamic system that printed all result fields without knowing them in advance, you could use metadata to do so, as the following example shows: // Use metadata to drive result retrieval
using (DbDataReader reader =
cmd.ExecuteReader(
CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
IExtendedDataRecord extRecord =
reader as IExtendedDataRecord;
EntityType recordType =
extRecord.DataRecordInfo.RecordType.EdmType
as EntityType;
foreach (EdmMember member in
recordType.Members)
{
if (member is EdmProperty)
{
Console.WriteLine("{0}: {1}",
member.Name,
reader[member.Name]);
}
}
}
}
The code in this snippet could go in place of the results processing code in the previous snippet. Within the while loop used to iterate over the result records, you cast the reader to the IExtendedDataRecord interface. This is a new interface, introduced in the ADO.NET Entity Framework, and provides access to the metadata associated with the results. From the IExtendedDataRecord you can navigate to the EdmType property to determine the entity type for each record. The ADO.NET Entity Framework represents the entity type with an instance of EntityType, a class in the ADO.NET Entity Framework’s Metadata system (documented fully in MSDN). Given the EntityType object, the code iterates over the type’s members and prints the name and value of each one. You can obtain the values by indexing into the data record using the member name. The if-statement that checks whether the member is of type EdmProperty is necessary to filter out navigation properties, which do not appear in the result records. In a real application you could use the metadata returned with EntityClient records to build dynamic UI or to provide more context for the meaning of the results. Though I did not show it in this article, the same metadata is also available at the Object Services layer. Wrapping Up This article provides just a brief overview of the features in the ADO.NET Entity Framework’s programming surface. The ADO.NET Entity Framework’s documentation and samples describe the features covered here (and several more I did not cover) in greater detail. The ADO.NET Entity Framework team encourages you to try the Beta 2 release and share your feedback. Shyam Pather |