EF Core - How to create a database and a table

In this article, I’ll show how to use EF Core to create a database with one table in it. At the end, I’ll show a standalone console app that inserts a record into this database.

Note: I’ll be using SQL Server. I haven’t tried this with other database providers. Please leave a comment if you’re using a different database provider and let me know how it went.

Table of Contents

Add the EF Core nuget packages and tools

Install the EF Core nuget packages

The first step is to install the EF Core packages – Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Design (this is installing with Package Manager Console):

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Design
Code language: PowerShell (powershell)

If you’re not using SQL Server, you’ll need to get the appropriate package for your database provider.

This will add the package references to your csproj file, like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RootNamespace>ef_core_cli</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.4">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.4" />
  </ItemGroup>

</Project>

Code language: HTML, XML (xml)

Install the dotnet ef tool

The dotnet ef tool is used to install and update the database.

Install the tool by executing the following on the command line:

dotnet tool install --global dotnet-ef
Code language: PowerShell (powershell)

Add a model

A model is a class that represents a table in the database. Each property is a column in the table. You can use data annotation attributes to define how each column should be created.

When you use EF Core to create the database, it will use the model classes to create the tables.

Here’s the Movie model class. This will be used to create the Movies table.

using System.ComponentModel.DataAnnotations;

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int ReleaseYear { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	[Required]
	public int RuntimeMinutes { get; set; }
}
Code language: C# (cs)

Data annotations

The Movie model is using data annotation attributes to specify how the table should be created. There are more data annotations available, but I’ll just explain that three that are being used on the Movie model:

Annotation	What’s it mean	Example
Key	The column is the primary key.	[Key] public int Id { get; set; } Generates column: Id (PK, int, not null)
Required	The column is not nullable.	[Required] public int ReleaseYear { get; set; } Generates column: ReleaseYear (int, not null)
MaxLength	The column field size. If you don’t specify this, string fields will have field size MAX.	[Required] [MaxLength(500)] public string Name { get; set; } Generates column: Name (nvarchar(500), not null)

It should be noted that some data annotations double as model validation attributes (such as [Required]) in web APIs and frontend web.

Add your own DbContext class

When creating the database, EF Core will use the information in your DbContext class to determine how to create the database.

So subclass DbContext and:

Override OnConfiguring and pass in a connection string.
Add a DbSet<T> property for each model.

using Microsoft.EntityFrameworkCore;

public class StreamingServiceContext : DbContext
{

	private readonly string ConnectionString;
	public StreamingServiceContext(string connectionString)
	{
		ConnectionString = connectionString;
	}
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(ConnectionString);
	}

	public DbSet<Movie> Movies { get; set; }
}
Code language: C# (cs)

Optional – Add your ConnectionString to a config file

You need to have your connection string somewhere. I’m going to show how to add it to appsettings.json and then override it using user secrets. This step is optional, because you may choose to deal with the connection string differently.

Add appsettings.json

Add a new file called appsettings.json.
Set it to Copy if newer.
Put the ConnectionStrings section in there. I’m leaving it blank on purpose. The actual connection string will be specified in the user secrets file.

{
  "ConnectionStrings": {
    "Default": "<left blank>"
  }
}
Code language: JSON / JSON with Comments (json)

Your csproj should now look like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RootNamespace>ef_core_cli</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.4">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.4" />
  </ItemGroup>

  <ItemGroup>
    <None Update="appsettings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

Code language: HTML, XML (xml)

Add the User Secrets and override the connection string

Now install the Microsoft.Extensions.Configuration.UserSecrets package (this is using Package Manager Console):

Install-Package Microsoft.Extensions.Configuration.UserSecrets
Code language: PowerShell (powershell)

Note: The UserSecrets package contains Microsoft.Extensions.Configuration.Json already, so you don’t need to add it separately.

Right-click on the project > click Manage User Secrets.
This creates the secrets.json file and opens it.
Now can you override the connection string from appsettings by putting it in secrets.json:

{
  "ConnectionStrings": {
    "Default": "Server=SQL_SERVER_INSTANCE_NAME;Database=StreamingService;Integrated Security=true"
  }
}
Code language: JSON / JSON with Comments (json)

Your csproj file should now have the user secrets info in it:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RootNamespace>ef_core_cli</RootNamespace>
    <UserSecretsId>9bc52419-f971-411a-91e7-47ecdfb428da</UserSecretsId>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.4">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.4" />
    <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="5.0.0" />
  </ItemGroup>

  <ItemGroup>
    <None Update="appsettings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

Code language: HTML, XML (xml)

Fetch the connection string with ConfigurationBuilder

Now use ConfigurationBuilder to fetch the connection string:

