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()
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With