Create Tables on runtime in EF Core

Question

I know I can use the following command to add a new migration and create the database :

dotnet ef migrations add MigrationName -c DbContextName
dotnet ef database update -c DbContextName

but I need to create my database/tables at runtime.

First I tried to do that by overriding OnModelCreating but I failed. It returned me an error pointing out that the tables have not been created

Here is my dbcontext class

public class AimeLoggerContext : DbContext
    {
        public AimeLoggerContext(DbContextOptions<AimeLoggerContext> options)
            : base(options)
        {
            Database.Migrate();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity("Aime.Logger.Loggers.Database.Model.Log", b =>
            {
                b.Property<int>("Id")
                  .ValueGeneratedOnAdd();
                b.Property<DateTime>("Datetime");
                b.HasKey("Id");
                b.ToTable("Logs");
            });

            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Log> Logs { get; set; }
    }

Answer 1

Before I dive in

If you really want to do things properly, then you should probably use Migrate() rather than EnsureCreated() as the latter means that if you want to move towards Migrations then you will have problems.

The fact that you want the database and tables created when you run the app, that means your probably more in favour of migration scripts and a code first approach to your workflow rather than a database script with a database first type approach.

So I will assume that

• You want to use a code first approach
• You want to use EF Core Migrations
• You want these migrations to happen automatically when you start your app.
• You want to do this the proper way!

I have just managed to acheive this the first time (mine was with Postgres, but everything should be pretty close as I saw examples for MS Sql and they were very similar, with the only difference if any being useSqlServer or UseNpgsql or similarly trivial differences.

After struggling to follow various articles on StackOverflow which were just tiny snippets of the biggest picture I finally managed to get it working nicely, properly and maybe elegantly. I followed this article here

There's a few things that I did where I'm not completely sure which thing exactly was the thing that got it working, but believe this list to be minimal enough for a satisfactory answer.

Here's What I did

1. I made sure that I had the powershell Cli tools installed by following this article here by MicroSoft

2. I ran this in cmd.exe dotnet tool install --global dotnet-ef

3. Open the VS Package manager console, which you can access by typing Package Manager Console in the search box at the top in VS 2019 (

4. Type the following commands into it

Install-Package Microsoft.EntityFrameworkCore.Tools
Update-Package Microsoft.EntityFrameworkCore.Tools

I have my repository code in a different project to my Web App project so for me I got some errors. I had to do the following things

I tried running the EF Migrate command on my repository project as it wasn't working from the WebAPI project

dotnet ef migrations add InitialCreate --project .\DilaRepository\DilaRepository.csproj

• The console complained that

Your startup project 'DilaRepository' doesn't reference Microsoft.EntityFrameworkCore.Design

So where I got these messages I added the nuget package, that got rid of these errors. I don't know if this was required or not, because in the end I got the EF Migrate command working on the WebApi project which contains the startup.cs and program.cs

Here's what I needed to do to get it to work though

in my startup.cs I had this (which is fairly boiler plate, you'll see this on all articles)

public void ConfigureServices(IServiceCollection services)
{
   ...
   services.AddEntityFrameworkNpgsql().AddDbContext<DilaDbContext>(opt => 
            opt.UseNpgsql(
                Configuration.GetConnectionString("DefaultConnection"), 
                b => b.MigrationsAssembly("DilaAPI")
            ));

The MigrationAssembly wasn't shown in any of the articles I read and in here the one I've used is the name of the Assembly its executed in, so it seems a bit strange that it can't figure it out itself, so perhaps I did something wrong to require that. I believe one of the error messages I got from running add-migration initial told me I needed that and after this it worked.

** Its super important you add this connection string to your appsettings.json (and within there the correct one that gets read at run time because there can be multiple (such as a appsettings.Development.json) **

This will be required when you run your add-migration command from the command prompt. This created a migration folder in my WebAPI app with some things in it for the migration, however this was not executed on my database. I'm not sure whether it actually should or not, but I will explain how I did finally get it to execute that on my database.

Execute the following command to generate the migration stuff

add-migration initial

If it doesn't work and you get some errors here's somet things you can try

our target project 'DilaAPI' doesn't match your migrations assembly 'DilaRepository'. Either change your target project or change your migrations assembly. Change your migrations assembly by using DbContextOptionsBuilder. E.g. options.UseSqlServer(connection, b => b.MigrationsAssembly("DilaAPI"))

That's what originally instructed me to add the assembly in the startup.cs This is actually a really good error message as it tells you exactly what you need to do to fix it and it works

error MSB4057: The target "GetEFProjectMetadata" does not exist in the project.

This happened when I either

• Executed this in the wrong directory
• Exectued this on the solution file

In the end I needed to run this on the csproj of the WebApi project to work, you might have to cd to the directory from the package mangager console or you can specify the path to it with the --project option e.g. --project .\DilaApi\DilaApi.csproj in my case

You may also get some errors if you haven't installed the EF Tools properly from the steps above

Here's a picture of the stuff it generated

One Last thing

in your startup.cs add this to your configure method

public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DilaDbContext context)

   ...
   context.Database.Migrate();

If you've gotten everything else right when this executes on startup, this will actually execute the migration that was pre-generated by the add-migration initial command.

Now start your app and it should look something like this

Mine complained about something I did wrong, however I followed articles according to Microsoft, EF Core and Postgres, So I'm not sure why, But it worked. If anyone knows why that is and how I can rid of that warning I would be grateful. Perhaps we should tell them to update their documentation so their examples don't lead to this warning also.

What did this do to my Database

• Created the Database
• Created the Tables

MAGIC!!! 🧝‍♀️

This is quite remarkable, because it uses the infamous Convention over Configuration to know that my table should be called Word and my database Dila by the name of my Context and the name of the Entities.

Sorry for the long answer but it took me a few hours to get this working so thought I would share my war stories in the hope that everyone else gets it done a lot quicker than I did

Answer 2

If you need to create you model tables also if in your DB there are some other tables you can use

RelationalDatabaseCreator databaseCreator = 
    (RelationalDatabaseCreator) context.Database.GetService<IDatabaseCreator>();
databaseCreator.CreateTables();

Answer 3

Instead of DbContext.Database.Migrate() I should use DbContext.Database.EnsureCreated() method.