Entity Framework Stored Procedures - Multiple Result sets with CodeFirst

asp.net-mvc c# entity-framework


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}",

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

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?


4/19/2014 4:58:30 PM

Popular Answer

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


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>()
            return this;

        public List<IEnumerable> Execute()
            var results = new List<IEnumerable>();

            using(var connection = _db.Database.Connection)
                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)

                return results;
        #endregion Public Methods

        #region Private Methods
        private IEnumerable OneResult<TResult>(DbDataReader reader)
            var result = _Adapter
            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}'")
        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[]
2/24/2016 9:11:33 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow