Entity Framework Core Code First - Basic and Custom Migrations for PostgreSQL

You'll be shown how to write classes, which we will use to create our database schema.

We'll override Entity Framework's default conventions to improve our database schema. For example, changing the table and column names to help handle quoted names in PostgreSQL queries.

Plus, sometimes it's not enough to use EF Core's predefined migrations to create a valid database schema. You'll be shown ways to write your own migration steps missing in Entity Framework Core.

From this post you'll learn:

• An alternative to using Entity Framework Core Migrations.
• To forward engineer (code first) - where the database is created from the entity classes - using Npgsql.
• To use Fluent API to override EF Core's default conventions.
• PowerShell commands for migrations.
• Use the SQL method in MigrationBuilder to run any SQL statement.

SQL (Database) First vs EF Core Migrations

SQL (database) first means writing SQL scripts to create your database and creating your context and entity classes from your existing database.

One of the issues we had with SQL first was keeping our database schema in sync with our application.

Any changes you need to perform on your existing database is done by creating a new SQL script. You then update your entity classes manually.

Entity Framework Core Migrations is all about keeping your database in sync with your application. We design our entity classes according to the needs of the business and keep our database in sync using EF Core Migrations.

Another issue we encountered when using SQL first was remembering which scripts we had ran against our database. However, while writing this post we found this: DBUp

DBUp is worth investigating if you prefer or want to consider using SQL first.

One criticism of using Entity Framework Migrations is that the default conventions doesn't support all SQL statements when creating your database. However, you'll see below that we can overcome this by using the SQL method in MigrationBuilder, which will allow us to run any SQL statement.

This post will help you see what's possible when using EF Core Migrations.

Project Setup using Visual Studio

Project Example

We'll use a book store scenario:

• We'll have books with metadata such as title and price.
• All books will have an author.
• An author can have 1 or many books.
• A book can have 0 or many reviews.

Our example will allow us to cover a range of techniques for customising our database schema.

We're starting with an empty ASP.NET Core Web project.

Entity classes

We need to create our classes for our entities.

Inside a new folder - we'll call it Model - add the following classes:

// Model/Author.cs
public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}

// Model/Book.cs
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Condition { get; set; }
    public double Price { get; set; }
    public DateTime? DateSold { get; set; }
    public Author Author { get; set; }
    public virtual ICollection<Review> Reviews { get; set; }
}

// Model/Review.cs
public class Review
{
    public int ReviewId { get; set; }
    public int Stars { get; set; }
    public string Comments { get; set; }
    public Book Book { get; set; }
}

You can see that Author in the Book class and Books in the Author class are declared as navigation properties. Similarly, Reviews in the Book class and Book in the Review class are declared as navigation properties.

The Condition property is to record values such as: "new", "like new" and "used".

Entity Framework Core Context Class

Let's create the context class.

Inside a folder - called Data - add the below class:

// Data/BookStoreContext.cs
public class BookStoreContext : DbContext
{
    public BookStoreContext(DbContextOptions<BookStoreContext> options) 
        : base(options) { }

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

We're only exposing the Book entities through the context. We could expose our Author and Review entities through the context but instead we’ll access both through the Book entities.

Access our PostgreSQL Database

Next we need to edit our Startup class.

First, install the NuGet package Npgsql.EntityFrameworkCore.PostgreSQL.

Add the following code to the ConfigureServices method in the Startup class:

// Startup.cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<BookStoreContext>(
        opts =>
        {
            opts.UseNpgsql(@"Server=localhost;Port=5432;Database=book_store;
                User ID=book_store;Password=password");
        });
}

Ensure you have your user created in PostgreSQL who is allowed to login. Also create your database, making this user the owner, or granting this user permission to login, read and write to the database. You can use PgAdmin for this.

It's not necessary for your database to already exist, but you will need to grant your user the privilege of being able to create databases.

