使用SqlQuery处理存储过程的多个结果

I have a stored procedure which returns a multiple set of results (two tables). I call the stored procedure like this:

我有一个存储过程，它返回一组多个结果（两个表）。我这样调用存储过程：

var result = context.Database.SqlQuery<RefererStatisticResult>(
"exec [dbo].[GetReferrer] @StartDate, @EndDate, @Source",
this.CreateInParam("@StartDate", SqlDbType.DateTime, startDate),
this.CreateInParam("@EndDate", SqlDbType.DateTime, endDate),
this.CreateInParam("@Source", SqlDbType.SmallInt, eventSourveVal)).ToArray();

My RefererStatisticResult contains two List<> properties, for the result set, but the lists are empty after the call. How can I handle the result set? Is it possible with the SqlQuery?

我的RefererStatisticResult包含两个List <>属性，用于结果集，但调用后列表为空。我该如何处理结果集？是否可以使用SqlQuery？

1 个解决方案

#1

The DbContext has no native support for materialising multiple resultsets. However, it is reasonably straight forward to achieve by dropping down to the ObjectContext and using the Translate method to copy results from a DbDataReader into entities in your domain model.

DbContext没有本机支持实现多个结果集。但是，通过下拉到ObjectContext并使用Translate方法将结果从DbDataReader复制到域模型中的实体，可以合理地直接实现。

Here's some example code. This assumes your ReferrerStatisticResult is just a container for the two lists called Set1 and Set2. Obviously adjust according to your actual domain model.

这是一些示例代码。这假设您的ReferrerStatisticResult只是名为Set1和Set2的两个列表的容器。显然根据您的实际域模型进行调整。

// Create container ready for the resultsets
var result = new RefererStatisticResult();

using (var myContext = new MyContext())
{
    // Create command from the context in order to execute
    // the `GetReferrer` proc
    var command = myContext.Database.Connection.CreateCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "[dbo].[GetReferrer]";
    // add in command parameters
    // (not shown)

    try
    {
        myContext.Connection.Open();
        var reader = command.ExecuteReader();

        // Drop down to the wrapped `ObjectContext` to get access to
        // the `Translate` method
        var objectContext = ((IObjectContextAdapter)myContext).ObjectContext;

        // Read Entity1 from the first resultset
        result.Set1 = objectContext.Translate<Entity1>(reader, "Set1", MergeOptions.AppendOnly);

        // Read Entity2 from the second resultset
        reader.NextResult();
        result.Set2 = objectContext.Translate<Entity2>(reader, "Set2", MergeOptions.AppendOnly);        
    }
    finally
    {
        myContext.Database.Connection.Close();
    }
}