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();
