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.

Why has this received a downvote? It's a well-explained question which shows effort in OP trying things himself already. — , Jun 30 '15 at 14:19
OP, I'm not going to post this as the answer because I'm not 100% sure on my approach to data layer when using EF - but since EF is effectively for mapping data to objects, I think it should have a 'consistent understanding' of what that data is that it is mapping - rather than having to adapt to a changing schema. i.e. If you have an updated database (or not), you need to consider whether that EF config is redundant now or not. I think it is. Why would your database change but the data layer or not? — , Jun 30 '15 at 14:21
I would second @JayMee, If there is no need to uncover this during runtime I would go for conditional compiles. — Mario The Spoon, Jun 30 '15 at 14:23
Would a different data-layer altogether be too drastic? Too many conditions and adaptations of a database affects the original mapping adversely. I'm not sure I'd want all versions to cater for all others. — , Jun 30 '15 at 14:30
@JayMe I can't conditionally compile the code in this case: it needs to be a single executable that will deal with the varying schema versions. — Steven Rands, Jun 30 '15 at 14:30
@StevenRands - maybe you're falling victim to the XY problem? Ask yourself why you need to deal with variations. Maybe the problem should be tackled earlier on in your architecture. Furthermore, if you have `Tank` in your solution - now what? You won't just disregard it, it's likely you'll want to do something with a `Tank` object, but only where it's available. So your conditions grow in complexity, and you're more prone to some nightmare maintenance. +1, it's a good question, I just think you should rethink your approach a little bit. — , Jun 30 '15 at 14:30
@JayMee Unfortunately I need to deal with the variations because they already exist! :O) This isn't a hypothetical question. There are multiple versions of this schema deployed already at different sites. With hindsight a schema version number or similar would have been very useful, but as things stand I don't have anything like that to rely on. — Steven Rands, Jun 30 '15 at 14:36
So then do you not need multiple version of the solution? As I said, surely you're going to want to (conditionally) do something with `Tanks`? — , Jun 30 '15 at 14:37
@JayMee We maintain a single version of the codebase, then write the data layer to cope with the different schemas used by the various installations. This approach makes some things easier, and some things more difficult. Whether this is a "correct" approach is really a matter of opinion, and also requires domain knowledge that I haven't detailed in the question. I do understand the points you are raising though. — Steven Rands, Jun 30 '15 at 14:47

score 1 · Answer 1 · answered Jul 01 '15 at 09:33

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
}

I don't see people checking SqlException.Number often. Omitting that check is a mistake most of the time. I'm impressed. — usr, Jul 01 '15 at 11:37

How can I deal with optional tables using Entity Framework?

1 Answers1

Check whether the table exists using raw SQL

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