{"id":303,"date":"2012-04-24T12:06:00","date_gmt":"2012-04-24T12:06:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/cesardelatorre\/2012\/04\/24\/entity-framework-code-first-dbcontext-and-sql-azure-connection-fault-handling\/"},"modified":"2012-04-24T12:06:00","modified_gmt":"2012-04-24T12:06:00","slug":"entity-framework-code-first-dbcontext-and-sql-azure-connection-fault-handling","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/entity-framework-code-first-dbcontext-and-sql-azure-connection-fault-handling\/","title":{"rendered":"Entity Framework Code First DbContext and SQL Azure Connection Fault Handling"},"content":{"rendered":"

\"image\"  <\/a><\/p>\n

I’ve been working migrating an App to Windows Azure<\/em> and SQL Azure<\/em>. This app is using Entity Framework 4.3 Code First<\/strong> and DbContext<\/strong>.<\/p>\n

One important point is related to the SQL Azure Conection Fault Handling. If you don’t know about this topic, you can read this explanatory info:<\/p>\n

http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/1541.sql-azure-connection-management-en-us.aspx<\/a><\/p>\n

Then, regarding a workaround when using Entity Framework 4.0 (based on ObjectContext), I wrote this post a few months ago:<\/p>\n

http:\/\/blogs.msdn.com\/b\/cesardelatorre\/archive\/2010\/12\/20\/handling-sql-azure-connections-issues-using-entity-framework-4-0.aspx<\/a> <\/p>\n

Also, this link, provided by the AppFabric Customer Advisory Team, is quite useful, too:<\/p>\n

http:\/\/blogs.msdn.com\/b\/appfabriccat\/archive\/2010\/12\/11\/sql-azure-and-entity-framework-connection-fault-handling.aspx<\/a><\/p>\n

But, this info was all related to EF 4.0, ObjectContext, EF Templates (STE & POCO T4templates, etc.), not about EF 4.3, CODE FIRST APPROACH and DbContext.<\/p>\n

Therefore, this post is about how to handle SQL Azure Connection Faults<\/strong> (something common in the Windows Azure platform) when using EF 4.3, DbContext and CODE FIRST APPROACH.<\/p>\n

THE ISSUE:<\/strong><\/p>\n

If you take a look to the link provided by the AppFabric Customer Advisory Team, about the ‘Retry Policy<\/strong>’ when creating the EF Context. The tactic is to implement the OnContextCreated()<\/strong> partial method of your model context<\/strong> (based on ObjectContext<\/strong>) which is called each time a new context is instantiated. In this partial method, you employ a retry policy which opens a connection, submits a dummy query and handles exceptions with proper closure of invalid connections. In this way, the pool is ‘cleansed’ of all connections that have disconnected due to network glitches or idle expirations.<\/strong><\/p>\n

This was the initial suggested code for EF 4.0<\/b> and ObjectContext<\/b>:<\/p>\n

 <\/p>\n

partial void OnContextCreated()
<\/span><\/strong>{
    int MaxRetries = 10;
    int DelayMS = 100;<\/p>\n

    RetryPolicy policy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(MaxRetries, TimeSpan.FromMilliseconds(DelayMS));
    policy.ExecuteAction(() =>
<\/strong>    {
        try
        {
            string ss = Connection.ConnectionString;
            Connection.Open();
<\/span><\/strong>            var storeConnection = (SqlConnection)((EntityConnection)Connection).StoreConnection;
            new SqlCommand(“declare @i int”, storeConnection).ExecuteNonQuery();
            \/\/ throw new ApplicationException(“Test only”);
        }
        catch (Exception e)
        {
            Connection.Close();
            throw e;
        }
    }
    );
}<\/p>\n

The issue is that this is based on the OnContextCreated()<\/strong> method which is related to your ObjectContext<\/strong> class.<\/p>\n

When using CODE-FIRST<\/strong> approach, we use a context based on the DbContext<\/strong>, so we’ll have a slightly different code.<\/p>\n

Instead of placing that code into the OnContextCreated() we can put it within the constructor of your model context<\/strong> (in this case based on DbContext<\/strong>).<\/p>\n

Take into account that we’re using the RetryPolicy<\/strong> class, which is implemented in a special building block made by Microsoft Pattern & Practices <\/em>team, called ‘Transient Fault Handling Application Block<\/strong>’ (good job Grigori & P&P team<\/strong>!).<\/p>\n

It is available from NuGet<\/strong>: http:\/\/nuget.org\/packages\/EnterpriseLibrary.WindowsAzure.TransientFaultHandling<\/a><\/p>\n

See also:<\/p>\n

http:\/\/blogs.msdn.com\/b\/agile\/archive\/2011\/12\/02\/announcing-the-enterprise-library-integration-pack-for-windows-azure-with-autoscaling-transient-fault-handling-and-more.aspx<\/a> <\/p>\n

Anyway, this would be the first version of the code for EF & DbContext (Still, it doesn’t work):<\/p>\n

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;<\/p>\n

…<\/p>\n

using System.Data.Common;
using System.Data.SqlClient;
using System.Data.EntityClient;
using Microsoft.Practices.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure; <\/p>\n

public class MyBCUnitOfWork : DbContext<\/span>
<\/strong>{
    \/\/Constructor where we handle ‘SQL Azure and Entity Framework Connection Fault Handling’
    public MyBCUnitOfWork()
<\/span><\/strong>        : base()
    {
        DbConnection<\/strong> currentDbConn = this.Database.Connection;
       
        int MaxRetries = 10;
        int DelayMS = 100;<\/p>\n

        RetryPolicy policy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(MaxRetries, TimeSpan.FromMilliseconds(DelayMS));
        policy.ExecuteAction(() =><\/strong>
        {
            try
            {
                currentDbConn.Open(); \/\/(Issue here!!)<\/span><\/strong>               <\/p>\n

                var storeConnection = (SqlConnection)currentDbConn;
                new SqlCommand(“declare @i int”, storeConnection).ExecuteNonQuery();
                \/\/ throw new ApplicationException(“Test only”);
            }
            catch (Exception e)
            {
                currentDbConn.Close();
                throw e;
            }
        }
        );<\/p>\n

    }<\/p>\n

But, that is not the only difference we need to make, because if you only put that code within the constructor, when you execute your app, you’ll get an error when executing any EF code (your Linq sentences in any real DB access you do):<\/p>\n

Exception mesage: “EntityConnection can only be constructed with a closed DbConnection<\/span><\/strong>”<\/p>\n

This issue is due to some internal changes in EF 4.3 DbContext, and it is explained by my friend Diego Vega<\/strong> in the following post:<\/p>\n

Exception from DbContext API: EntityConnection can only be constructed with a closed DbConnection<\/h5>\n

http:\/\/blogs.msdn.com\/b\/diego\/archive\/2012\/01\/26\/exception-from-dbcontext-api-entityconnection-can-only-be-constructed-with-a-closed-dbconnection.aspx<\/a><\/p>\n

It is a very nice explanatory information about that issue. In any case, the solution for that exception is to open the connection using the ObjectContext object instead of the DbConnection.<\/p>\n

Instead of this line:<\/p>\n

currentDbConn.Open(); \/\/(Issue here!!)<\/span><\/strong>               <\/p>\n

We use this other line of code:<\/p>\n

((IObjectContextAdapter)this).ObjectContext.Connection.Open();<\/strong><\/p>\n

Then, we’ll be able to run all the workaround within a DbContext constructor, like the final code below:<\/p>\n

public class MyBCUnitOfWork
    : DbContext, IQueryableUnitOfWork<\/strong>
{
    \/\/Constructor where we handle ‘SQL Azure and Entity Framework Connection Fault Handling’
    public MyBCUnitOfWork()
<\/strong>        : base()
    {
        \/\/SQL Azure and Entity Framework Connection Fault Handling
        \/\/See: http:\/\/blogs.msdn.com\/b\/appfabriccat\/archive\/2010\/12\/11\/sql-azure-and-entity-framework-connection-fault-handling.aspx?Redirected=true<\/a>
        DbConnection currentDbConn = this.Database.Connection;
       
        int MaxRetries = 10;
        int DelayMS = 100;<\/p>\n

        RetryPolicy policy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(MaxRetries, TimeSpan.FromMilliseconds(DelayMS));
        policy.ExecuteAction(() =>
        {
            try
            {
                \/\/(CDLTLL) Using ObjectContext.Connection because there’s an issue when using DbConnection.Open() here.
                \/\/See this blog post from Diego Vega:
                \/\/http:\/\/blogs.msdn.com\/b\/diego\/archive\/2012\/01\/26\/exception-from-dbcontext-api-entityconnection-can-only-be-constructed-with-a-closed-dbconnection.aspx<\/a>
                ((IObjectContextAdapter)this).ObjectContext.Connection.Open();<\/span><\/strong><\/p>\n

                var storeConnection = (SqlConnection)currentDbConn;
                new SqlCommand(“declare @i int”, storeConnection).ExecuteNonQuery();
                \/\/ throw new ApplicationException(“Test only”);
            }
            catch (Exception e)
            {
                currentDbConn.Close();
                throw e;
            }
        }
        );<\/p>\n

    } <\/p>\n

With that, we would have the ‘Retry Policy<\/strong>’ implemented when creating any DbContext<\/strong> object, so you employ a retry policy which opens a connection, submits a dummy query and handles exceptions with proper closure of invalid connections. In this way, the connection-pool is ‘cleansed’ of all connections that have disconnected due to network glitches, Sql Azure cluster-fail-over or idle expirations.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

  I’ve been working migrating an App to Windows Azure and SQL Azure. This app is using Entity Framework 4.3 Code First and DbContext. One important point is related to the SQL Azure Conection Fault Handling. If you don’t know about this topic, you can read this explanatory info: http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/1541.sql-azure-connection-management-en-us.aspx Then, regarding a workaround when […]<\/p>\n","protected":false},"author":362,"featured_media":12806,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,90,114],"class_list":["post-303","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cesardelatorre","tag-entity-framework","tag-sql-azure","tag-window-azure"],"acf":[],"blog_post_summary":"

  I’ve been working migrating an App to Windows Azure and SQL Azure. This app is using Entity Framework 4.3 Code First and DbContext. One important point is related to the SQL Azure Conection Fault Handling. If you don’t know about this topic, you can read this explanatory info: http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/1541.sql-azure-connection-management-en-us.aspx Then, regarding a workaround when […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/users\/362"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/comments?post=303"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/303\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media\/12806"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media?parent=303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/categories?post=303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/tags?post=303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}