Entity Framework with PostgreSQL

Problem to solve : Create a demo database to use on demo projects , this database must have one to many, and many to many relationships.

Download docker desktop, and install it from Docker.

After that, open a terminal and run the following command:

docker pull postgres

This will download the docker image with the postgres database.

Next, run the following command:

docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

This will create a container with the name postgres, and the password postgres, and expose the port 5432 to the host machine.

Run your favorite database client, I use Azure Data Studio, and connect to the database using the following connection string:

Host=localhost;Port=5432;Database=retro_gaming;Username=postgres;Password=postgres

and run the following sql script to create the database:

create DATABASE retro_gaming

Create a new project with for framework 9.0, let's call it RetroDatabase, and add the following nuget packages:

  • Microsoft.EntityFrameworkCore 9.0.0
  • Microsoft.EntityFrameworkCore.Tools 9.0.0
  • Npgsql.EntityFrameworkCore.PostgreSQL 9.0.2

The first two are the Entity Framework Core packages, and the last one is the Npgsql provider for PostgreSQL.

Create a folder called Models, and add the following classes:

public class VideoGame
{
    public Guid Id { get; set; }
    public required string Title { get; set; }
    public int Year { get; set; }
    public required string Description { get; set; }
    public List<VideoGenre> Genres { get; set; } = [];
    public required Developer Developer { get; set; }
    public Guid DeveloperId { get; set; }
}
 
public class VideoGenre
{
    public Guid Id { get; set; }
    public required string Name { get; set; }
    public List<VideoGame> VideoGames { get; set; } = [];
}
 
public class Developer
{
    public Guid Id { get; set; }
    public required string Name { get; set; }
    public List<VideoGame> VideoGames { get; set; } = [];
}

On the VideoGame class, we have a one to many relationship with the Developer class with the property

public required Developer Developer { get; set; },

and a many to many relationship with the VideoGenre class with

public List<VideoGenre> Genres { get; set; } = []; .

Let's create the DatabaseContext class:

public class RetroDbContext : DbContext
{
 
    public DbSet<VideoGame> VideoGames => Set<VideoGame>();
    public DbSet<VideoGenre> VideoGenres => Set<VideoGenre>();
    public DbSet<Developer> Developers => Set<Developer>();
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<VideoGame>()
            .HasMany(e => e.Genres)
            .WithMany(e => e.VideoGames)
            .UsingEntity(
                "VideoGameGenre",
                l => l.HasOne(typeof(VideoGenre)).WithMany().HasForeignKey("VideoGenreId").HasPrincipalKey(nameof(VideoGenre.Id)),
                r => r.HasOne(typeof(VideoGame)).WithMany().HasForeignKey("VideoGameId").HasPrincipalKey(nameof(VideoGame.Id)),
                j => j.HasKey("VideoGameId", "VideoGenreId"));
 
        modelBuilder.Entity<Developer>()
            .HasMany(d => d.VideoGames)
            .WithOne(e => e.Developer)
            .HasForeignKey(e => e.DeveloperId)
            .IsRequired();
    }
}

We expose the VideoGame, VideoGenre, and Developer classes as DbSet properties.

For now we override the OnConfiguring method so EF knows that we are using the Postgres provider, when we create the Migration.

On the OnModelCreating method, we define the many to many relationship between the VideoGame and VideoGenre classes, and the one to many relationship between the Developer and VideoGame classes.

    modelBuilder.Entity<VideoGame>()
        .HasMany(e => e.Genres)
        .WithMany(e => e.VideoGames)
        .UsingEntity(
            "VideoGameGenre",
            l => l.HasOne(typeof(VideoGenre)).WithMany().HasForeignKey("VideoGenreId").HasPrincipalKey(nameof(VideoGenre.Id)),
            r => r.HasOne(typeof(VideoGame)).WithMany().HasForeignKey("VideoGameId").HasPrincipalKey(nameof(VideoGame.Id)),
            j => j.HasKey("VideoGameId", "VideoGenreId"));

This is the many to many relationship between the VideoGame and VideoGenre classes, we use the UsingEntity method to define the join table, and the HasOne and WithMany methods to define the relationship between the VideoGame and VideoGenre classes. HasOne and WithMany methods are used to define the relationship between the join table and the VideoGame and VideoGenre classes. HasKey method to define the composite key of the join table.

This is the one to many relationship between the Developer and VideoGame classes, we use the HasMany and WithOne methods to define the relationship between the Developer and VideoGame classes.

    modelBuilder.Entity<Developer>()
        .HasMany(d => d.VideoGames)
        .WithOne(e => e.Developer)
        .HasForeignKey(e => e.DeveloperId)
        .IsRequired();


Create a Configuration folder, and add the following classes:

 
public class VideoGameConfiguration : IEntityTypeConfiguration<VideoGame>
{
    public void Configure(EntityTypeBuilder<VideoGame> builder)
    {
        builder.HasKey(v => v.Id);
        builder.Property(v => v.Title).IsRequired();
        builder.Property(v => v.Description).IsRequired();
    }
}
 
public class VideoGenreConfiguration : IEntityTypeConfiguration<VideoGenre>
{
    public void Configure(EntityTypeBuilder<VideoGenre> builder)
    {
        builder.HasKey(v => v.Id);
        builder.Property(v => v.Name).IsRequired();
    }
}
 
public class DeveloperConfiguration : IEntityTypeConfiguration<Developer>
{
    public void Configure(EntityTypeBuilder<Developer> builder)
    {
        builder.HasKey(v => v.Id);
        builder.Property(v => v.Name).IsRequired();
    }
}

I prefer to use the IEntityTypeConfiguration interface to configure the entities, instead of DataAnnotations, because it is more flexible and easier to maintain. I want my models to be clean and simple, and the configuration to be in a separate class.

Create a folder for Migrations, open a terminal on the root folder of the solution and run the following command to create the initial migration:

"C:\Program Files\dotnet\dotnet.exe" ef migrations add --project RetroDatabase\RetroDatabase.csproj --startup-project RetroDatabase\RetroDatabase.csproj --context RetroDatabase.RetroDbContext CreateInitialTables --output-dir Migrations

Two new files will be created in the Migrations folder, one with the migration code and another with the model snapshot.

To update the database, run the following command:

"C:\Program Files\dotnet\dotnet.exe" ef database update --project RetroDatabase\RetroDatabase.csproj --startup-project RetroDatabase\RetroDatabase.csproj --context RetroDatabase.RetroDbContext CreateInitialTables --connection Host=localhost;Port=5432;Database=retro_gaming;Username=postgres;Password=postgres

The option --connection Host=localhost;Port=5432;Database=retro_gaming;Username=postgres;Password=postgres indicates the connection string to the database.

The option CreateInitialTables indicates the name of the Migration, it can be different for you.

You may need to install EF tools globally, run the following command:

dotnet tool install --global dotnet-ef

Now just connect to database and check if the tables were created.

You can see the join table VideoGameGenre with the columns VideoGameId and VideoGenreId and the foreign keys between the tables.


Hope this helps on your journey. Happy coding! 🚀