实体框架存储过程——使用CodeFirst的多个结果集

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

同时，我需要从另一个存储过程中检索多个结果集，根据这个文档，我发现这是可能的:http://msdn.microsoft.com/en- us/data/j691402.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?

然而，微软的例子是使用ADO.NET。没有ADO是不可能达到同样的结果的。净使用EF呢?

Thanks

谢谢

2 个解决方案

#1

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

看到这个链接。这将首先使用EF 6.0代码。

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.

我有我自己的扩展，基于上面的链接，c# 6.0，添加参数和使用多重选择不必要的过程。

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

#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.

这是一个老话题，但在这里添加注释，以防有人需要它。我需要使用一个存储的proc，它从不同的数据库返回两个表，然后在处理存储到应用程序数据库的返回数据之后。参考标准文档并遵循这些步骤，但不喜欢它。首先出现了问题，代码暴露了一些不足之处，从可维护性的角度来看，这不是一个好主意。

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.

一个专门为处理SPs而设计的Nuget包出现了。看看CodeFirstStoredProcs。出色的包装和非常明确的重点和工作完美。这将为存储的proc的每个结果集返回一个对象集合，然后可以使用任何所需的方式。他们对不同版本的EF(包括版本6)提供了良好且一致的支持。还要检查代码项目代码的说明，先存储过程。下载的源代码甚至有一个PDF，说明如何在详细步骤中使用它。

Big thanks to the author aureolin.

非常感谢作者aureolin。

#1