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.

Ladislav Mrnka · Accepted Answer · 2011-05-23T18:37:04.020

43

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;

edited May 23 '11 at 18:37

answered May 23 '11 at 18:07

Ladislav Mrnka

360,892
59
660
670

How can I convert DbContext to ObjectContext? I've seend diffrent approaches to check for the existence of a table. Is there any difference between your solutin an a) SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND TABLE_NAME = 'TheTable')) or b) SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U')? Could you explain advantages/disadvantages? I would prefer to use a most standard conform way. – 0xbadf00d May 23 '11 at 18:25
Generally all these SQL selects query the same information - that is just different way to get the same result. – Ladislav Mrnka May 23 '11 at 18:29
What does type in (N'U') mean? Your version does ignore the table schema, right? – 0xbadf00d May 23 '11 at 18:32
I modified query to work with schema as well. Here you have list of types: http://msdn.microsoft.com/en-us/library/ms190324.aspx `U` means user defined table. – Ladislav Mrnka May 23 '11 at 18:38
This doesn't work for me (maybe due to MS changing SQL server?). All my `S.Name` are "dbo", so there are no results. – Steve Smith Sep 12 '18 at 15:27

Dmitrii Polianskii · Answer 2 · 2016-02-16T08:35:52.913

6

I can't add comment to previous post. I'm using SQL Compact and I don't know schema of the table. I'm using this code to check for table It's pretty the same that in previous post but It works for any table.

    /// <summary>
    /// Check if data table is exist in application
    /// </summary>
    /// <typeparam name="T">Class of data table to check</typeparam>
    /// <param name="db">DB Object</param>
    public static bool CheckTableExists<T>(this ModelLocker db) where T : class
    {
        try
        {
            db.Set<T>().Count();
            return true;

        }
        catch (Exception)
        {
            return false;
        }
    }

edited Feb 16 '16 at 08:35

answered Feb 16 '16 at 07:59

Dmitrii Polianskii

565
1
8
21

9

catching any random exception doesn't necessarily mean the table doesn't exist. – Ben Mar 28 '18 at 14:13
2

Also, using Any may improve the query in case it exists – Ori Nachum Oct 29 '19 at 08:45

philu · Answer 3 · 2018-10-14T07:54:14.487

4

An alternative method; it's not as efficient as Ladislav's, but it's not tied to SQL Server (edited to add Where clause to address performance issue):

bool CheckTableExists()
{
    try
    {
        context.YourTable.Where(s => s.<yourKeyField> = <impossible value>).Count();
        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

edited Oct 14 '18 at 07:54

answered Mar 20 '15 at 05:27

philu

795
1
8
17

5

Dont use exceptions for flow control, its an anti pattern – David Oct 11 '18 at 14:58
Agree Exceptions are used only for 'exceptions' not to normal operations. – FlyingV Feb 20 '19 at 19:32
It might be that the exception is only ever used once for a check. I can't see any harm if usage is one-time. – 27k1 Apr 06 '22 at 07:40

score 2 · Answer 4 · answered Jan 27 '17 at 17:34

2

Assumption: SQL Server

Catching any old exception when querying the DbSet does not mean the table does not exist.

Querying a DbSet where the table does not exist will throw an EntityCommandExecutionException with an inner exception of type SqlException. That inner exception has an ErrorNumber property.

Error number 208 reads (source):

Invalid object name '%.*ls'.

answered Jan 27 '17 at 17:34

Aaron Hudon

5,280
4
53
60

The property is actually named Number. – Dark Daskin Mar 22 '18 at 18:40

score 2 · Answer 5 · answered Aug 25 '18 at 09:08

I think following code is a little bit more understandable.

using(YourDbEntities db = new YourDbEntities()) 
{
  bool IsExists = db.Database
   .SqlQuery <int?> (@"
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = '" + yourTableName + "'
    ")
    .FirstOrDefault() > 0;

    return IsExists;
}

Miti Razvan · Answer 6 · 2020-02-19T10:13:11.830

In EF Core, I have added the TableExists method as a extension method for the DbContext class. Here is my solution using Dapper.

using System.Linq;
using Dapper;
using Microsoft.EntityFrameworkCore;

public static class DbContextsExtensions
{
    public static bool TableExists(this DbContext dbContext, string tableName)
    {
        var sqlQ = $"SELECT COUNT(*) as Count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";

        var conn = dbContext.Database.GetDbConnection();
        {
            if (conn != null)
            {
                // Query - method extension provided by Dapper library
                var count = conn.Query<int>(sqlQ).FirstOrDefault();

                return (count > 0);                    
            }
        }
        return false;
    }
}

And here is an usage example:

if(context != null && context.TableExists("AppSettings"))
{
   // do something;
}

Hope this help other people.

But how can you access DBContext in Migration ? – HGMamaci Jun 12 '21 at 17:57 — HGMamaci, Jun 12 '21 at 17:57

score 0 · Answer 7 · answered Feb 09 '23 at 15:25

Here's an extension method for doing this in EF Core (without Dapper):

public static async Task<bool> TableExists(this DbContext context, string tableName)
{
    var connection = (SqlConnection)context.Database.GetDbConnection();
    if (connection.State != ConnectionState.Open)
    {
        await connection.OpenAsync();
    }

    await using var command = connection.CreateCommand();
    command.CommandText = $"SELECT COUNT(*) FROM sys.tables WHERE name = '{tableName}'";
    var result = await command.ExecuteScalarAsync();
    var count = (int)result;
    return count <= 0;
}

score -2 · Answer 8 · answered May 15 '22 at 06:31

-2

this code create all tables automatic if dose not exist

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            var x = modelBuilder.RegisterEntityType;
        }

answered May 15 '22 at 06:31

Mohammad

21
6

That's not the question. – Gert Arnold May 15 '22 at 15:38
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 16 '22 at 00:18

Entity Framework - How to check if table exists?

8 Answers8

Linked