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

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

Answer 2

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 3

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

Entity Framework is part of ADO.NET... this document that you have linked, is showing you what you want using ADO.NET Entity Framework. The example is using raw sql command to execute a stored procedure (there is no point writing a LINQ query if you have already written your SQL procedure). See here:

A primary goal of the upcoming version of ADO.NET is to raise the level of abstraction for data programming, thus helping to eliminate the impedance mismatch between data models and between languages that application developers would otherwise have to deal with. Two innovations that make this move possible are Language-Integrated Query and the ADO.NET Entity Framework. The Entity Framework exists as a new part of the ADO.NET family of technologies. ADO.NET will LINQ-enable many data access components: LINQ to SQL, LINQ to DataSet and LINQ to Entities.

---

The code:

It's not clear what are the result sets that you are returning from your procedure: SpSetFoo... the name SpSetFoo suggests that procedure updates Foo in the DB. To simplify things, I assume you have a procedure called GetFooAndBarForUser:

CREATE PROCEDURE [dbo].[GetFooAndBarForUser] (@userId int)
AS
    SELECT * FROM Foo F WHERE F.UserId = @userId
    SELECT * FROM Bar B WHERE B.UserId = @userId

This is how you can read both models:

public void GetTwoResultSetsForUserId(int userId)
{
    using (var db = new MyDbContext())
    {
        // Create a SQL command and add parameter
        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "[dbo].[GetFooAndBarForUser]";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@userId", userId));

        // execute your command
        db.Database.Connection.Open();
        var reader = cmd.ExecuteReader();

        // Read first model --> Foo
        var blogs = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Foo>(reader, "Foo", MergeOption.AppendOnly);

        // move to next result set
        reader.NextResult();

        // Read second model --> Bar
        var bar = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Post>(reader, "Bar", MergeOption.AppendOnly);
    }
}