The ADO.NET Entity Framework (EF) allows you to map stored procedures to functions that return typed results in the conceptual model. However, when stored procedures results don’t match the patterns supported by the EF, reading and tracking results is quite difficult. Fortunately, it is possible to run arbitrary stored procedures and Transact-SQL commands through the ObjectContext. This post describes these facilities and leverages a new utility library recently posted on Code Gallery (EFExtensions) to make the job much easier. In most cases, I will show the coding patterns directly against the EF and then illustrate the same behavior using EFExtensions, e.g.:
The following patterns are discussed:
· Creating and executing store commands.
· Materializing typed results.
· Tracking results.
· Multiple result sets.
There’s lots of code between here and the end, so as a motivation I’ll start by showing you what all of these extensions methods working in concert can do. Without the EFExtensions library the code to call a stored procedure in the database (including properly handling connection lifetime), create a set of objects and identity resolve them against the context would be surprisingly large. With EFExtensions, that code is reduced to:
You can reuse the connection on an ObjectContext to create a store command as follows:
First you’ll need to add the library namespace to your sources files in order to use the extension methods provided by this library:
Now you can use the CreateStoreCommand method which extends ObjectContext and packages the code shown above:
The ObjectContext does a nice job of managing the store connection for you when you run a query. If the connection is not already open, it will be opened for the duration of the query. I’ve included a general purpose extension method called CreateConnectionScope on DbConnection to support the same behavior, so instead of writing:
I can write
Alazel Acheson, a developer on the ADO.NET team, wrote a more extensive connection scope utility that you can find here.
Materializing typed results
Given a store command, you can manually produce typed results, which EF team calls “result materialization”: turning relational records we get from the store into “real” objects!
Here’s some code that takes results from the command we created in the last section and uses them to populate Category instances:
EFExtensions includes a Materializer class and several Materialize extension method overloads that make this a little bit easier.
Internally, all Materialize overloads taking commands leverage CreateConnectionScope and handle the lifetime of the reader. Materialize overloads optionally take a “shaper” delegate (as shown in the above example) which maps from IDataRecord to the result type. Note the Field method overload as well which handles the magic around type conversion and DBNull handling from data records (similar to the LINQ to DataSet method).
When no materialization delegate is specified, we generate a default shaper which assigns column values to public writable properties of the result type of the same name. In the following example, the store command explicitly projects two columns with the names of CLR properties on the Category type:
Why are shapers provided as Expression<Func<IDataRecord, T>> rather than just Func<IDataRecord, T>? We plan on exploiting the expression representation of the shaper in a future release of EFExtensions (details to follow).
While the Materialize extension methods on DbCommand and DbDataReader are convenient, it is more efficient to create a single Materializer instance and reuse it. Here are a couple of materializers that I will reuse in the rest of this post:
Using the techniques I’ve described so far, you can retrieve arbitrary CLR objects from store commands, including entities. Entities aren’t very useful if they aren’t tracked however – you cannot modify them or delete them using the context’s state manager. To facilitate tracking, EFExtensions includes an EntitySet class which manages activities related to Entity Data Model (EDM) entity sets. The class includes a FindOrAttach method which either attaches the given entity to the state manager for tracking purposes or returns an existing entity with the same key. This simulates the identity resolution behavior that the EF provides when running queries.
In the following example, we run a command returning a single category and track it
An EntitySet<T> similar to an ObjectQuery<T> in the EF but it is bound to a specific EDM EntitySet instance (which you can examine by looking at the EntitySet<T>.Metadata property), and encapsulates various services related to the set. I will cover this class in greater detail in a future post.
For convenience, a Bind extension method that attaches a sequence of entities is also included. This makes it easy to stream results, as in the following example:
I recommend creating EntitySet properties on your ObjectContext partial class definition. These properties can also be used as roots for LINQ and Entity-SQL queries.
Multiple result sets
I’ll give one final example that ties together many of the concepts described in this post. Assume we have a stored procedure returning two result sets. The first result set includes a Category instance and the second result set includes all related Products. For convenience, I’ll define a method on my object context that takes a category ID, and returns that category with all related products attached. We’ve already defined the necessary materializers (s_categoryMaterializer and s_productMaterializer), as well as our entity set properties (ProductSet and CategorySet). Note that we leverage an existing EF method, EntityCollection.Attach, to associate the returned category with the related products.
Expect more posts on the EFExtensions library over the months leading up to the release of the ADO.NET Entity Framework V1!