Entity Framework Internals: Connection Resiliency

When you use cloud services to deploy your application and database it might be possible that transient connection will be a problem between database server and web servers. When you use on premise servers that uses the database server and web server on same data centre. When you use cloud there will be huge infrastructures and you never know where it is deployed even if it deployed on same data centre there will more connection like network load balancers etc. When you use cloud services that will be shared by lots of users which means its responsiveness can be affected by them. And your access to the database might be subject to throttling. Throttling means the database service throws exceptions when you try to access it more frequently than is allowed in your Service Level Agreement (SLA).

So in cloud service there will be transient problems which will be resolved in short period of time. So when you got such kind of errors then you can wait for some time and then you have retry. For that kind of operation Entity Framework provides connection resiliency feature.

The connection resiliency features must be configured for proper database services. It has to know which exceptions are likely to be transient problem and which exceptions are caused by our code. It has to wait for an appropriate amount of time between retries of failed operation. Also it has to try number of times before giving up.

Enough theory let’s see how we can use that feature with SQL Azure with Entity Framework. I’m going to create a small console application for the same.



and I have added Entity framework via nuget pacakge.



Now it’s time to create SQL Azure on Window Azure. So login into windows azure portal and go to data-storage part.



Once you click on it it will ask for Server creation.



Once you are done with it. It will ask for database creation.



After doing this, It will take some time to create database. Now if you want to access this database with local SQL Server and local application you need to create firewall settings. So find your ip and allow this IP in firewall settings like below.



Once you are done with it. You are now able to connection to SQL Azure database with your SQL Server management studio. So We are all set to write some code. Here is the Employee class I have created.

namespace EFConnectionResiliency
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string  Designation { get; set; }
    }
}

As we all know Entity Framework provides Connection Resiliency features it provides DbConfiguration class from where you can create your own configuration based on your cloud services you are using. So in our case we are using SQL Azure so I have created my EmployeeConfiguration class like below.

using System.Data.Entity;
using System.Data.Entity.SqlServer;

namespace EFConnectionResiliency
{
    public class EmployeeConfiguration : DbConfiguration
    {
        public EmployeeConfiguration()
        {
            SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
        }
    }
}

So in above code if you see that, I have set execution Strategy to SQL Azure as I have used SQL Azure in this example.  Now it’s time to write our data context class. There are multiple way you can tell your datacontext class to use this configuration and easiest way of doing this is to use DbConfigurationType Attribute like below.

using System.Data.Entity;

namespace EFConnectionResiliency
{
    [DbConfigurationType(typeof(EmployeeConfiguration))] 
    public class EmployeeContext : DbContext
    {
        public EmployeeContext(): base("DefaultConnectionString")
        {
            // public constructor to use connection string
        }
        public DbSet<Employee> Employees { get; set; } 
    }
}

And here is the code for the my console application.

using System;

namespace EFConnectionResiliency
{
    class Program
    {
        static void Main(string[] args)
        {
            Employee employee = new Employee
            {
                FirstName = "Jalpesh",
                LastName = "Vadgama",
                Designation = "Project Manager"
            };

            using (EmployeeContext employeeContext = new EmployeeContext())
            {
                employeeContext.Employees.Add(employee);
                employeeContext.SaveChanges();
            }

            Console.WriteLine("Employee Created");
        }
    }
}

Now when you run this, following is output as expected.



And when you connection your SQL Azure database in your SQL management studio and see employee table is created like below.



And here’s how data is inserted.



So it is very easy to use Connection Resiliency feature with Entity Framework. That’s it. Hope you like Stay tuned for more!

You can find complete source code for example on following location- https://github.com/dotnetjalps/EFConnectionResiliency