How to perform CRUD operations with Entity Framework Core and PostgreSQL

When working with relational databases, you often encounter two tasks: writing SQL queries and mapping the results to some DTO objects.

.NET developers are lucky to have an incredibly powerful tool that can accelerate their development: Entity Framework. Entity Framework (abbreviated: EF) is an ORM built with thought in mind Simplicity and readability.

In this article we will perform CRUD operations with Entity Framework Core in a database table stored in PostgreSQL.

Introduction EF Core

With Entity Framework you do not have to write SQL queries in plain text: you write C # code that is automatically translated into SQL commands. The result is then automatically mapped to your C # rates.

Entity Framework supports tons of database engines, such as SQL Server, MySQL, Azure CosmosDB, Oracle, and of course, PostgreSQL.

There are many things you need to know about EF if you are new to it. In this case, the best resource is his Official documentation.

But the only way to learn it is to get your hands dirty. let’s go!

How to configure EF Core

For this article, we will again use the same .NET Core repository and the same database table we used when performing CRUD operations with Dapper (lightweight OR-M) and with NpgSql, which is the directory that performs exposed metal operations. .

The first thing to do is, as usual, Install the associated NuGet package. Here we will need Npgsql.EntityFrameworkCore.PostgreSQL. Since using .NET 5, I have downloaded a version 5.0.10.

Then, we have to Define and define the context of the DB.

Set up and configure DbContext

The idea behind the Entity Framework is to create DB context Objects that map database tables to C # data sets. DB Contexts are the entry point for tablesAnd EF’s way of working with databases.

So, the first thing to do is set up a class that inherits from it DbContext:

public class BoardGamesContext : DbContext
{

}

Within this class we define one or more DbSetsRepresenting the data line collections in the related DB table:

public DbSet<BoardGame> Games { get; set; }

So we can define it specifically DbContext By overriding OnConfiguring Method and specifying several options; For example, you can specify the connection string:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql(CONNECTION_STRING);
    base.OnConfiguring(optionsBuilder);
}

Remember to call base.OnConfiguring! Otherwise some configurations will not apply, and the system may not work.

also, Pay attention to the port in the connection string! While with other directories you can set it to approx

private const string CONNECTION_STRING = "Host=localhost:5455;" +
    "Username=postgresUser;" +
    "Password=postgresPW;" +
    "Database=postgresDB";

The Entity Framework core requires specifying the port in another field:

private const string CONNECTION_STRING = "Host=localhost;"+
            "Port=5455;" + 
            "Username=postgresUser;" +
            "Password=postgresPW;" +
            "Database=postgresDB";

If you do not explicitly set the port, EF Core will not recognize the target host.

Then, We can configure the mapped models to DB tables by overriding OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BoardGame>(e => e.ToTable("games"));
    base.OnModelCreating(modelBuilder);
}

Here we say that the lines in Games The table will be mapped to BoardGame stuff. We will return to it later.

For now, we’re done; Here is the full one BoardGamesContext status:

public class BoardGamesContext : DbContext
{
    public DbSet<BoardGame> Games { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(CONNECTION_STRING);
        base.OnConfiguring(optionsBuilder);
    }
    private const string CONNECTION_STRING = "Host=localhost;Port=5455;" +
                "Username=postgresUser;" +
                "Password=postgresPW;" +
                "Database=postgresDB";

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BoardGame>(e => e.ToTable("games"));
        base.OnModelCreating(modelBuilder);
    }
}

Add the DbContext to the program

Now that we have the BoardGamesContext Ready we need to add his reference in Startup status.

In the ConfigureServices Method, add the following instruction:

services.AddDbContext<BoardGamesContext>();

With this instruction, you do the BoardGamesContext The context is available throughout the app.

You can set this context using another type parameter Action<DbContextOptionsBuilder>. In this example, you can skip it, since we have already defined the BoardGamesContext Uses b OnConfiguring method. They are equal.

If you do not like

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql(CONNECTION_STRING);
    base.OnConfiguring(optionsBuilder);
}

You can do

services.AddDbContext<BoardGamesContext>(
    optionsBuilder => optionsBuilder.UseNpgsql(CONNECTION_STRING)
);

The choice is in your hands!

Define and customize the DB model

As we know, EF allows you to map DB rows to C # objects. Therefore, we need to create a class and configure it in a way that will allow EF Core to perform the mapping.

Here we have the BoardGame status:

