It’s not very often, but there are times when a stored procedure just works a little better than LINQ. Complexity for developers can be decreased while performance can be dramatically increased for users. In those very rare cases, it may make sense to return more than one result set from a stored procedure. This post will show you how to utilize Entity Framework 6 to execute a stored procedure and materialize multiple result sets. This post specifically focuses on objects not found in your DbContext.

Returning multiple result sets is a feature of SqlClient that Entity Framework can take advantage of. It can pipeline multiple results over the same connection to reduce the number of round trips to retrieve data. This can lead to a significant performance increase that users will thank you for.

The Data

In this example we will have two tables: Cats and Dogs. You can find the script below.

CREATE TABLE [dbo].[Cats](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Dogs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]

-- Cats
INSERT INTO [dbo].[Cats] ([Name]) VALUES('Felix')
INSERT INTO [dbo].[Cats] ([Name]) VALUES('Garfield')
INSERT INTO [dbo].[Cats] ([Name]) VALUES('Heathcliff')
INSERT INTO [dbo].[Cats] ([Name]) VALUES('Grumpy Cat')

-- Dogs
INSERT INTO [dbo].[Dogs] ([Name]) VALUES('Lassie')
INSERT INTO [dbo].[Dogs] ([Name]) VALUES('Droopy')
INSERT INTO [dbo].[Dogs] ([Name]) VALUES('Scooby Doo')
INSERT INTO [dbo].[Dogs] ([Name]) VALUES('Santa''s Little Helper')
Go

CREATE PROCEDURE [dbo].[Pets]
AS
BEGIN

	SET NOCOUNT ON;

	Select * From [dbo].[Cats]
	Select * From [dbo].[Dogs]
END

When we execute our stored procedure, we get the following results.

Returning Multiple Results Sets (The Messy Way)

The code borrows from an Entity Framework example found on MSDN. The difference between my code and the example on MSDN, is the POCOs don’t necessarily have to be in the DbContext.

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace EntityFrameworkExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = new SampleDbContext();

            using (var connection = db.Database.Connection)
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = "EXEC [dbo].[Pets]";

                using (var reader = command.ExecuteReader())
                {
                    var cats =
                        ((IObjectContextAdapter) db)
                            .ObjectContext
                            .Translate<Cat>(reader)
                            .ToList();

                    reader.NextResult();

                    var dogs =
                        ((IObjectContextAdapter) db)
                            .ObjectContext
                            .Translate<Dog>(reader)
                            .ToList();
                }
            }

            Console.ReadLine();
        }
    }

    public class SampleDbContext : DbContext
    {
        public SampleDbContext()
            : base("Sample")
        {}
    }

    public class Cat
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Dog
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Note that the classes of Dog and Cat are not in the DbContext. This means your stored procedure results are not tied directly to your Entity Framework models. When we execute the following code we get the result we are looking for.

Returning Multiple Results Sets (The Reuseable Way)

There is clearly a pattern here.

1. Execute the command
2. Map to what we think the result looks like
3. Return multiple enumerables

Let’s see if we can make a helper class that can encapsulate that complexity. What I’d like to be able to do is write some code like this.

            var results = new SampleDbContext()
                .MultipleResults("[dbo].[Pets]")
                .With<Cat>()
                .With<Dog>()
                .Execute();

That’s what I’ve done with the code below.

public static class MultipleResultSets
{
    public static MultipleResultSetWrapper MultipleResults(this DbContext db, string storedProcedure)
    {
        return new MultipleResultSetWrapper(db, storedProcedure);
    }

    public class MultipleResultSetWrapper
    {
        private readonly DbContext _db;
        private readonly string _storedProcedure;
        public List<Func<IObjectContextAdapter, DbDataReader, IEnumerable>> _resultSets;

        public MultipleResultSetWrapper(DbContext db, string storedProcedure)
        {
            _db = db;
            _storedProcedure = storedProcedure;
            _resultSets = new List<Func<IObjectContextAdapter, DbDataReader, IEnumerable>>();
        }

        public MultipleResultSetWrapper With<TResult>()
        {
            _resultSets.Add((adapter, reader) => adapter
                .ObjectContext
                .Translate<TResult>(reader)
                .ToList());

            return this;
        }

        public List<IEnumerable> Execute()
        {
            var results = new List<IEnumerable>();

            using (var connection = _db.Database.Connection)
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = "EXEC " + _storedProcedure;

                using (var reader = command.ExecuteReader())
                {
                    var adapter = ((IObjectContextAdapter)_db);
                    foreach (var resultSet in _resultSets)
                    {
                        results.Add(resultSet(adapter, reader));
                        reader.NextResult();
                    }
                }

                return results;
            }
        }
    }
}

When we execute the new helper, we get the following results.

Note that all the results are there, but they are coming back as IEnumerable. You can utilize the Cast method in LINQ to strong type the enumerable.

There you have it! Use Entity Framework 6 to execute a stored procedure with multiple result sets even if your results are not in your DbContext.