try-catch-FAIL

Failure is inevitable

NAVIGATION - SEARCH

Returning Multiple Result Sets from a Stored Procedure Using Entity Framework 6

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.

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! Smile 

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!

About Matt Honeycutt...

Matt Honeycutt is a software architect specializing in ASP.NET web applications, particularly ASP.NET MVC. He has over a decade of experience in building (and testing!) web applications. He’s an avid practitioner of Test-Driven Development, creating both the SpecsFor and SpecsFor.Mvc frameworks.

He's also an author for Pluralsight, where he publishes courses on everything from web applications to testing!

blog comments powered by Disqus