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!