Check if a table exists within a database using LINQ

Question

We have a database that has been deployed to various clients. We are currently introducing a optional new feature that, to be used, will require the customers who want the feature to have a new table added to the existing database.

As we are rolling out a new piece of software that will have to interact with versions of the database both with and without the new table (and as we don't want 2 versions one for customers who have the new table and one for ones who don't) we were wondering if it is possible to programmatically determine (with entity framework) whether a table exists in the database (I can try to access the table and have it throw a exception but was wondering if there was a built in function to do this)

Thanks

Edit: Given that people are telling me i should be using a config file not checking with EF can anyone give me guidence on how to check the config file with, for example, a custom data annotations for a mvc controller. Something like:

[Boolean(Properties.Settings.Default.TableExists)]
public class NamedController : Controller

Which throws a page not found if false?

Edit 2: With the Suggestions given by people to use the config settings i ended up with the following solution

App settings to set whether the table exists

<appSettings>
    <add key="tableExists" value="True"/>
</appSettings>

a custom data annotation to say whether to allow access to controller

[AuthoriseIfTableExistsIsTrue]
public class NamedController : Controller

the code for the custom authorise

public class AuthoriseIfTableExistsIsTrue : AuthorizeAttribute
{
    private readonly bool _tableExists;

    public AuthoriseIfTableExistsIsTrue()
    {
        _tableExists = string.Equals(bool.TrueString, ConfigurationManager.AppSettings["tableExists"], StringComparison.InvariantCultureIgnoreCase);
    }

    public AuthoriseIfTableExistsIsTrue(bool authorise)
    {
        _tableExists = authorise;
    }

    protected override bool AuthorizeCore(HttpContextBase httpContext)
    {
        if (_tableExists)
            return base.AuthorizeCore(httpContext);
        else
            throw new HttpException(404, "HTTP/1.1 404 Not Found");
    }
}

Thanks everyone for the help and telling me not to use EF for this and use config setting instead

possible duplicate http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists — Kris Ivanov, Mar 07 '11 at 10:32
I would NOT use LINQ for this. Of course you could map database schema metadata (I wouldn't). — Jaroslav Jandek, Mar 07 '11 at 10:36
@K Ivanov not really a duplicate, i was asking whether it could be done with Entity framework, i already knew how to do it with SQL — Manatherin, Mar 08 '11 at 11:18

score 8 · Accepted Answer · answered Mar 07 '11 at 10:53

8

A much better option would be to store the version differences as configuration. This could be stored in the database itself, a configuration file or even web.config.

Otherwise you'll end up with messy code like:

int result = entity.ExecuteStoreQuery<int>(@"
    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TableName') 
        SELECT 1
    ELSE
        SELECT 0
    ").SingleOrDefault();

answered Mar 07 '11 at 10:53

Eddie Groves

33,851
14
47
48

If I put whether it exists in a config file do i just add the EF normally and just ignore it if the table doesn't exist? – Manatherin Mar 08 '11 at 11:16

score 2 · Answer 2 · answered Mar 07 '11 at 10:34

The only possible ways are

Query table and get exception
Use native SQL to query system views and look for that table - in EFv4 you can execute query directly from ObjectContext by calling ExecuteStoreQuery.

Your entity model will still have this table so in my opinion you should simply ship your DB with that table and in application code handle if feature is allowed or not (table will not be used but will be in DB).

If you want to make modular system then whole your feature (including application code) should not be present when client don't want to use it.

Check if a table exists within a database using LINQ

2 Answers2

Linked