I know, I know, you’re already rolling your eyes!  “Stored procedures??!?”  But hey, sometimes a stored procedure is the best solution to a problem!  Entity Framework allows you to call stored procedures quite easily out of the box, but working with sprocs that return multiple result sets isn’t so easy.  Here’s a handy extension method you can use to make it easier.

[more]

First, credit where credit is due: this approach was inspired by Rowan Miller’s blog post.  Thanks, Rowan!  Check out his approach if you’re more interested in how this approach works.

Now, let’s say we have a simple sproc that returns two sets of related data for a given month, blog posts and their comments:

CREATE PROCEDURE dbo.GetPostsByDateRange
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
AS
BEGIN
    SELECT
       *
    FROM
       Posts 
    WHERE
       PostDate BETWEEN @StartDate AND @EndDate

    SELECT
       *
    FROM
       Comments 
       JOIN Posts
          ON Comments.Post_Id = Posts.Id
    WHERE
       PostDate BETWEEN @StartDate AND @EndDate
END

And let’s say we have a couple of C# view models like this:

public class PostModel
{
    public int Id {get; set;}
    public string Title {get; set;}
    public IList<CommentModel> Comments {get; set;}
    
    public PostModel()
    {
        Comments = new List<CommentModel>();
    }
}

public class CommentModel
{
    public int Id {get; set;}
    public string Author {get; set;}
    public string Body { get; set;}
}

We want to load our PostModels along with their comments.   Yes, we could do this quite easily using EF without a sproc, but let’s pretend we couldn’t because our sproc actually did something interesting!  

Using our extension method, we can now execute our sproc once, and get both sets of data:

var query = "EXEC dbo.GetPostsByDate @StartDate, @EndDate";
var parameters = new[] 
{
    new SqlParameter("@StartDate", startDate),
    new SqlParameter("@EndDate", endDate),
};

//Assume _context is your EF DbContext
using (var multiResultSet = _context.MultiResultSetSqlQuery(query, parameters))
{
    var posts = multiResultSet.ResultSetFor<PostModel>().ToDictionary(x => x.Id);

    var comments = multiResultSet.ResultSetFor<CommentModel>().ToArray();

    foreach (var comment in comments)
    {
        posts[comment.PostId].Comments.Add(comment);
    }
    
    return posts.Values.ToArray();
}

And here’s the code for the extension method and the MultiSetReader:

public static class DbContextExtensions
{
    public static MultiResultSetReader MultiResultSetSqlQuery(this DbContext context, string query, params SqlParameter[] parameters)
    {
        return new MultiResultSetReader(context, query, parameters);
    }
}

public class MultiResultSetReader : IDisposable
{
    private readonly DbContext _context;
    private readonly DbCommand _command;
    private bool _connectionNeedsToBeClosed;
    private DbDataReader _reader;

    public MultiResultSetReader(DbContext context, string query, SqlParameter[] parameters)
    {
        _context = context;
        _command = _context.Database.Connection.CreateCommand();
        _command.CommandText = query;

        if (parameters != null && parameters.Any()) _command.Parameters.AddRange(parameters);
    }


    public void Dispose()
    {
        if (_reader != null)
        {
            _reader.Dispose();
            _reader = null;
        }

        if (_connectionNeedsToBeClosed)
        {
            _context.Database.Connection.Close();
            _connectionNeedsToBeClosed = false;
        }
    }

    public ObjectResult<T> ResultSetFor<T>()
    {
        if (_reader == null)
        {
            _reader = GetReader();
        }
        else
        {
            _reader.NextResult();
        }

        var objContext = ((IObjectContextAdapter) _context).ObjectContext;

        return objContext.Translate<T>(_reader);
    }

    private DbDataReader GetReader()
    {
        if (_context.Database.Connection.State != ConnectionState.Open)
        {
            _context.Database.Connection.Open();
            _connectionNeedsToBeClosed = true;
        }

        return _command.ExecuteReader();
    }
}

Have fun!