How to auto create database on first run?

Question

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

dotnet ef migrations add MyFirstMigration
dotnet ef database update

I don't want end user to enter these but prefer the app to create and setup the database on first use. For EF 6 there is :

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

But I can't find any equivalent for EF Core. I have the model classes already so I could write code to initialize the database based on the models but it would be easier if the framework did this automatically. I don't want to auto build the model or migrate, just create the tables in a new database.

Is an auto create table function missing from 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

• deandob: I'm still pretty new to EF, I created the classes that define the data structures using EF 6 code first "create from database" from visual studio using a current database file. I then cut/pasted these into the new dotnet core VS solution - so I guess these are not migrations. Does that mean I have to create a migrations file before the above code can be used?
• Ricardo Fontana: I`m sorry, I did a mistake in my answer, if you are not using migrations, can use the command context.Database.EnsureCreated()/EnsureDeleted(), more details in blogs.msdn.microsoft.com/dotnet/2016/09/29/…
• Lars Udengaard: What if you need both solutions? We have just ported our App to UWP and started using EF Core, which means some users need the DB to be created from scratch, while others already have the DB. Is there some way to ensure that the first migration only creates the initial tables if they do not already exist? Your answer doesn't seem to cover this or am i missing something?

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.

• Thomas Schneiter: Just as an information from here : EnsureCreated totally bypasses migrations and just creates the schema for you, you can't mix this with migrations. EnsureCreated is designed for testing or rapid prototyping where you are ok with dropping and re-creating the database each time. If you are using migrations and want to have them automatically applied on app start, then you can use context.Database.Migrate() instead.
• ctrl-z pls: Just wanted to note that I just tried pasting this in my Startup file and VS2019 informed me that IHostingEnvironment is now deprecated and the recommended alternative is Microsoft.AspNetCore.Hosting.IWebHostEnvironment.
• julealgon: @ThomasSchneiter note that not all providers support Migrations, so this is still an interesting option in those cases. See for example the CosmosDB provider.

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>();
}

• Farrah Stark: Keep in mind you if you are running migrations at runtime you will need to use a database connection with access to write to the database schema. When using this approach it is advisable to use a different connection for your queries than you are for running the migrations. If a SQL injection attack makes it through somehow and you are using a connection string with DCL or DDL permissions for you're queries it is a potential attack vector. geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands

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();
var databaseCreator = context.Database.GetService<IRelationalDatabaseCreator>();
databaseCreator.CreateTables(); 

Thanks to Bubi's answer

• MichaelMao: what is your services?
• mwilson: All the documentation I'm reading shows big fat warnings around migrations saying "Don't use EnsureCreated or EnsureDeleted or Database.Migrate will fail"

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();
 }

• Jeremy Lakeman: You can build a more portable version of this process by reusing EF Core internal services IMigrationsAssembly & IHistoryRepository.

Answer 7

As of EF Core v7.0 and .NET 6.0

Adding on to Ricardo's answer. Some changes were made to the objects in EF Core v7.0 which calls for a tweak in the answer provided.

To get the service scope they call the following line:

var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope()

However, it is a bit different now:

var serviceScope = app.Services.CreateScope()