Handle multiple result from a stored procedure with SqlQuery

Question

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?

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

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.

// 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();
    }
}