Entity Framework -- Returning Multiple Result Sets from Stored Procedure.

Returning Multiple Result Sets from Stored Procedure in Entity Framework, is no more a difficult task.

Here's the way how we can do:

Sample Procedure Returning multiple Result-Sets:

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

Sample Entities that we might have in Entity Framework:

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


Extenstion Class with Methods, to fetch Multiple Result Sets in EF:

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

Using the above extension methods, we can achieve our goal. Here we come:

Sample Code:

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


Hope it helps.
Reference from here