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?

Solution 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

Solution 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.

Solution 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();
      ...

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

Solution 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

View more solutions

Comments

[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/…

[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.

[pampi]

This answers my confusion why my connection string is not working... :( So database not automatically created that you must specify Database.EnsureCreated() which I done in my DbContext constructor. Thank you very much, saves me from 3 days dilemma. X_X

[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?

[Tobias]

IMHO this is the best solution: you don't have to care about any service or even the DB context, you just can use the context which you get via dependency injection. Nice!

[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"

[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.

[Paul 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

[Paramesh Korrakuti]

Looks like IApplicationBuilder works for web applications, is there a possibility to do the same from C# azure functions?

[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.

[Mohsen]

Saved my day for the second time.