Create a table if it does not exist?

Question

Using Entity Framework Core, is there a way to create the table if it does not yet exist? Exception will throw even if EnsureCreated is called in the context:

DbSet<Ticker> Ticker { get; set }
Database.EnsureCreated();

Ticker.Add(...);
dbctx.SaveChanges(); <== exception

Results in exception:

System.Data.SqlClient.SqlException: Invalid object name 'Ticker'

Is there a way to create the table Ticker before data is inserted?

== EDIT==

This questions is not to create/migrate the entire database, the database always exist and most of its tables also exists, but some of the tables may not. So I just need create one or two tables in runtime.

Answer 1

In Entity framework Core (on version 2.2.4) you can use the following code in your DbContext to create tables in your database if they don't exist:

try
{
    var databaseCreator = (Database.GetService<IDatabaseCreator>() as RelationalDatabaseCreator);
    databaseCreator.CreateTables();
}
catch (System.Data.SqlClient.SqlException)
{
    //A SqlException will be thrown if tables already exist. So simply ignore it.
}

Database.EnsureCreated() doesn't create the schema (so your tables) when the database already exists. That's the reason why you get that exception. You can check that method's documentation.

PS: Make sure you catch the right exception if it changes in the new versions of Entity framework Core.

Answer 2

My guess is that your context is wrongly defined. Maybe you forgot to add the DbSet to your context implementation?

Below code is working perfectly, and I honestly prefer to EnsureCreated() in the constructor of the actual DBContext implementation.

internal class AZSQLDbContext : DbContext
{
    public AZSQLDbContext() {
        this.Database.EnsureCreated();
    }

    internal DbSet<TaskExecutionInformation> TaskExecutionInformation { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var dbUser = "your-user";
        var dbPW = "your-pw";
        optionsBuilder.UseSqlServer(
            $"Server=tcp:sample-sql.database.windows.net,1433;Initial Catalog=sample-db;Persist Security Info=False;User ID={dbUser};Password={dbPW};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
    }
}

TaskExecutionInformation is just a PoCo and could be anything. See below though if you need a bit of guidance.

public class TaskExecutionInformation
{
    public Guid Id { get; set; }
    public string Status { get; set; }
    public int Duration { get; set; }
}

Answer 3

In my case there was 2 applications using same database and those could create its own code-first tables, if they were missing. So my solution for that is following extension method used in startup on dbcontext:

using System;
using System.Linq;

using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Extensions
{
    internal static class DbContextExtensions
    {
        internal static void EnsureCreatingMissingTables<TDbContext>(this TDbContext dbContext) where TDbContext : DbContext
        {
            var type = typeof(TDbContext);
            var dbSetType = typeof(DbSet<>);

            var dbPropertyNames = type.GetProperties().Where(p => p.PropertyType.Name == dbSetType.Name)
                .Select(p => p.Name).ToArray();

            foreach (var entityName in dbPropertyNames)
            {
                CheckTableExistsAndCreateIfMissing(dbContext, entityName);
            }
        }

        private static void CheckTableExistsAndCreateIfMissing(DbContext dbContext, string entityName)
        {
            var defaultSchema = dbContext.Model.GetDefaultSchema();
            var tableName = string.IsNullOrWhiteSpace(defaultSchema) ? $"[{entityName}]" : $"[{defaultSchema}].[{entityName}]";

            try
            {
                _ = dbContext.Database.ExecuteSqlRaw($"SELECT TOP(1) * FROM {tableName}"); //Throws on missing table
            }
            catch (Exception)
            {
                var scriptStart = $"CREATE TABLE {tableName}";
                const string scriptEnd = "GO";
                var script = dbContext.Database.GenerateCreateScript();

                var tableScript = script.Split(scriptStart).Last().Split(scriptEnd);
                var first = $"{scriptStart} {tableScript.First()}";

                dbContext.Database.ExecuteSqlRaw(first);
                Log.Information($"Database table: '{tableName}' was created.");
            }
        }
    }
}

Answer 4

You have a few options here. The simplest is to do:

MyContext.Database.CreateIfNotExists();

Or, do it initialization style, by putting this in your context's constructor:

Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());

Both of these however require you to drop your schema manually every time you have modified your model and need to re-create the database. If you don't want to do that, you can use the following initialization instead:

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());

This will check your model against the database every time you run your program, and automatically drop and re-create the database if the model has been modified.

EDIT:

If you don't want to drop the database, and simply update it, then you can use the following initialization:

Database.SetInitializer<MyContext>(new MigrateDatabaseToLatestVersion<MyContext, Config>());