Entity Framework Stored Procedures - Multiple Result sets with CodeFirst

Question

I am using the below code to get a regular result from a stored procedure:

var paramUserId = new SqlParameter
{
    ParameterName = "userId",
    Value = userId
};

string query = string.Format("{0} {1}",
              "SpSetFoo",
              "@userId");

var results = context.Database.SqlQuery<FooModel>(query,
                                             paramUserId);

result = results.ToList();

Meanwhile I need to retrieve multiple result sets from another stored procedure, which I found it's possible according to this documentation : http://msdn.microsoft.com/en-us/data/jj691402.aspx

However the example from Microsoft is using ADO.NET. It's not possible to achieve the same result without ADO.NET using EF instead?

Thanks

Answer 1

This is an old topic but adding comments here just in case someone needs it. I needed to consume a stored proc that returned two tables from a different database and then after processing the returned data storing into our application database. Referred to the standard documentation and followed the steps but did not like it. First there were problems and the code exposed some underbelly that was not a good idea from maintainability point of view.

That where a Nuget package designed specifically for handling SPs come into picture. Take a look at CodeFirstStoredProcs. Brilliant package with very specific focus and does the job perfectly. This returns a collection of objects for each result set of stored proc that can then be used any way desired. Their is a good and consistent support for different versions of EF including version 6. Also check the explanation on code project Code First Stored Procedures. Downloaded source code even has a PDF that explains how to use it in detailed steps.

Big thanks to the author aureolin.

Answer 2

See this link. this will work with EF 6.0 Code first.

http://www.khalidabuhakmeh.com/entity-framework-6-multiple-result-sets-with-stored-procedures

I've My own extension based on the above link, C# 6.0 , add parameter and work with multiple select not necessary a procedure.

 public static class MultipleResultSets
{

    #region Public Methods
    public static MultipleResultSetWrapper MultipleResults(this DbContext db,string query,IEnumerable<SqlParameter> parameters=null) => new MultipleResultSetWrapper(db: db,query: query,parameters: parameters);
    #endregion Public Methods

    #region Public Classes
    public class MultipleResultSetWrapper
    {

        #region Public Fields
        public List<Func<DbDataReader,IEnumerable>> _resultSets;
        #endregion Public Fields

        #region Private Fields
        private readonly IObjectContextAdapter _Adapter;
        private readonly string _CommandText;
        private readonly DbContext _db;
        private readonly IEnumerable<SqlParameter> _parameters;
        #endregion Private Fields

        #region Public Constructors
        public MultipleResultSetWrapper(DbContext db,string query,IEnumerable<SqlParameter> parameters = null)
        {
            _db = db;
            _Adapter = db;
            _CommandText = query;
            _parameters = parameters;
            _resultSets = new List<Func<DbDataReader,IEnumerable>>();
        }
        #endregion Public Constructors

        #region Public Methods
        public MultipleResultSetWrapper AddResult<TResult>()
        {
            _resultSets.Add(OneResult<TResult>);
            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 = _CommandText;
                if(_parameters?.Any() ?? false) { command.Parameters.AddRange(_parameters.ToArray()); }
                using(var reader = command.ExecuteReader())
                {
                    foreach(var resultSet in _resultSets)
                    {
                        results.Add(resultSet(reader));
                    }
                }

                return results;
            }
        }
        #endregion Public Methods

        #region Private Methods
        private IEnumerable OneResult<TResult>(DbDataReader reader)
        {
            var result = _Adapter
                .ObjectContext
                .Translate<TResult>(reader)
                .ToArray();
            reader.NextResult();
            return result;
        }
        #endregion Private Methods

    }
    #endregion Public Classes

}

and this is an example how to call it

var Policy = "123";
var Results=   db
        .MultipleResults($"EXEC GetPolicyInfo '{Policy}'")
        .AddResult<Driver>()
        .AddResult<Address>()
        .AddResult<Phone>()
        .AddResult<Email>()
        .AddResult<Vehicle>()
        .Execute();
        var Output= new clsPolicyInfo
        {
            Drivers = Results[0] as Driver[],
            Addresses = Results[1] as Address[],
            Phones = Results[2] as Phone[],
            Emails = Results[3] as Email[],
            Vehicles = Results[4] as Vehicle[]
        };