Handle multiple result from a stored procedure with SqlQuery

c# entity-framework sql-server stored-procedures

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?

1
7
8/14/2014 9:37:42 AM

Accepted Answer

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();
    }
}
14
2/16/2017 11:23:03 AM


Related Questions





Related

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