EF Core - Creating a Database and Table

In this guide, I'll demonstrate how to use EF Core to create a database with a table. We will also explore how to insert a record into the database via a simple console application.

1. Install EF Core NuGet Packages

To begin, you need to install the required EF Core packages.

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Design

This will add the following to your csproj file:

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.4" />
</ItemGroup>

2. Install the dotnet-ef Tool

To work with migrations, install the dotnet ef tool globally.

dotnet tool install --global dotnet-ef

3. Create the Book Model

In EF Core, a model represents a table in the database.

We will create a Book model for the Books table.

using System.ComponentModel.DataAnnotations;

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

    [Required]
    [MaxLength(255)]
    public string Title { get; set; }

    [Required]
    public string Author { get; set; }

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

    public string Genre { get; set; }
}

4. Create the DbContext Class

The DbContext class in EF Core is used to map the models to the database tables.

Here, we create a LibraryContext for our Book model.

using Microsoft.EntityFrameworkCore;

public class LibraryContext : DbContext
{
    private readonly string _connectionString;

    public LibraryContext(string connectionString)
    {
        _connectionString = connectionString;
    }

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

    public DbSet<Book> Books { get; set; }
}

5. Add Connection String

To keep the connection string secure, you can add it to your appsettings.json file.

{
  "ConnectionStrings": {
    "Default": "Server=SQL_SERVER_INSTANCE;Database=LibraryDb;Integrated Security=True"
  }
}

6. Create a Design-Time Context Factory

To run migrations, EF Core needs a parameterless DbContext class. If it can't find one, it will search for an IDesignTimeDbContextFactory.

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

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

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

        return new LibraryContext(connectionString);
    }
}

7. Add a Migration

Now, you need to create a migration to define the initial version of the database.

dotnet ef migrations add InitialCreate

This will create migration files under the /Migrations/ directory, including:

• 20230201093500_InitialCreate.cs
• 20230201093500_InitialCreate.Designer.cs
• LibraryContextModelSnapshot.cs

8. Apply the Migration

Once you have the migration, you can apply it to the database using one of two options:

Option 1: Apply Migration with dotnet ef

Run the following command to apply the migration:

dotnet ef database update

Option 2: Apply Migration Programmatically

You can also apply the migration in code, using context.Database.Migrate():

using (var context = new LibraryContext(connectionString))
{
    context.Database.Migrate();
}

9. Use the Database

Now that your database is set up, you can start using it!

For example, how you can insert a new record into the Books table:

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

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

using (var context = new LibraryContext(connectionString))
{
    context.Database.Migrate();

    context.Add(new Book 
    { 
        Title = "1984",
        Author = "George Orwell",
        PublishedYear = 1949,
        Genre = "Dystopian"
    });
    context.SaveChanges();
}

You should now see the new book record inserted into the Books table in the database.

With these steps, you’ve successfully created a database with EF Core, defined a Book model, created the necessary migrations, and inserted a record.