Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check if a table exists within a database using LINQ

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

like image 827
Manatherin Avatar asked Mar 07 '11 10:03

Manatherin


2 Answers

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();
like image 152
eddiegroves Avatar answered Sep 30 '22 06:09

eddiegroves


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.

like image 45
Ladislav Mrnka Avatar answered Sep 30 '22 07:09

Ladislav Mrnka