How can I deal with optional tables using Entity Framework?

Question

I'm using Entity Framework 6 to access a database. There are several slightly different versions of this database's schema that have been deployed over time. How can I access the database using EF when parts of the schema might be "missing", so to speak.

For example, a Tank table was added to the database at some point, so I'd like to be able to add this to my context:

public DbSet<Tank> Tanks { get; set; }

and then perform queries against it, like so:

var tanks = context.Tanks.Where( ... ).ToList()

but obviously this throws an exception when running against versions of the schema that don't have this table.

Is there any way of dealing with this cleanly, other than catching the exception and checking for the appropriate SQL error code? Preferably some way of getting EF to check whether the table exists before I run the query? I couldn't see anything on the DbContext class that would help, and an internet search didn't really turn up anything either. I suppose I could drop down to raw SQL to do it.

---

I should probably point out that there is no "schema version number" or similar column on any of the tables that would help to determine which version of the database is being accessed. Maybe this would have been a good idea (hindsight and all that) but it never happened, and retrofitting something like this to existing installations would be difficult.

Also, in my particular scenario, I am only reading data from an existing database: I do not need to write to the database using EF, nor do I need to get EF to build the schema for me.

---

EDIT: I just found this SO question: Entity Framework - How to check if table exists?, but the answer basically recommended performing a SQL query. That was from 2011 though, I'm wondering if EF has introduced anything cleaner in later versions.

Answer 1

The Entity Framework doesn't seem to support this, so as I see it there are two possible approaches:

Check whether the table exists using raw SQL

public static class DbContextExtensions
{
    public static bool HasTableNamed(
        this DbContext context, string table, string inSchema = "dbo")
    {
        string sql = @"SELECT CASE WHEN EXISTS
            (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA=@p0 AND TABLE_NAME=@p1) THEN 1 ELSE 0 END";

        return context.Database.SqlQuery<int>(sql, inSchema, table).Single() == 1;
    }
}

Used like so:

MyContext db = ...   // class derived from DbContext

...

if (db.HasTableNamed("MyOptionalTable"))
{
    // the table exists in the database
}
else
{
    // the table DOES NOT exist in the database
}

Attempt to run the EF query anyway, and handle the resulting exception

public static class EntityCommandExecutionExceptionExtensions
{
    public static bool IsInvalidObjectNameError(
        this EntityCommandExecutionException exception)
    {
        const int InvalidObjectName = 208;

        var sqlException = exception.InnerException as SqlException;
        if (sqlException == null)
        {
            return false;
        }

        return sqlException.Errors.Cast<SqlError>().Any(
            error => error.Number == InvalidObjectName);
    }
}

Used like so:

try
{
    // run some EF query
}
catch (EntityCommandExecutionException exception)
{
    if (!exception.IsInvalidObjectNameError())
    {
        throw;
    }

    // one of the objects referenced by the query is missing
}