ADO.Entity Framework: Stored Procedure Customization
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.:
// EF example
// EFExtensions example
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:
var results = context
.CreateStoreCommand("GetCategories", CommandType.StoredProcedure)
.Materialize<Category>()
.Bind(categorySet);
Store commands
You can reuse the connection on an ObjectContext to create a store command as follows:
using (MyContext context = new MyContext())
{
// In V1 of the EF, the context connection is always an EntityConnection
EntityConnection entityConnection = (EntityConnection)context.Connection;
// The EntityConnection exposes the underlying store connection
DbConnection storeConnection = entityConnection.StoreConnection;
DbCommand command = storeConnection.CreateCommand();
command.CommandText = "GetCategory";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("cid", 1));
}
First you’ll need to add the library namespace to your sources files in order to use the extension methods provided by this library:
using Microsoft.Data.Extensions;
Now you can use the CreateStoreCommand method which extends ObjectContext and packages the code shown above:
using (MyContext context = new MyContext())
{
DbCommand command = context.CreateStoreCommand("GetCategory",
CommandType.StoredProcedure, new SqlParameter("cid", 1));
}
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:
bool openingConnection = command.Connection.State == ConnectionState.Closed;
if (openingConnection) { command.Connection.Open(); }
int result;
try
{
result = command.ExecuteNonQuery();
}
finally
{
if (openingConnection && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); }
}
I can write
int result;
using (context.Connection.CreateConnectionScope())
{
result = command.ExecuteNonQuery();
}
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:
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int categoryIDOrdinal = reader.GetOrdinal("cid");
int nameOrdinal = reader.GetOrdinal("name");
yield return new Category
{
CategoryID = reader.GetInt32(categoryIDOrdinal),
Name = reader.IsDBNull(nameOrdinal) ? (string)null : reader.GetString(nameOrdinal),
};
}
}
EFExtensions includes a Materializer class and several Materialize extension method overloads that make this a little bit easier.
command.Materialize(r =>
new Category {
CategoryID = r.Field<int>("cid"),
Name = r.Field<string>("name"),
});
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:
context.CreateStoreCommand("select cid as CategoryID, [name] as [Name] from dbo.Categories")
.Materialize<Category>();
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:
// Materializer with column renames.
private static readonly Materializer<Category> s_categoryMaterializer = new Materializer<Category>(r =>
new Category
{
CategoryID = r.Field<int>("cid"),
Name = r.Field<string>("name"),
});
// Materializer returning different types based on a condition.
private static readonly Materializer<Product> s_productMaterializer = new Materializer<Product>(r =>
r.Field<DateTime?>("discontinued_date").HasValue ?
(Product)new DiscontinuedProduct
{
ProductID = r.Field<int>("pid"),
Name = r.Field<string>("name"),
DiscontinuedDate = r.Field<DateTime>("discontinued_date")
} :
new Product
{
ProductID = r.Field<int>("pid"),
Name = r.Field<string>("name")
});
Tracking results
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
Category category = s_categoryMaterializer.Materialize(command).SingleOrDefault();
// Create a category set
EntitySet<Category> categorySet = new EntitySet<Category>(context, "categories");
// Track command result
category = categorySet.FindOrAttach(category);
An EntitySet<T> similar toan 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:
IEnumerable<Product> products = s_productMaterializer.Materialize(command).Bind(productSet);
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.
public partial class MyObjectContext : ObjectContext
{
private EntitySet<Product> productSet;
/// <summary>
/// Gets alternative entry point for the Products entity set.
/// </summary>
public EntitySet<Product> ProductSet
{
get
{
if (null == this.productSet)
{
this.productSet = new EntitySet<Product>(this, "Products");
}
return this.productSet;
}
}
…
}
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.
public Category GetCategoryAndRelatedProducts(int categoryID)
{
DbCommand command = this.CreateStoreCommand("GetCategoryAndProducts", CommandType.StoredProcedure, new SqlParameter("cid", categoryID));
Category category;
using (command.Connection.CreateConnectionScope())
using (DbDataReader reader = command.ExecuteReader())
{
// first result set includes the category
category = s_categoryMaterializer
.Materialize(reader)
.Bind(this.CategorySet)
.SingleOrDefault();
// second result set includes the related products
if (null != category && reader.NextResult())
{
category.Products.Attach(s_productMaterializer
.Materialize(reader)
.Bind(this.ProductSet));
}
}
return category;
}
Expect more posts on the EFExtensions library over the months leading up to the release of the ADO.NET Entity Framework V1!
Comments
Anonymous
March 26, 2008
Colin is a super smart colleague of mine, who works as a developer on the Entity Framework. So it isAnonymous
March 27, 2008
When I announced the start of the Entity Framework Toolkits & Extensions section in CodeGallery ,Anonymous
April 25, 2008
This week at DevConnections in Orlando, I gave a “deep-dive” talk on LINQ. I wanted to give people aAnonymous
May 13, 2008
A beta of Visual Studio 2008 SP1 was released on Monday and the ADO.NET Entity Framework (EF) is nowAnonymous
August 08, 2008
The comment has been removedAnonymous
August 13, 2008
Hello, I'm looking at Entity Framework Extensions for building my Data Access Layer as it seems promising with the requirement I have. My requirement is to use stored procedures (for get, add, update, delete) and allow EF to handle concurrency. I'm not sure if this is possible. Firstly, I have created a data object from the EF model and removed all its table mapping. I have mapped my stored procedures to the Insert, Update and Delete mappings of the data object. For the list, "get" stored procedure from the EF using CreateStoreCommand, and materializing it to data objects (using EF Extenstions). Now I want to attach it to the objectcontext to allow EF to handle concurrency, which means If i bind this context to the grid, the changes made to the grid should be saved (using the mapped stored procedures). Here the problem is, when I try to attach the objects to the object context, it dosen't seem to do the job, i end up with no objects in the objectcontext. I use the objectContext.AttachTo(<<EntitySet name>>, <DataObject>) Please advice, any help on this would be greatly appriciated. Thanks, AlAnonymous
December 02, 2008
LINQ to SQL supports a wide range of scenarios that involve stored procedure mapping and usage. ThisAnonymous
December 07, 2008
A user of the EFExtensions library asked me how to retrieve a set of ‘categories’ and their related ‘products’Anonymous
February 04, 2009
You could find EF stored procedure here http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.entryAnonymous
June 09, 2009
This week at DevConnections in Orlando, I gave a “deep-dive” talk on LINQ. I wanted to giveAnonymous
June 09, 2009
A user of the EFExtensions library asked me how to retrieve a set of ‘categories’ and theirAnonymous
October 12, 2011
Is there anyway to get output parameters using the Materializer?Anonymous
June 24, 2012
Hi Colin, Can please help me to call StoredProcedure through GenericRepository. As I am able to call it with the help of context object.