EF Core Database/Tables exists

Introduction

Learn how to determine if a database exists and if the required tables exist, which can be used in an application or a dotnet tool using EF Core.

Reasons for these checks

Before going to staging and/or production, the connection string must be correct, the database must exist, and the required tables must exist.

The code

• Each method is generic
• Rather than lump the code into one method, three are used so that a developer is not locked into all three checks.
• DatabaseExists checks if the database exists from the DbContext connection string.
• HasTables is a check to see if there are any tables in the database.
• TablesExist accepts a string array (params string[]) of table names and checks if they exists. In the example project, table names are read from a section in appsettings.json

"DatabaseTables": {
  "Customer": "Customer",
  "ContactTypes": "ContactTypes",
  "Genders": "Genders"
}

In both the console project and class project these methods are fully documented.

internal class DbContextHelpers
{

    public static bool DatabaseExists<TContext>(TContext context) where TContext : DbContext
    {
        if (context.GetService<IDatabaseCreator>() is not RelationalDatabaseCreator databaseCreator)
        {
            throw new InvalidOperationException("Database creator service is not available.");
        }

        return databaseCreator.Exists();
    }
    public static bool HasTables<TContext>(TContext context) where TContext : DbContext
    {
        if (context.GetService<IRelationalDatabaseCreator>() is not RelationalDatabaseCreator databaseCreator)
        {
            throw new InvalidOperationException("Database creator service is not available.");
        }

        return databaseCreator.HasTables();
    }


    public static bool TablesExist(DbContext context, params string[] tableNames)
    {

        var existingTables = new HashSet<string>(
            context.Model.GetEntityTypes()
                .Select(t => t.GetTableName())
                .Where(t => !string.IsNullOrEmpty(t)) 
                .Cast<string>() 
        );

        return existingTables.IsSupersetOf(tableNames);
    }

    public static bool FullCheck<TContext>(TContext context, params string[] tableNames) where TContext : DbContext 
        => DatabaseExists(context) && HasTables(context) && TablesExist(context, tableNames);
}

Example

We create an instance of the DbContext and get table names to check from appsettings.json.

Using DbContextHelpers.FullCheck(context, tableNames) check if the database exists and required tables exists. If FullCheck returns true, perform a query and present the results to the console window while returning false from FullCheck displays an error message to the console window.

internal partial class Program
{
    static async Task Main(string[] args)
    {

        await Setup();

        await using var context = new Context();

        var tableNames = ApplicationConfiguration.GetTableNames();

        if (DbContextHelpers.FullCheck(context, tableNames))
        {
            var ops = new DataOperations(context);
            var customers = ops.GetCustomers();

            AnsiConsole.MarkupLine(
                ObjectDumper.Dump(customers)
                    .Replace("{Customer}", "{[cyan]Customer[/]}")
                    .Replace("{ContactType}", "{[yellow]ContactType[/]}")
                    .Replace("{Gender}", "{[yellow]Gender[/]}")
                    .Replace("null --> Circular reference detected", "") // no need to show this
                    );
        }
        else
        {
            AnsiConsole.MarkupLine("[red]Create the database and run the script under[/][cyan] Data scripts[/]");
        }

        // Allows a developer to see the log file before the console closes e.g. in VS Code
        await Log.CloseAndFlushAsync();

        ExitPrompt();

    }
}

Project setup

First, build and run to receive an error message. Next, create the database CustomerDatabase. SQLEXPRESS, or if using a named instance of SQL Server, make sure to change the connection string in appsetings.json.

Under the folder Data scripts run the script CreateAndPopulate.sql.

Run the project again to see a list of customers.

Modifications / other uses

Rather than returning Booleans, a developer using these methods in an application could throw exceptions. As coded, used in ValidateOnStart in an ASP.Net Core project.

Get the row count for each table

The best way to achieve this is to write an SQL statement, get the connection string from an instance of the DbContext, and use the NuGet package Dapper, as shown below.

class EntityQueryHelpers
{
    public static async Task<List<TableInfo>> GetTableRowCountsAsync()
    {
        await using var context = new Context();
        await using var connection = context.Database.GetDbConnection();
        const string sql = """
                           SELECT 
                               TableSchema = s.name,
                               Name = t.name,
                               [RowCount] = p.rows
                           FROM sys.tables t
                           INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                           INNER JOIN sys.indexes i ON t.object_id = i.object_id
                           INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
                           WHERE t.is_ms_shipped = 0
                           GROUP BY t.name, s.name, p.rows
                           ORDER BY s.name, t.name;
                           """;

        return (await connection.QueryAsync<TableInfo>(sql)).AsList();
    }
}

public class TableInfo
{
    public string TableSchema { get; set; }
    public string Name { get; set; }
    public long RowCount { get; set; }
}

Another version for specific tables which in this version reads the connection string from appsettings.json.

public static async Task<List<TableInfo>> GetTableRowCountsAsync(params string[] tableNames)
{

    /*
     * Config is defined in GlobalUsings.cs
     * ConnectionStrings is defined in ConsoleConfigurationLibrary
     */
    var connectionString = Config.Configuration.JsonRoot()
        .GetSection(nameof(ConnectionStrings))
        .GetValue<string>(nameof(ConnectionStrings.MainConnection));

    IDbConnection cn = new SqlConnection(connectionString);

    const string sql =
        """
        SELECT 
            TableSchema = s.name,
            Name = t.name,
            [RowCount] = p.rows
        FROM sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i ON t.object_id = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
        WHERE t.is_ms_shipped = 0
        AND t.name IN @TableNames
        GROUP BY t.name, s.name, p.rows
        ORDER BY s.name, t.name;
        """;

    return (await cn.QueryAsync<TableInfo>(sql, new { TableNames = tableNames })).ToList();
}

Source code

Console project Source code Class project Source code

Summary

Methods for EF Core have been presented to assist with validating a database and tables, which can help determine if an environment is properly set up. What is out of scope is checking if the required records are in the tables.