title: Using Entity Framework with SQL Azure - Reliability
body: <p>I'm writing an application for Windows Azure. I'm using Entity Framework to access SQL Azure. Due to throttling and other mechanisms in SQL Azure, I need to make sure that my code performs retries if an SQL statement has failed. I'm trying to come up with a solid method to do this.</p>
  
  <p>(In the code below, ObjectSet returns my EFContext.CreateObjectSet())</p>
  
  <p>Let's say I have a function like this:</p>
  
  <pre><code>  public Product GetProductFromDB(int productID)
    {
       return ObjectSet.Where(item =&gt; item.Id = productID).SingleOrDefault();
    }
  </code></pre>
  
  <p>Now, this function performs no retries and will fail sooner or later in SQL Azure. A naive workaround would be to do something like this:</p>
  
  <pre><code>  public Product GetProductFromDB(int productID)
    {
       for (int i = 0; i &lt; 3; i++)
       {
          try
          {
             return ObjectSet.Where(item =&gt; item.Id = productID).SingleOrDefault();
          }
          catch
          {
  
          }
       }
    }
  </code></pre>
  
  <p>Of course, this has several drawbacks. I will retry regardless of SQL failure (retry is waste of time if it's a primary key violation for instance), I will retry immediately without any pause and so on. </p>
  
  <p>My next step was to start using the Transient Fault Handling library from Microsoft. It contains RetryPolicy which allows me to separate the retry logic from the actual querying code:</p>
  
  <pre><code>  public Product GetProductFromDB(int productID)
    {
       var retryPolicy = new RetryPolicy&lt;SqlAzureTransientErrorDetectionStrategy&gt;(5);
  
       var result = _retryPolicy.ExecuteAction(() =&gt;
          {
             return ObjectSet.Where(item =&gt; item.Id = productID).SingleOrDefault;
          });
  
       return result;
    }
  </code></pre>
  
  <p>The latest solution above is described as a <a href="http://blogs.msdn.com/b/appfabriccat/archive/2010/10/28/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications.aspx" rel="noreferrer">http://blogs.msdn.com/b/appfabriccat/archive/2010/10/28/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications.aspx</a> Best Practices for Handling Transient Conditions in SQL Azure Client Application (Advanced Usage Patterns section).</p>
  
  <p>While this is a step forward, I still have to remember to use the RetryPolicy class whenever I want to access the database via Entity Framework. In a team of several persons, this is a thing which is easy to miss. Also, the code above is a bit messy in my opinion.</p>
  
  <p>What I would like is a way to enforce that retries are always used, all the time. The Transient Fault Handling library contains a class called ReliableSQLConnection but I can't find a way to use this with Entity Framework.</p>
  
  <p>Any good suggestions to this issue?</p>