Create Tables on runtime in EF Core
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();
Instead of DbContext.Database.Migrate()
I should use DbContext.Database.EnsureCreated()
method.
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
I made sure that I had the powershell Cli tools installed by following this article here by MicroSoft
I ran this in cmd.exe
dotnet tool install --global dotnet-ef
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 (
- 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