Entity Framework Stored Procedure with Multiple Result Sets

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();
	}
}

MSDN: Entity Framework Sprocs with Multiple Result Sets