Btukfyl

In Entity Framework the existence of a table can be checked this way:

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;

I am using EF Core 2.1 and the method SqlQuery does not exist.

What would be the right way of checking whether or not a table exists? Ideally without trying to access the table and assuming it doesn't exist if an exception is thrown.

EDIT: My final implementation

public bool TableExists(string tableName)

{

    return TableExists("dbo", tableName);

}



public bool TableExists(string schema, string tableName)

{

    var connection = Context.Database.GetDbConnection();



    if (connection.State.Equals(ConnectionState.Closed))

        connection.Open();



    using (var command = connection.CreateCommand())

    {

        command.CommandText = @"

            SELECT 1 FROM INFORMATION_SCHEMA.TABLES 

            WHERE TABLE_SCHEMA = @Schema

            AND TABLE_NAME = @TableName";



        var schemaParam = command.CreateParameter();

        schemaParam.ParameterName = "@Schema";

        schemaParam.Value = schema;

        command.Parameters.Add(schemaParam);



        var tableNameParam = command.CreateParameter();

        tableNameParam.ParameterName = "@TableName";

        tableNameParam.Value = tableName;

        command.Parameters.Add(tableNameParam);



        return command.ExecuteScalar() != null;

    }

}

There is ExecuteSqlCommand.

context.Database.ExecuteSqlCommand("...")

However, it's limited to returning an integer indicating how many rows were affected. No rows get affected if you're doing a SELECT, so it doesn't really work for what you want.

There is also FromSql, but that only works on tables, not at the database level:

context.TableName.FromSql("SELECT ...")

For what you're doing, a better option is to get the DbConnection from EF, and create your own DbCommand. This returns what you would expect:

var conn = context.Database.GetDbConnection();

if (conn.State.Equals(ConnectionState.Closed)) await conn.OpenAsync();

using (var command = conn.CreateCommand()) {

    command.CommandText = @"

    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'";

    var exists = await command.ExecuteScalarAsync() != null;

}

A word of warning here: Don't put the DbConnection you get from GetDbConnection() in a using statement, or close it when you're done. That connection is used for the life of that DbContext instance. So if you close it, any later requests made by EF will fail. It's also possible that it was already opened before your code, which is why I put a test in there to see if it's closed before calling OpenAsync().