Entity Framework Multiple Result Sets, Azure Connection Resiliency and Command Interception

Question

I am trying to add Connection Resiliency to my Repository class and test it in order to move my WCF service to Azure. Julie Lerman has a great post on it:

http://thedatafarm.com/data-access/testing-out-the-connection-resiliency-feature-into-ef6/#comment-61311

When I call a repository method that uses normal Entity Framework queries, the Interceptor ReaderExecuting method get triggered and I am able to simulate the connection problem.

var states = dbContext.Blogs.ToList();

But for some reason the Interceptor ReaderExecuting method doesn’t get called when one of my repository methods which uses “Multiple Result Sets” returned from a stored-proc is being executed (see the code below). I am expecting the ReaderExecuting method being called when "ObjectContext.Translate" or "ToList" is executed, but it doesn't:

db.Database.Connection.Open();

var reader = cmd.ExecuteReader();

var blogs = ((IObjectContextAdapter)db).ObjectContext.Translate<Blog>(reader).ToList();

FYI, I'm following this article for handling “Multiple Result Sets”:

https://msdn.microsoft.com/en-us/data/jj691402.aspx

I am trying to relay on the Entity Framework 6+ Execution Strategy to handle Connection Resiliency. But if it is not capable of handling Multiple Result Sets, my next option will be using Polly library for Transient Exception Handling.

Have you ever run into this situation?

Do you have any solution for it?

Answer 1

Because of the EF limitation mentioned by @tdykstra, I changed my stored-proc to return an XML output instead of "Multiple Result Sets". I used "SqlQuery" method to call the stored-proc and get the output XML, then I deserialized the XML into objects. This way Interceptor ReaderExecuting method got executed and I was able to test Connection Resiliency. Hopefully EF team add more support for "Multiple Result Sets" on the future releases. Here is the sample code:

var paramId = new SqlParameter
{
    ParameterName = "id",
    SqlDbType = SqlDbType.Xml,
    Direction = ParameterDirection.Input,
    Value = 1
};

var paramXmlResult = new SqlParameter
{
    ParameterName = "XmlResult",
    SqlDbType = SqlDbType.Xml,
    Direction = ParameterDirection.Output
};

db.Database.SqlQuery<XElement>(
    "EXEC [dbo].[GetDataAsXml] @id, @XmlResult OUT", 
    paramId, paramXmlResult).ToList();


XElement xmlResult = XElement.Parse(paramXmlResult.Value.ToString());

//FromXElement is an Extension method that deserializes XML into a Type (like MyData)
MyData data = xmlResult.FromXElement<MyData>();