CODEWITHSUNDEEP
c#sqlite.net-coreentity-framework-core
deandob
deandob

Reputation: 5280

How to auto create database on first run?

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?

Upvotes: 191

Views: 209236

Answers (7)

cejor6
cejor6

Reputation: 41

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

Upvotes: 1

Ricardo Fontana
Ricardo Fontana

Reputation: 4813

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

Upvotes: 254

Ali Yousefi
Ali Yousefi

Reputation: 2743

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

Upvotes: 3

Farrah Stark
Farrah Stark

Reputation: 620

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

Upvotes: 22

John Pankowicz
John Pankowicz

Reputation: 4431

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

Upvotes: 22

Nathan Alard
Nathan Alard

Reputation: 1839

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

Upvotes: 14

susieloo_
susieloo_

Reputation: 1519

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.

Upvotes: 47

Related Questions