MySQL Failover with Entity Framework

I recently started using the Entity Framework 4.1, and I absolutely love it! It has taken away several of the headaches I've faced; for example, when I have multiple databases with identical schemas, I no longer need to keep separate stored procedures for them - the queries are now handled directly in my code. In addition, I now have strong typing without having to map each DataRow column to a class property. However, I ran into a small snag when it came to server failover. The app I'm working on has 4 MySQL servers - 3 slaves, 1 master. Two of the slaves are in a different site, so they are used only if the main read and master servers go offline. In the old version of the app, I used a custom solution to direct the queries to the proper servers. For example, instead of using this code:

var datatable = new DataTable();
using(var command = new MySqlCommand("Render_GetGraphics", new MySqlConnection("connectionstring")))
{
    command.Parameters.AddRange(new MySqlParameterCollection[]
                                    {
                                        new MySqlParameter("_clientId", _clientId),
                                        new MySqlParameter("_otherId", _otherId),
                                    });
    datatable.Load(command.ExecuteReader());
}
var graphics = datatable.Rows.Cast<DataRow>().Select(
        r =>
        new
        {
            Name = (string)r["Name"],
            Action = (GraphicApplicationAction)r["ActionID"],
            AlwaysApply = Convert.ToBoolean(r["AlwaysApply"]),
            PageSettings = (string)r["PageSettings"],
            IsEnabled = Convert.ToBoolean(r["IsEnabled"]),
            Image = r.IsNull("Image") ? null : (byte[])r["Image"],
            Type = r.IsNull("Type") ? "" : (string)r["Type"]
        })
    .ToList();

I used this code:

public ConnectionInfo dbConnection = new ConnectionInfo(null, new [] { "Server1", "Server2" }, "dbname", "dbuser",
                                                                   "dbpassword");
var graphics = new DbCommand(dbConnection, "Render_GetGraphics",
                                         new[]
                                             {
                                                 new MySqlParameter("_clientId", _clientId),
                                                 new MySqlParameter("_otherId", _otherId),
                                             })
                .RetrieveDataRows().Select(
                    r =>
                    new
                        {
                            Name = (string)r["Name"],
                            Action = (GraphicApplicationAction)r["ActionID"],
                            AlwaysApply = Convert.ToBoolean(r["AlwaysApply"]),
                            PageSettings = (string)r["PageSettings"],
                            IsEnabled = Convert.ToBoolean(r["IsEnabled"]),
                            Image = r.IsNull("Image") ? null : (byte[])r["Image"],
                            Type = r.IsNull("Type") ? "" : (string)r["Type"]
                        })
                .ToList();

The RetrieveDataRows() method had error handling so that if it had trouble connecting to one server, it could try the next best option. With the EntityFramework, I no longer had that option, as my old model would have prevented me from using LINQ efficiently. So I spent this morning working on it, and came up with these two classes: EntityFrameworkConnection.cs

using System;
using System.Collections.Generic;
using System.Data.EntityClient;
using System.Linq;
using MySql.Data.MySqlClient;

namespace myApp
{
    public class EntityFrameworkConnection
    {
        # region Fields

        readonly List<string> _servers = new List<string>();
        private readonly string _initialConnectionString;
        # endregion

        # region Properties

        public string ConnectionString
        {
            get
            {
                lock (_servers)
                {
                    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(_initialConnectionString);
                    entityBuilder.ProviderConnectionString = String.Format(entityBuilder.ProviderConnectionString, "localhost");
                    MySqlConnectionStringBuilder sqlConnectionBuilder = new MySqlConnectionStringBuilder(entityBuilder.ProviderConnectionString) { Server = _servers.First() };
                    entityBuilder.ProviderConnectionString = sqlConnectionBuilder.ToString();

                    return entityBuilder.ToString();
                }
            }
        }

        public List<string> Servers { get { lock (_servers) return _servers; } }

        # endregion

        # region Constructors

        public EntityFrameworkConnection(string connectionString)
        {
            _initialConnectionString = connectionString;
        }

        # endregion

        # region Public Methods

        public bool GoToNextServer()
        {
            lock (_servers)
                if (_servers.Any())
                    _servers.RemoveAt(0);

            return _servers.Any();
        }

        # endregion
    }
}

The final version of this class will be able to update the list of servers from the database. This version has to have the servers manually specified. After a failed connection to a server, that server is removed from the list of available servers, and the next server in the List<> is used. AdminDb.cs

using System;
using System.Configuration;
using System.Linq;
using MySql.Data.MySqlClient;

namespace myApp
{
    public class AdminDb
    {
        public delegate T QueryDelegate<T>(Models.adminEntities db);

        # region Instance Stuff
        private readonly EntityFrameworkConnection _dbConnection;
        //public EntityFrameworkConnection DbConnection { get { return _dbConnection; } }
        
        private AdminDb()
        {
            _dbConnection = new EntityFrameworkConnection(
                ConfigurationManager.ConnectionStrings["adminEntities"].ConnectionString);
        }

        # endregion 

        # region Static Stuff
        private static AdminDb _manager;

        public static EntityFrameworkConnection Connection { get { return _manager._dbConnection; } }
        
        static AdminDb()
        {
            _manager = new AdminDb();
            // Manually add the servers
            Connection.Servers.Add("localhost");
        }

        public static T RunQuery<T>(QueryDelegate<T> del, bool isReadonly = true)
        {
            while (_manager._dbConnection.Servers.Any())
            {
                try
                {
                    using (
                        var db =
                            new Models.adminEntities(
                                _manager._dbConnection.ConnectionString))
                    {
                        return del(db);
                    }
                }
                catch (Exception ex)
                {
                    if (ex.InnerException is MySqlException &&
                        ex.Message == "Unable to connect to any of the specified MySQL hosts.")
                        if (_manager._dbConnection.GoToNextServer())
                            continue;

                    throw;
                }
            }

            return default(T);
        }
        # endregion
    }
}

This class acts as a layer between Entity Framework and my app, enabling me to easily do things like failover while still keeping the simplicity of EntityFramework. For each EF model I simply have to create a version of this class that uses the proper connection string and model. Example time:

 
// Standard entity framework call:
using (var db = new estatements_adminEntities())
{
    var usersList = db.users.Include(u => u.clientgroup).Include(u => u.client).ToList();
    // do stuff..
}

// The same call using AdminDb:
var usersList = AdminDb.RunQuery(db => db.users.Include(u => u.clientgroup).Include(u => u.client)).ToList();