It is good practice to not grant additional privileges to users. Our user only needs to be able to read and write to the book_store database.

In the connection string you'll replace the:

• Port with the port number for your local installation of PostgreSQL.
• Database with the name of your database.
• User ID and Password with the user credentials permitted to login, read and write to that database.

For production you want to store the connection string in an environment variable or a secret json file.

Commands for Migrations

We will be building our database at the time of development. This means we'll use commands via PowerShell within the Package Manager Console of Visual Studio. You can also use the CLI tool for EF Core, which is cross platform.

We could also create our database at runtime, meaning we would use methods such as EnsureCreated() and Migrate() inside our application.

Add-Migration

To create our first migration, using the Package Manager Console, we type Add-Migration, followed by the name of the migration, for example, Add-Migration initial. Executing this command creates a Migrations folder.

A class is also created - named after the name of your migration.

If we look at this class, the Up() method creates the database (if it doesn't exist), tables, columns and indexes. The Down() method will drop the tables, columns and indexes.

Script-Migration

After creating our first migration, we'll run the command Script-Migration. It'll generate an SQL script, which will allow us to see the tables, columns and constraints we can expect to see in our newly created database.

There are a few things you'll notice:

• The books table was named after the DbSet<T> property name in the context class - Books
• The author and review tables were named after the navigational type names - Author and Review
• Each property (public property with a getter and a setter) is a column with the same name, and the same case.
• Integer properties named Id or {Type}Id are primary keys with auto-increment values. In our case this is serial. We could have named our property BookID, which would have created a column called BookID
• A foreign key column is created in our Books table called AuthorId, and in our Review table called BookId. This on the N side of the 0/1:N relationship. An index is also created for each foreign key column.
• All string types are text columns.
• There is a DELETE RESTRICT referential action on both of our foreign key clauses.

You'll also spot which columns in the tables are allowed to be left empty (NULL). Nullable types are nullable columns in the database.

With Script-Migration you want to make sure the SQL script will do exactly what you want it to do when you run Update-Database.

Update-Database

Once you're happy with the migrations (or SQL script), you can run the command Update-Database. This will apply all the migrations which haven't been run against your database.

Using the default configurations does not create a suitable database schema for our use case.

There are a few things we want to change. Below we'll show you how.

Remove-Migration

Remove-Migration will remove the last migration class.

We can run this command now to remove our initial migration. It is safe to do so because we've not used it to update our database yet.

Configuring the Database with Fluent API

We'll override some of the configuration via Fluent API in the OnModelCreating() method.

Table Names

We don’t want to pluralise any of our table names.

This is not something you need to do, but it is a good idea to use consistent plurals, or don’t use them at all.

Case sensitivity can sometimes be a problem when working with PostgreSQL. You can name your tables and columns using mixed case characters, but you will need to write your SQL using double quotes. This is because PostgreSQL converts all names to lowercase when used in a SQL statement.

CREATE TABLE users (userid integer); is the same as CREATE TABLE Users (UserId integer);

SELECT * FROM users; is the same as SELECT * FROM Users;, but is not the same as SELECT * FROM "Users".

We’ll change the names of our tables and columns to contain only lowercase letters and underscores. This is known as snake case.

This is also not something you need to do because the majority of your data access will be done using Entity Framework. However, if you ever do need to write some custom SQL, this can make things easier.

Here's how we change our table name. We edit the OnModelCreating method in our BookStoreContext class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().ToTable("book");
}

We've removed the plural name and changed it lower case.

We could do the same for other entities, but instead we'll change the names of several tables at once:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ...
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        entity.Relational().TableName = entity.DisplayName().ToSnakeCase();
    }
}

This changes our table names from being named after the DBSet properties to being named after our entity classes.

Thanks to this Gist, we have this extension method:

// StringExtensions.cs
public static class StringExtensions
{
    public static string ToSnakeCase(this string str)
    {
        return string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? 
            "_" + x.ToString() : x.ToString())).ToLower();
    }
}

