Test to see if Entity Framework is connected to something

Question

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?

Answer 1

Simplest (EF5+):

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;
        }
    }
}

Update for EF core 2+:

using (var dbContext = new MyEntityCollection()) {
    return dbContext.Database.CanConnect();
}

or (async):

using (var dbContext = new MyEntityCollection()) {
    return await dbContext.Database.CanConnectAsync();
}

Answer 2

Are you just wanting to see if the DB connection is valid. If so take a look at the objectcontext.databaseExists().

Answer 3

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;
            }
        }
    }

Answer 4

Shouldn't such infrastructure be provided on the network or database server layer? Manually handling connection to backup server looks strange. Moreover it is not possible without waiting for timeout because you must try to open connection to primary server first.

Connection itself is accessible on ObjectContext.Connection. This property should return EntityConnection instance which contains StoreConnection property holding connection to real DB. You can check state of this connection.

Answer 5

In my app user can change connection to database from UI. I use this code to quickly check connection with new connection data:

    public bool CheckAvailable(string sqlServerName, string login, string password)
    {
        var testConnectionString = GetConnectionString(sqlServerName, login, password);

        if (string.IsNullOrWhiteSpace(testConnectionString))
            return false;

        var result = false;

        try
        {
            var testContext = new EntityContext(testConnectionString);

            result = testContext.Database.Exists();
        }
        catch (Exception ex)
        {
            log.Error(exception);
        }

        return result;
    }


    private string GetConnectionString(string sqlServerName, string login, string password)
    {
        var sqlConnectionString = new SqlConnectionStringBuilder
                                 {
                                     DataSource = sqlServerName,
                                     InitialCatalog = ConfigurationProvider.DatabaseName,
                                     UserID = login,
                                     Password = password,
                                     MultipleActiveResultSets = true,
                                     ConnectTimeout = 2 // in seconds
                                 };

        var efConnectionString = new EntityConnectionStringBuilder
                                 {
                                     Provider = ProviderName,
                                     Metadata = ConnectionMetadata,
                                     ProviderConnectionString = sqlConnectionString.ToString()
                                 };

        return efConnectionString.ConnectionString;
    }

ConnectTimeout property of the SqlConnectionStringBuilder type is very important to perform the check lightening fast. If you know, that your database should be available and should response fast you can set this value to 1 (with SQL server installed locally it takes 100-200 milliseconds to check). Do not set 0, because it means no limit and should be avoided in connection strings.

Answer 6

I did an extension method to use with EntityFramework Core.

Here's the code:

using Microsoft.EntityFrameworkCore;
using System.Data.Common;

namespace TerminalInventory
{
    public static class ExtensionMethods
    {
        public static bool TestConnection(this DbContext context)
        {
            DbConnection conn = context.Database.GetDbConnection();

            try
            {
                conn.Open();   // Check the database connection

                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}

Now you just have to call:

if (!context.TestConnection())
{
    logger.LogInformation("No database connection. Check the connection string in settings.json. {0}", configuration["connectionString"]);

    return;
}