2

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?

mmzadeh
  • 21
  • 4
  • Please see the following link. http://stackoverflow.com/questions/23172888/entity-framework-stored-procedures-multiple-result-sets-with-codefirst/35594099#35594099 – Waleed A.K. Oct 05 '16 at 23:11

3 Answers3

0

Have you tried wiring up the built-in connection resiliency from a custom DbConfiguration implementation like so?

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
        : base()
    {
        var strategy = System.Data.Entity.SqlServer.SqlAzureExecutionStrategy();
        SetExecutionStrategy("System.Data.EntityClient", strategy);
        SetExecutionStrategy("System.Data.SqlClient", strategy);
    }
}

I've used this approach with MARS and I've had no issues with it.

Martin Costello
  • 9,672
  • 5
  • 60
  • 72
  • Yes. I followed the Julie Lerman's guideline and implemented the configuration class. The issue is only in the processing of "Multiple Result Sets" which does not trigger the interceptor ReaderExecuting method. Everything else works fine. – mmzadeh Aug 11 '15 at 05:33
0

In this scenario the call to ExecuteReader is outside the scope of EF - the code is calling it directly on the ADO.NET command object. Because you are circumventing EF altogether for the execution of the command then you miss out on everything EF (including interceptors). Translate simply takes the already retrieved results and populated them into objects.

tdykstra
  • 5,880
  • 2
  • 23
  • 20
0

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>();
mmzadeh
  • 21
  • 4