The ADO.NET Entity Framework raises the level of abstraction at which developers work with data.
Rather than coding against rows and columns, the ADO.NET Entity Framework allows you to define a higher-level Entity Data Model over your relational data, and then program in terms of this model. You get to deal with your data in the shapes that make sense for your application and those shapes are expressed in a richer vocabulary that include concepts like inheritance, complex types, and explicit relationships.
In his article, An Entity Data Model for Relational Data (parts I & II), Mike Pizzo explains the details of the Entity Data Model and how to map a model to a database. In this article, I discuss the capabilities the ADO.NET Entity Framework provides for programming against a model after it is defined. The material in this article is intended as an overview that covers the breadth of features available to developers. Each of these features is a topic unto itself and is covered in more detail in the MSDN documentation that accompanies the ADO.NET Entity Framework.
Overview of the ADO.NET Entity Framework
The ADO.NET Entity Framework is built in a layered architecture on top of existing ADO.NET 2.0 data providers. Figure 1 illustrates this layering.
The ADO.NET 2.0 Data Providers offer a common programming model for accessing disparate data sources. Whether you are programming against a Microsoft SQL Server database or an Oracle database, you use the same fundamental programming abstractions: a connection object, a command object, and a data reader object.
The ADO.NET Entity Framework introduces a new data provider called EntityClient; this is the first layer above the ADO.NET 2.0 data providers. EntityClient offers the same programming abstractions as the other data providers-connections, commands, and data readers-but adds mapping capabilities that translate queries expressed in terms of the model into the equivalent queries in terms of the tables in the databases. To complement these capabilities, the ADO.NET Entity Framework introduces an extended query language called Entity SQL (E-SQL), which augments traditional SQL with constructs necessary for querying in terms of the higher-level modeling concepts in Entity Data Models (inheritance, complex types, and explicit relationships).
Above the EntityClient layer, the ADO.NET Entity Framework includes an Object Services layer, which provides a programming model in terms of strongly-typed objects. At the Object Services layer, entities are represented as objects-instances of data classes that are mapped to the entity types in the model. The Object Services layer supports querying with both Entity SQL and Language Integrated Query (LINQ). Query results are manifested as objects whose properties can be read and set by application code. Object Services keeps track of changes made to the objects and can propagate these back to the database. The ADO.NET Entity Framework’s mapping engine performs the translation between changes to the entity objects and operations on the underlying database tables.
The Object Services layer supports querying with both Entity SQL and Language Integrated Query (LINQ).
Developers can choose to program against either the EntityClient layer or the Object Services layer. This choice provides flexibility for different application scenarios. In cases where the application does not require objects, the EntityClient layer provides a lower-level entry point that offers the benefits of entities without the overhead of object materialization. When you require the convenience and change-tracking capabilities of objects, you can choose to work at the Object Services layer. In either case, an underlying ADO.NET data provider performs database operations and compensates for the differences between database systems. Thus, the ADO.NET Entity Framework exposes exactly the same programming model and query syntax, regardless of the particular DBMS in which the data is stored.
In this article, I’ll approach the ADO.NET Entity Framework top-down: First I’ll show some code snippets that illustrate programming at the Object Services layer. Then, I’ll delve into some examples that show EntityClient in action.
A Sample Entity Data Model
For the purpose of illustration in this article, I’ll use the sample data model shown in Figure 2. This model represents various types of athletic events, like bike races, running events, and triathlons. There are two base types in this model: Event, which encapsulates the attributes common to all athletic events, and Participant, which represents a participant in one or more events. The model declares a many-to-many relationship between Events and Participants called Event_Participant.
Both Event and Participant have sub-types. Event is the parent for sub-types BikeEvent, RunEvent, SwimEvent, and TriathlonEvent. Each of these sub-types augments the base type with new properties. Participant has a sub-type called Volunteer: This is used to represent participants who volunteer, as opposed to compete, in the events. Note that Volunteer does not add any new fields to its base type, Participant.
In cases where the application does not require objects, the EntityClient layer provides a lower-level entry point that offers the benefits of entities without the overhead of object materialization.
The model also declares an entity container called TriEventsEntities, which contains entity sets for Events and Participants and an association set for the Event_Participant association. Figure 2 does not show the entity container, but it is a fundamental part of the model.
The ADO.NET Entity Framework supports several ways to map a model like this one to relational tables. I happened to use a Table-per-Type (TPT) mapping (a table for each base type and a table for each sub-type that stores just the additional properties declared by the sub-type). For the purposes of this article, the mapping strategy is irrelevant: the ADO.NET Entity Framework supports the same programming patterns regardless of how you map the model.
Programming with Object Services
To program against the model with Object Services, you need data classes that correspond to the types declared in the model. The ADO.NET Entity Framework includes tools that can generate these classes from the model definition. Alternatively, you can code these classes by hand (doing so requires you to implement a specific interface mandated by the ADO.NET Entity Framework). I’ve based the code snippets shown in this article on classes generated by the ADO.NET Entity Framework’s tools.
Here is a code snippet that enumerates entities in the model:
using (TriEventsEntities entities =
new TriEventsEntities())
{
// Enumerate entities
foreach (Event e in entities.Events)
{
Console.WriteLine("Event Name: {0}",
e.EventName);
}
}
The preceding snippet is all that’s required to connect to the model and enumerate the Event entities within it. Dissecting this code highlights several important aspects of working with the ADO.NET Entity Framework.
The first line of code creates an instance of the TriEventsEntities class. The ADO.NET Entity Framework tools generated this class and it corresponds to the entity container declared in the model. It inherits from the Object Services base class, ObjectContext, and you can loosely refer to it as the “object context” for the model. You can think of this class as a starting point for interaction with the model-It encapsulates both a connection to the database and the in-memory state required to do change tracking and identity resolution for the objects that result from queries. It is instantiated within a using block, which ensures that it is disposed of properly at the end of the code.
Within the using block, the foreach loop iterates over objects in the Events property in the object context. This property represents the contents of the Events entity set (the generated object context class has a property for each entity set in the container). Iteration over the Events property sends a query for all Events to the database. The ADO.NET Entity Framework materializes the results of this query into objects of the generated Events class. Within the foreach loop, the code simply prints the value of one of the entity’s properties. You could easily imagine more sophisticated processing logic here.
At this point, you may be wondering how the ADO.NET Entity Framework establishes the connection to the database because the code snippet does not include any connection information. The default constructor generated on the object context class, TriEventsEntities, looks for a named connection string in the App.config file with the same name. The ADO.NET Entity Framework tools conveniently create the TriEventsEntities connection string in the App.config file when generating the class. This connection string will look something like the following:
metadata=.\TriEvents.csdl|.\TriEvents.ssdl|.
\TriEvents.msl;provider=System.Data.SqlClient;
provider connection string="Data
Source=.\SqlExpress;
Initial Catalog=TriEvents;
Integrated Security=True;
MultipleActiveResultSets=true"
You can think of this connection string as consisting of three parts. The first part specifies a list of metadata files; these files define the model, the database schema, and the mapping between the two in a format defined by the ADO.NET Entity Framework. The second part of the connection string specifies which ADO.NET data provider to use to connect to the database. This example specifies the SqlClient data provider. The final part of the connection string is really another connection string: the one the data provider uses to connect to the database. The ADO.NET Entity Framework uses the three parts of the connection string to establish a connection to a model: it uses the metadata files to load the model and mapping information, it uses the data provider name to instantiate the data provider, and it uses the provider’s connection string to connect to the database.
Navigating Relationships
The Entity Data Model contains an explicit relationship between Events and Participants. The Event and Participant types have navigation properties that traverse this relationship and allow you to find entities on one end of the relationship when you are holding a reference to the other end. For example, given an Event object, I can use the Participants navigation property to find the related Participant entities. The following code snippet shows how (in this and subsequent code snippets, the code shown goes within the using block declared in the previous code snippet; the using block is omitted here for brevity):
// Navigate relationships explicitly
foreach (Event e in entities.Events)
{
Console.WriteLine("Event Name: {0}",
e.EventName);
e.Participants.Load();
foreach (Participant p in e.Participants)
{
Console.WriteLine("\tParticipant:{0}",
p.ParticipantName);
}
}
This code navigates through the events and prints the related participants for each one. As in the previous example, the foreach loop that iterates over the Events results in a database query for all events. By default, when issuing such a query, the ADO.NET Entity Framework does not pull in related entities (doing so would add overhead that is unnecessary, unless you are going to access those related entities). Therefore, to obtain the related Participant objects, the code makes a call to the Load() method on the Participants collection on each Event entity. This issues a separate query to the database to retrieve the participants associated with each event. Thereafter, you can enumerate the participants with a foreach loop.
If you know you will need to pull in related entities, you can take advantage of an Object Services feature called Relationship Span and avoid having to issue separate queries. The following code snippet illustrates how to retrieve Event entities and the related Participants at the same time:
// Relationship span
foreach (Event e in
entities.Events.Include("Participants"))
{
Console.WriteLine("Event Name: {0}",
e.EventName);
foreach (Participant p in e.Participants)
{
Console.WriteLine("\tParticipant:{0}",
p.ParticipantName);
}
}
In the outer foreach loop, the code calls the Include() method to specify which related entities the query should retrieve. The Include() method takes a text representation of a path across one or more navigation properties (here you reference the “Participants” navigation property). In models that have several levels of relationships (for example, Categories related to Products related to Suppliers), you can use the Include method to traverse more than one level. Because the code called the Include() method in the outer enumeration, it is not necessary to call the Load() method before enumerating the Participants on each Event.
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.