using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder()
	.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
	.AddJsonFile("appsettings.json")
	.AddUserSecrets<Program>()
	.Build();


var connectionString = config.GetConnectionString("Default");

Console.WriteLine(connectionString);
Code language: C# (cs)

Run the code. It should output the connection string from the user secrets file:

Server=SQL_SERVER_INSTANCE_NAME;Database=StreamingService;Integrated Security=trueCode language: plaintext (plaintext)

Note: If the connection string is not overridden in secrets.json, it’ll pull the connection string from appsettings.json instead.

Create the database

Now that you have the DbContext, a model, and a way to get the connection string, you can use dotnet ef to actually create the database.

This is a two-step process. First you create a database migration for the initial version of the database, and then you apply it. As you make changes to the database schema, you create more migrations and apply them on top of existing migrations. This will make more sense when you do the steps below.

Add a design-time context factory

When you use dotnet ef to create the migration, it will look for a parameterless DbContext class. If that doesn’t exist, it will look for an implementation of IDesignTimeDbContextFactory. After that, it starts to get more and more complicated.

The simplest solution is to implement IDesignTimeDbContextFactory. This gives you full control over what context object dotnet ef uses when it creates migrations.

using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;

public class DesignTimeContextFactory : IDesignTimeDbContextFactory<StreamingServiceContext>
{
	public StreamingServiceContext CreateDbContext(string[] args)
	{
		var config = new ConfigurationBuilder()
			.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
			.AddJsonFile("appsettings.json")
			.AddUserSecrets<Program>()
			.Build();


		var connectionString = config.GetConnectionString("Default");

		return new StreamingServiceContext(connectionString);
	}
}
Code language: C# (cs)

Create database migration

To create a database migration, execute the following command (from the project directory):

dotnet ef migrations add Database_v0
Code language: PowerShell (powershell)

This will add a /Migrations/ folder to your project and create three migration source files:

20210314124406_Database_v0.cs
20210314124406_Database_v0.Designer.cs
StreamingServiceContextModelSnapshot.cs

Note: dotnet ef creates the migration files with a timestamp in the name.

These migration files contain the logic for creating the database. Take a look at 20210314124406_Database_v0.cs:

public partial class Database_v0 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.CreateTable(
			name: "Movies",
			columns: table => new
			{
				Id = table.Column<int>(type: "int", nullable: false)
					.Annotation("SqlServer:Identity", "1, 1"),
				Name = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				ReleaseYear = table.Column<int>(type: "int", nullable: false),
				Description = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				RuntimeMinutes = table.Column<int>(type: "int", nullable: false)
			},
			constraints: table =>
			{
				table.PrimaryKey("PK_Movies", x => x.Id);
			});
	}

	protected override void Down(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.DropTable(
			name: "Movies");
	}
}
Code language: C# (cs)

Apply the database migration to create the database

Now you can apply the migration programmatically or by using the dotnet ef tool.

Note: Discussing production deployment strategies is out of scope for this article.

Option 1 – Apply migration using dotnet ef

To apply the latest migration, execute the following (from the project directory):

dotnet ef database update
Code language: PowerShell (powershell)

If your database doesn’t exist, it’ll create it, then apply the latest migration. You can also specify which migration to use.

This option works if you’re in a dev environment and have the project directory available.

Option 2 – Apply migration from the code

You can call context.Database.Migrate() and it’ll apply the latest migration. If your database doesn’t exist, it’ll create it.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder()
	.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
	.AddJsonFile("appsettings.json")
	.AddUserSecrets<Program>()
	.Build();


using (var context = new StreamingServiceContext(config.GetConnectionString("Default")))
{
	context.Database.Migrate();
}
Code language: C# (cs)

Use the database

Now that you have the database created, you can start to use it.

If you’re not familiar with using EF Core to execute queries, I suggest experimenting with it in a standalone console app. Here is an example of inserting a record into the database:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder()
	.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
	.AddJsonFile("appsettings.json")
	.AddUserSecrets<Program>()
	.Build();


using (var context = new StreamingServiceContext(config.GetConnectionString("Default")))
{
	context.Database.Migrate();

	context.Add(new Movie 
	{ 
	   Name = "John Wick",
	   Description = "A revenge-seeking assassin goes after EVERYONE",
	   ReleaseYear = 2014,
	   RuntimeMinutes = 101
	});
	context.SaveChanges();
}
Code language: C# (cs)

If you look in the database, you can see this record got inserted in the Movies table.

Source code in GitHub

The source code shown in this article is available on GitHub. If you use the code from this repository, make sure you install dotnet ef so you can add / apply migrations.

EF Core – How to create a database and a table