Column Names

Here's how we change our column names:

modelBuilder.Entity<Book>(entity =>
    {
        entity.ToTable("book");

        // changing column names
        entity.Property(x => x.BookId).HasColumnName("book_id");
        entity.Property(x => x.Condition).HasColumnName("condition");
        entity.Property(x => x.Price).HasColumnName("price");
        entity.Property(x => x.Title).HasColumnName("title");
        entity.Property(x => x.DateSold).HasColumnName("date_sold");
    });

Our foreign key column is named after the Author primary key. Here's how we change that:

modelBuilder.Entity<Book>(entity =>
    {
        ...
        entity.HasOne(x => x.Author)
            .WithMany(x => x.Books)
            .HasForeignKey("author_id");
    });

Or, if you decide not to include the navigation property in your Author entity:

public class Author
{
    ...
    // You do not want hold a collection of books
    // public virtual ICollection<Book> Books { get; set; }
}

modelBuilder.Entity<Book>(entity =>
    {
        ...
        entity.HasOne(x => x.Author).WithMany().HasForeignKey("author_id");
    });

Here's the bulk configuration:

foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
    ...
    foreach (var property in entity.GetProperties())
    {
        property.Relational().ColumnName = property.Name.ToSnakeCase();
    }
}

Mandatory Columns and Relationships

Entity Framework Core, by default, maps nullable types to nullable columns in the database. This includes: string, int? and DateTime?.

We want the title and condition columns in the book table to be NOT NULL:

modelBuilder.Entity<Book>(entity =>
    {
        ...
        entity.Property(x => x.Condition)
            .IsRequired();

        entity.Property(x => x.Title)
            .IsRequired();
    });

Every book must have an author, and every review must be for a book. So we'll add the NOT NULL constraint to the foreign key columns.

Every book must have an author:

modelBuilder.Entity<Book>(entity =>
    {
        ...
        entity.HasOne(x => x.Author)
            .WithMany(x => x.Books)
            .IsRequired();
    });

Every review must be for a book:

modelBuilder.Entity<Review>()
    .HasOne(x => x.Book)
    .WithMany(x => x.Reviews)
    .IsRequired();

Column Lengths

We'll leave the string columns as text. This is because there is no performance difference between text and varchar(n).

If you want PostgreSQL to check the length then we would use varchar(n):

entity.Property(x => x.Title)
    // if you wanted to limit the title column to 150 characters
    .HasMaxLength(150)
    .HasColumnName("title");

Unique Indexes

To avoid duplicate stock we want to enforce unique book title names for different condition types ("new", "like new" and "used"):

modelBuilder.Entity<Book>(entity =>
    {
        ...
        entity.HasIndex(x => new
        {
            x.Title,
            x.Condition
        }).IsUnique();
    });

However, we only want to enforce unique titles for books which haven't been sold:

entity.HasIndex(x => new
{
    x.Title,
    x.Condition
}).IsUnique()
.HasFilter("date_sold IS NULL");

Here we've written some custom SQL.

Further Configurations

There are a few more thing we could add to keep our data clean.

We want to ensure that the price column always has a value greater than 0.

We can add custom migration steps to our Up() method on our automatically generated Migration class.

This is useful when you want to do something which can't be done with Fluent API.

Custom Migrations Inside the Up() Method

We'll add a check constraint to the price column, where we want the value to always be greater than 0.

If you haven't created (or re-created) your initial migration, run the command Add-Migration initial.

In our generated Migrations class, in our Up() method, just after creating the book table we'll add the following:

migrationBuilder.Sql(
    "ALTER TABLE book ADD CONSTRAINT book_price_chk CHECK (price > 0);");

Run the command Script-Migration and examine the SQL. You'll see your custom SQL just after the CREATE TABLE query.

We do not need change the Down() method becuase the step migrationBuilder.DropTable(name: "book"); will simply drop the table.