auto create database in Entity Framework Core

Question

My application which is being ported to .NET core will use the new EF Core with SQLite. I want to automatically create the database and table structures when the app is first run. According to the EF core documentation this is done using manual commands

dotnet ef migrations add MyFirstMigration

dotnet ef database update

However I don't want the end user to enter these commands and would prefer to have the app create and setup the database for first use. For EF 6 there is functionality like

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

But in EF Core these don't seem to exist. I can't find any examples or documentation on something equivalent for EF core and it is not mentioned in the list of missing features in the EF core documentation. I have the model classes setup already so I could write some code to initialize the database based on the models but it would be heaps easier if the framework did this automatically. I don't want to auto build the model or migrate, just create the table structures on a new database.

Am I missing something here or is auto create table function missing in EF core?

Answer 1

If you have created the migrations, you could execute them in the Startup.cs as follows.

 public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
 {
      using (var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
      {
            var context = serviceScope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
            context.Database.Migrate();
      }
      
      ...

This will create the database and the tables using your added migrations.

If you're not using Entity Framework Migrations, and instead just need your DbContext model created exactly as it is in your context class at first run, then you can use:

 public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
 {
      using (var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
      {
            var context = serviceScope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
            context.Database.EnsureCreated();
      }
      
      ...

Instead.

If you need to delete your database prior to making sure it's created, call:

            context.Database.EnsureDeleted();

Just before you call EnsureCreated()

Adapted from: http://docs.identityserver.io/en/latest/quickstarts/7_entity_framework.html?highlight=entity

Answer 2

My answer is very similar to Ricardo's answer, but I feel that my approach is a little more straightforward simply because there is so much going on in his using function that I'm not even sure how exactly it works on a lower level.

So for those who want a simple and clean solution that creates a database for you where you know exactly what is happening under the hood, this is for you:

public Startup(IHostingEnvironment env)
{
    using (var client = new TargetsContext())
    {
        client.Database.EnsureCreated();
    }
}

This pretty much means that within the DbContext that you created (in this case, mine is called TargetsContext), you can use an instance of the DbContext to ensure that the tables defined with in the class are created when Startup.cs is run in your application.

Answer 3

If you get the context via the parameter list of Configure in Startup.cs, You can instead do this:

public void Configure(IApplicationBuilder app, IHostingEnvironment env,  LoggerFactory loggerFactory,
    ApplicationDbContext context)
 {
      context.Database.Migrate();
      ...

Answer 4

For EF Core 2.0+ I had to take a different approach because they changed the API. As of March 2019 Microsoft recommends you put your database migration code in your application entry class but outside of the WebHost build code.

public class Program
{
    public static void Main(string[] args)
    {
        var host = CreateWebHostBuilder(args).Build();
        using (var serviceScope = host.Services.CreateScope())
        {
            var context = serviceScope.ServiceProvider.GetRequiredService<PersonContext>();
            context.Database.Migrate();
        }
        host.Run();
    }

    public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
            .UseStartup<Startup>();
}

Answer 5

If you haven't created migrations, there are 2 options

1.create the database and tables from application Main:

var context = services.GetRequiredService<YourRepository>();
context.Database.EnsureCreated();

2.create the tables if the database already exists:

var context = services.GetRequiredService<YourRepository>();
context.Database.EnsureCreated();
RelationalDatabaseCreator databaseCreator =
(RelationalDatabaseCreator)context.Database.GetService<IDatabaseCreator>();
databaseCreator.CreateTables();

Thanks to Bubi's answer

Answer 6

If you want both of EnsureCreated and Migrate use this code:

     using (var context = new YourDbContext())
            {
                if (context.Database.EnsureCreated())
                {
                    //auto migration when database created first time

                    //add migration history table

                    string createEFMigrationsHistoryCommand = $@"
USE [{context.Database.GetDbConnection().Database}];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE [dbo].[__EFMigrationsHistory](
    [MigrationId] [nvarchar](150) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY CLUSTERED 
(
    [MigrationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
";
                    context.Database.ExecuteSqlRaw(createEFMigrationsHistoryCommand);

                    //insert all of migrations
                    var dbAssebmly = context.GetType().GetAssembly();
                    foreach (var item in dbAssebmly.GetTypes())
                    {
                        if (item.BaseType == typeof(Migration))
                        {
                            string migrationName = item.GetCustomAttributes<MigrationAttribute>().First().Id;
                            var version = typeof(Migration).Assembly.GetName().Version;
                            string efVersion = $"{version.Major}.{version.Minor}.{version.Build}";
                            context.Database.ExecuteSqlRaw("INSERT INTO __EFMigrationsHistory(MigrationId,ProductVersion) VALUES ({0},{1})", migrationName, efVersion);
                        }
                    }
                }
                context.Database.Migrate();
            }