Entity Framework - How to check if table exists?

Question

I'm using the Entity Framework with Code First approach. The base class DbContext has functions to create and delete the database as well as to check for its existence.

I want to check if a special table (entity) is existing or not. Is it possible with an framework implementation or do I need to write custom methods? If I need to write my own implementation, what would be the most generic approach to do that?

Thanks for any help.

Answer 1

If you need to check existence of the table you must call custom SQL code:

bool exists = context.Database
                     .SqlQuery<int?>(@"
                         SELECT 1 FROM sys.tables AS T
                         INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                         WHERE S.Name = 'SchemaName' AND T.Name = 'TableName'")
                     .SingleOrDefault() != null;

Table name is defined by default as the name of DbSet exposed on your derived context but the default name can be overriden either by fluent API's ToTable method or Table data annotation.

Doing this in the generic way is not something supposed in code first approach. That will require browsing metadata and manually explore to which table is the entity mapped - this can be pretty complex because entity can be mapped to multiple tables. Code first doesn't offer access to metadata. You must convert DbContext to ObjectContext and browse MetadataWorkspace.

Edit:

To convert DbContext to ObjectContext use this:

ObjectContext objContext = ((IObjectContextAdapter)dbContext).ObjectContext;