Sometimes when using stored procedures you will need to return more than one result set. This scenario is commonly used to reduce the number of database round trips required to compose a single screen
Model
The examples in this article use a basic Blog and Posts model where a blog has many posts and a post belongs to a single blog. We will use a stored procedure in the database that returns all blogs and posts, something like this:
CREATE PROCEDURE [dbo].[GetAllBlogsAndPosts]
AS
SELECT * FROM dbo.Blogs
SELECT * FROM dbo.Posts
Accessing Multiple Result Sets with Code
In order to get multiple result sets working we need to drop to the ObjectContext API by using the IObjectContextAdapter interface. Once we have an ObjectContext then we can use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal. The following code sample demonstrates this in action.
using (var db = new BloggingContext())
{
// If using Code First we need to make sure the model is built before we open the connection
// This isn't required for models created with the EF Designer
db.Database.Initialize(force: false);
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
// Read Blogs from the first result set
var blogs = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);
foreach (var item in blogs)
{
Console.WriteLine(item.Name);
}
// Move to second result set and read Posts
reader.NextResult();
var posts = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Post>(reader, "Posts", MergeOption.AppendOnly);
foreach (var item in posts)
{
Console.WriteLine(item.Title);
}
}
finally
{
db.Database.Connection.Close();
}
}