public class BoardGame
{
    [System.ComponentModel.DataAnnotations.Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public int MinPlayers { get; set; }

    public int MaxPlayers { get; set; }

    public int AverageDuration { get; set; }
}

Notice that We have explicitly stated this Id Is the main key at the table.

But that’s not enough! That way the code will not work! 😣

Look at the table on Postgres:

Did you notice that? Postgres uses names in lower caseBut we use CamelCase. C # names must be 100% identical to those in the database!

We now have two ways:

➡ Rename all the C # properties to their lowercase equivalent in lower case

public class BoardGame
{
    [System.ComponentModel.DataAnnotations.Key]
    public int id { get; set; }
    public string name { get; set; }
    
}

➡ Decorate all the properties with Column attribute.

public class BoardGame
{
    [System.ComponentModel.DataAnnotations.Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; }

    [Column("minplayers")]
    public int MinPlayers { get; set; }

    [Column("maxplayers")]
    public int MaxPlayers { get; set; }

    [Column("averageduration")]
    public int AverageDuration { get; set; }
}

Uses b Column The feature is also useful when the column names DB and C # properties are different for more than just a case, as in:

[Column("averageduration")]
public int AvgDuration { get; set; }

Is it enough? Take another look at the table setting:

Did you notice the name of the table? It’s “games”, not “board game”!

We need to tell EF what table it contains BoardGame stuff.

Again, we have two ways:

➡ Bypass the OnModelCreating Method b BoardGamesContext Grade, as we have seen before:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BoardGame>(e => e.ToTable("games"));
    base.OnModelCreating(modelBuilder);
}

את Add the Table Attribute to BoardGame status:

[Table("games")]
public class BoardGame
{...}

Again, the choice is yours.

CRUD operations with Entity Framework

Now that the setup is complete, we can perform our CRUD operations. Entity Framework Simplifies Much The way to perform such types of actions so that we can move quickly in this part.

Two main points to keep in mind:

1. To approach the context we must create a new show of his BoardGamesContextWhich should be placed into a using block.
2. When performing actions that change the status of the DB (add / update / delete lines), explicitly call SaveChanges or SaveChangesAsync Apply these changes. This is useful when performing batch operations in one or more tables (for example, adding an order to the order table and updating the user address in the users table).

to create

To add a new one BoardGameWe need to initialize the BoardGamesContext Contact and add a new game to Games DbSet.

public async Task Add(BoardGame game)
{
    using (var db = new BoardGamesContext())
    {
        await db.Games.AddAsync(game);
        await db.SaveChangesAsync();
    }
}

to read

If you need a specific entity according to its ID you can use Find and FindAsync.

public async Task<BoardGame> Get(int id)
{
    using (var db = new BoardGamesContext())
    {
        return await db.Games.FindAsync(id);
    }
}

Alternatively, if you need all the items, you can retrieve them using ToListAsync

public async Task<IEnumerable<BoardGame>> GetAll()
{
    using (var db = new BoardGamesContext())
    {
        return await db.Games.ToListAsync();
    }
}

updating

Updating an item is incredibly simple: you need to call Update Method, and then save your changes with SaveChangesAsync.

public async Task Update(int id, BoardGame game)
{
    using (var db = new BoardGamesContext())
    {
        db.Games.Update(game);
        await db.SaveChangesAsync();

    }
}

For some reason, EF does not provide an asynchronous way to update and remove items. I guess it was done to prevent or reduce race conditions.

Clear

Finally, to delete an item you need to call Remove Method and transfer to it the game to be removed. Of course, you can retrieve the game using FindAsync.

public async Task Delete(int id)
{
    using (var db = new BoardGamesContext())
    {
        var game = await db.Games.FindAsync(id);
        if (game == null)
            return;

        db.Games.Remove(game);
        await db.SaveChangesAsync();
    }
}

Additional readings

Entity Framework is impressive, and you can integrate it with tons of database providers. In the link below you will find the full list. But note that not all libraries are implemented by the EF team, some are third-party libraries (like the one we used for Postgres):

🔗 Database Providers Microsoft docs

If you want to start working with PostgreSQL, a good way is to download it as a Stabbing Image:

How to Run PostgreSQL Locally with Docker | Code4IT

Then, if you do not like the Entity Framework, you can perform CRUD operations using the original directory, NpgSql:

CRUD operations on PostgreSQL using C # and Npgsql | Code4IT

Or, perhaps, if you prefer Dapper:

Gre PostgreSQL CRUD operations with C # and Dapper | Code4IT

Finally, you can look at the full database here:

🔗 Database used for this article GitHub

Finishing

This article concludes the series examining 3 ways to perform CRUD operations on a Postgres database with C #.

In the first article, we saw how to execute just-metal queries using NpgSql. In the second article, we used Dapper, which helps map query results to C # DTOs. Finally, we used the Entity Framework to avoid writing SQL queries and everything in place.

What is your preferred way of querying relational databases?

What are the pros and cons of each approach?

Happy coding!

🐧