ADO.NET Entity framework advanced scenarios: Working with stored procedures that return multiple resultsets
Today I got an interesting question about stored procedures that return multiple resultsets in combination with ADO.NET entity framework. During the CTP phase this was broken at some point and the previous version of the entity framework didn’t support it at all. But as it turns out, it’s supported now and a pretty cool feature if you ask me.
In this post I will show you how to use stored procedures with multiple resultsets to return a full object graph. I will also show you some of the pitfalls that you may encounter with this scenario.
Overview of the scenario
The scenario for this post is the database model of the recipe browser that you can find on CodePlex. It’s a sample application written in Silverlight with RIA services as a backend. The database contains recipes, that has multiple ingredients. Each of the ingredients can be expressed as a “thing” from which you need an x amount in the recipe. The table structure that I will be talking about in this post is shown below.
In this scenario I will be showing you a stored procedure that returns a single recipe with its ingredients, units and the categories the recipe is part of.
Creating the stored procedure
The stored procedure used for the scenario is a stored procedure that retrieves a single recipe with the ingredients that are part of the recipe. It also includes the units for each of ingredients used.
For the ADO.NET entity framework to be able to work with the stored procedure, you need to follow some specific rules. Each of the design rules is discussed in the following sections of this post.
Include all columns
When you select an entity from the database you are required to include all columns mapped in the store model part of your entity model. If you don’t map all the columns you will get the following error:
The data reader is incompatible with the specified ‘RecipeBrowserModel.Recipe’. A member of the type, ‘RecipeId’, does not have a corresponding column in the data reader with the same name.
Follow the object graph to fill the children
The second important design rule that you need to keep in mind is to follow a specific pattern to map a specific entity with its children. Although it’s not required to follow this rule, it’s a makes it easier to debug a specific piece of code when you follow a specific pattern.
When mapping an object, you need to start at the root of the object graph and work your way down. At the moment, ADO.NET entity framework doesn’t support mapping a whole object graph, so the mapping is all manual code. To map the object graph in the the example, you will need to follow the pattern displayed in the diagram below. As you can see it’ follows a depth-first approach for mapping the recipe object graph.
The red dots in the diagram show the order in which they are returned by the stored procedure. This order is important later, when you’re going to create the stored procedure mapping as you will need to load the resultsets from the database in that exact order.
The completed stored procedure
In the end, the stored procedure used to select a recipe with its ingredients and categories isn’t all that complicated. The complete stored procedure can be found below.
1: CREATE PROCEDURE SelectRecipeWithIngredients
2: @RecipeId decimal
3: AS
4: BEGIN
5: SET NOCOUNT ON;
6:
7: -- Select the recipe
8: SELECT Recipe.*
9: FROM Recipe
10: WHERE Recipe.RecipeId = @RecipeId
11:
12: -- Select the categories themselves
13: SELECT Category.*
14: FROM Category
15: JOIN RecipeCategory ON RecipeCategory.CategoryId = Category.CategoryId
16: WHERE RecipeCategory.RecipeId = @RecipeId
17:
18: -- Select the ingredient information for the recipe
19: SELECT RecipeIngredient.*
20: FROM RecipeIngredient
21: JOIN Recipe ON Recipe.RecipeId = RecipeIngredient.RecipeId
22: WHERE Recipe.RecipeId = @RecipeId
23:
24: -- Select the ingredients themselves
25: SELECT Ingredient.*
26: FROM Ingredient
27: JOIN RecipeIngredient ON RecipeIngredient.IngredientId = Ingredient.IngredientId
28: JOIN Recipe ON Recipe.RecipeId = RecipeIngredient.RecipeId
29: WHERE Recipe.RecipeId = @RecipeId
30:
31: -- Select the units that are associated with the ingredients
32: SELECT Unit.*
33: FROM Unit
34: JOIN Ingredient ON Ingredient.UnitId = Unit.UnitId
35: JOIN RecipeIngredient ON RecipeIngredient.IngredientId = Ingredient.IngredientId
36: WHERE RecipeIngredient.RecipeId = @RecipeId
37: END
38: GO
Meet the EF extensions project
The stuff that is part of the entity framework in .NET framework 4 is way better than it was in v3.5, but it’s still not complete. If you are serious about stored procedures, you are going to need to add some extensions to your application that are part of the EF Extensions framework.
You can download the extensions here: http://code.msdn.microsoft.com/EFExtensions
The extensions project contains various components to make working with the entity framework easier. For example the following extensions are available:
- Stored procedure execution customization
- Materialization of arbitrary CLR types given a data reader or DB command.
- Connection lifetime management.
I’m not going to discuss each of these features here. There’s just one feature we need to use for the scenario to work, namely the stored procedure execution customization.
Creating the stored procedure mapping
Normally you would create a new function import on your entity model, but this allows for very little customization. So instead of doing that, I’m going to show you how you can use the EF extensions to map the stored procedure, so that it supports multiple resultsets.
Creating a custom stored procedure mapping starts with creating a partial class definition for the ObjectContext. To this context you will need to add a new method that returns the desired entity. In this case the method has to accept a RecipeId of type decimal and will return a single recipe with its child objects.
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5: using System.Data.Objects;
6: using Microsoft.Data.Extensions;
7: using System.Data.Common;
8: using System.Data.SqlClient;
9: using System.Data;
10:
11: namespace StoredProcWithMultipleResultSetsSample
12: {
13: public partial class RecipeBrowserEntities : ObjectContext
14: {
15: public Recipe GetRecipeWithIngredients(decimal recipeId)
16: {
17: Recipe result = null;
18:
19: //TODO: Implement this method
20:
21: return result;
22: }
23: }
24: }
Next is the stored procedure mapping itself. You can get a hold of a stored procedure using the CreateStoreCommand extension method that is exposed when you add a using statement for the Microsoft.Data.Extensions namespace. The result of the CreateStoreCommand method is a DbCommand that you can execute.
1: public Recipe GetRecipeWithIngredients(decimal recipeId)
2: {
3: Recipe result = null;
4:
5: SqlParameter recipeIdParameter = new SqlParameter
6: {
7: ParameterName = "@RecipeId",
8: DbType = System.Data.DbType.Decimal,
9: Value = recipeId
10: };
11:
12: DbCommand cmd = this.CreateStoreCommand("SelectRecipeWithIngredients",
13: CommandType.StoredProcedure, recipeIdParameter);
14:
15: // Use a connection scope to manage the lifetime of the connection
16: using(var connectionScope = cmd.Connection.CreateConnectionScope())
17: {
18: using(var reader = cmd.ExecuteReader())
19: {
20: //TODO: Materialize the recipe.
21: }
22: }
23:
24: return result;
25: }
Once you have the DbCommand you can execute it and materialize the results. I’m using a ConnectionScope to manage the lifecycle of the connection. The using statement ensures that the connection is properly closed when the resultsets have been processed. Normally you would do this kind of operation if you were using SqlConnection or any other DbConnection class. When working with the ADO.NET entity framework however, this will be managed for you and this is just a trick to prevent problems with the framework not being able to do its job when it comes to connection management.
The final step to retrieve the data returned by the stored procedure, is to iterate over the resultsets and materialize them.
1: public Recipe GetRecipeWithIngredients(decimal recipeId)
2: {
3: Recipe result = null;
4:
5: SqlParameter recipeIdParameter = new SqlParameter
6: {
7: ParameterName = "@RecipeId",
8: DbType = System.Data.DbType.Decimal,
9: Value = recipeId
10: };
11:
12: DbCommand cmd = this.CreateStoreCommand("SelectRecipeWithIngredients",
13: CommandType.StoredProcedure, recipeIdParameter);
14:
15: // Use a connection scope to manage the lifetime of the connection
16: using(var connectionScope = cmd.Connection.CreateConnectionScope())
17: {
18: using(var reader = cmd.ExecuteReader())
19: {
20: // Materialize the recipe.
21: result = reader.Materialize<Recipe>().Bind(this.Recipes).FirstOrDefault();
22:
23: if(result != null)
24: {
25: // Move on to the categories resultset and attach it to the recipe.
26: // Also bind it to the datacontext, so the object tracking works correctly.
27: reader.NextResult();
28: result.Categories.Attach(reader.Materialize<Category>().Bind(this.Categories));
29:
30: // Materialize the recipe ingredient information and attach it to the recipe.
31: // Also bind it to the datacontext, so the object tracking works correctly.
32: reader.NextResult();
33: result.RecipeIngredients.Attach(reader.Materialize<RecipeIngredient>().Bind(this.RecipeIngredients));
34:
35: // Materialize the ingredients and attach them to the datacontext to enable object tracking.
36: reader.NextResult();
37: var ingredients = reader.Materialize<Ingredient>().Bind(this.Ingredients);
38:
39: // Iterate over the ingredient information
40: // and attach the ingredients to the records
41: foreach(var item in result.RecipeIngredients)
42: {
43: // Attach the related ingredient to the ingredient reference property
44: item.IngredientReference.Attach(ingredients.FirstOrDefault(
45: ingredient => ingredient.IngredientId == item.IngredientId));
46: }
47:
48: reader.NextResult();
49: var units = reader.Materialize<Unit>().Bind(this.Units);
50:
51: // Iterate over the ingredients in the recipe and attach the related unit information
52: foreach(var ingredient in result.RecipeIngredients.Select(item => item.Ingredient))
53: {
54: // Attach the related unit to the unit reference property
55: ingredient.UnitReference.Attach(units.FirstOrDefault(unit => unit.UnitId == ingredient.UnitId));
56: }
57: }
58: }
59: }
60:
61: return result;
62: }
You can materialize a resultset by invoking the Materialize<T> method on the datareader. This will read the whole resultset and convert it into entities.
Although not required, it’s a good idea to bind the materialized entities to the ObjectContext. This enables the ObjectContext to perform object tracking on them, thus supporting update and delete scenarios. You can bind the entities using the Bind extension method to a specific EntitySet<T> object on the ObjectContext.
When you look at the previous code snippet, you will notice that I’m using a bunch of Attach calls. This is done so that I can link the child objects to their parents. You can use the Attach method on an EntitySet<T> object to attach multiple objects to it. Attaching two objects that are part of a many-to-one or a one-to-one relation is done by calling the Attach method on the …Reference properties that you can find on the various entities in your entity model.
The end result
When you execute the GetRecipeWithIngredients method you will end up with a complete object graph of a single recipe. In this case the “Chicken in the basket” recipe.
Conclusion
The combination stored procedures and the ADO.NET entity framework still isn’t a very happy marriage. Although it’s much better than with the previous version. There’s especially much to gain the field stored procedures that return multiple resultsets. It’s just too much work to write all the mapping code even with the help of the the Materialize<T> method.
At least I hope this post helps to give you an idea how you can use stored procedures that return multiple resultsets in combination with the ADO.NET entity framework.