When you create a new EntityCollection object, the connection doesn't attempt to open the database until you try and do something with that collection. I need to determine whether or not an Entity collection has a valid connection or not, and I can't find an efficient method of doing it.
Currently I've got this in my code:
var db = new MyEntityCollection();
try
{
var checkworking = from c in db.Customers select c;
}
catch
{
ConnectToBackUp();
}
Which is not only horrible code, but very slow since it waits an age to determine whether or not the connection is active before throwing an exception.
I know I can control how long it waits before giving up by using ConnectionTimeout but that's just another ugly hack that makes a bad situation worse.
Surely there's a better way of doing this?
Solved this by going around the houses a bit and building a new connection string to test with ADO. Still involves using a try catch but it's a lot faster:
private bool TestConnection()
{
EntityConnectionStringBuilder b = new EntityConnectionStringBuilder();
ConnectionStringSettings entityConString = ConfigurationManager.ConnectionStrings["MyEntityConnectionString"];
b.ConnectionString = entityConString.ConnectionString;
string providerConnectionString = b.ProviderConnectionString;
SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder();
conStringBuilder.ConnectionString = providerConnectionString;
conStringBuilder.ConnectTimeout = 1;
string constr = conStringBuilder.ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
try
{
conn.Open();
return true;
}
catch
{
return false;
}
}
}
Simplest:
private bool TestConnection()
{
var db = new MyEntityCollection();
int oldTimeOut = db.CommandTimeout;
try
{
db.CommandTimeout = 1;
db.Connection.Open(); // check the database connection
return true;
}
catch
{
return false;
}
finally
{
db.CommandTimeout = oldTimeOut;
}
}
Update for EF6:
using System.Data.Common;
...
public bool TestConnection() {
using (var db = new MyEntityCollection()) {
DbConnection conn = db.Database.Connection;
try {
conn.Open(); // check the database connection
return true;
}
catch {
return false;
}
}
}