How to perform CRUD operations with Entity Framework Core and PostgreSQL
When working with relational databases, you often come across 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 speed up their development: Entity Framework. Entity Framework (in short: EF) is an ORM built with in mind simplicity and readability.
In this article, we will perform CRUD operations with Entity Framework Core on a database table stored on PostgreSQL.
Introduction EF Core
With Entity Framework you don’t have to write SQL queries in plain text: you write C# code that gets automatically translated into SQL commands. Then the result is automatically mapped to your C# classes.
Entity Framework supports tons of database engines, such as SQL Server, MySQL, Azure CosmosDB, Oracle, and, of course, PostgreSQL.
There are a lot of things you should know about EF if you’re new to it. In this case, the best resource is its official documentation.
But the only way to learn it is by getting your hands dirty. Let’s go!
How to set up EF Core
For this article, we will reuse the same .NET Core repository and the same database table we’ve used when we performed CRUD operations with Dapper (a lightweight OR-M) and with NpgSql, which is the library that performs bare-metal operations.
The first thing to do is, as usual, install the related NuGet package. Here we will need Npgsql.EntityFrameworkCore.PostgreSQL
. Since I’ve used .NET 5, I have downloaded version 5.0.10.
Then, we need to define and configure the DB Context.
Define and configure DbContext
The idea behind Entity Framework is to create DB Context objects that map database tables to C# data sets. DB Contexts are the entry point to the tables, and the EF way to work with databases.
So, the first thing to do is to define a class that inherits from DbContext
:
1 2 |
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGamesContext</span> <span class="token punctuation">:</span> <span class="token type-list"><span class="token class-name">DbContext</span></span> <span class="token punctuation">{</span> <span class="token punctuation">}</span> |
Within this class we define one or more DbSets
, that represent the collections of data rows on their related DB table:
1 |
<span class="token keyword">public</span> <span class="token return-type class-name">DbSet<span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span> Games <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Then we can configure this specific DbContext by overriding the OnConfiguring
method and specifying some options; for example, you can specify the connection string:
1 2 3 |
<span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnConfiguring</span><span class="token punctuation">(</span><span class="token class-name">DbContextOptionsBuilder</span> optionsBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> optionsBuilder<span class="token punctuation">.</span><span class="token function">UseNpgsql</span><span class="token punctuation">(</span>CONNECTION_STRING<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnConfiguring</span><span class="token punctuation">(</span>optionsBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Remember to call base.OnConfiguring
! Otherwise some configurations will not be applied, and the system may not work.
Also, pay attention to the Port in the connection string! While with other libraries you can define it as
1 |
<span class="token keyword">private</span> <span class="token keyword">const</span> <span class="token class-name"><span class="token keyword">string</span></span> CONNECTION_STRING <span class="token operator">=</span> <span class="token string">"Host=localhost:5455;"</span> <span class="token operator">+</span> <span class="token string">"Username=postgresUser;"</span> <span class="token operator">+</span> <span class="token string">"Password=postgresPW;"</span> <span class="token operator">+</span> <span class="token string">"Database=postgresDB"</span><span class="token punctuation">;</span> |
Entity Framework core requires the port to be specified in a different field:
1 |
<span class="token keyword">private</span> <span class="token keyword">const</span> <span class="token class-name"><span class="token keyword">string</span></span> CONNECTION_STRING <span class="token operator">=</span> <span class="token string">"Host=localhost;"</span><span class="token operator">+</span> <span class="token string">"Port=5455;"</span> <span class="token operator">+</span> <span class="token string">"Username=postgresUser;"</span> <span class="token operator">+</span> <span class="token string">"Password=postgresPW;"</span> <span class="token operator">+</span> <span class="token string">"Database=postgresDB"</span><span class="token punctuation">;</span> |
If you don’t explicitly define the Port, EF Core won’t recognize the destination host.
Then, we can configure the models mapped to DB tables by overriding OnModelCreating
:
1 2 3 |
<span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnModelCreating</span><span class="token punctuation">(</span><span class="token class-name">ModelBuilder</span> modelBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> modelBuilder<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">Entity</span><span class="token generic class-name"><span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span></span><span class="token punctuation">(</span>e <span class="token operator">=></span> e<span class="token punctuation">.</span><span class="token function">ToTable</span><span class="token punctuation">(</span><span class="token string">"games"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnModelCreating</span><span class="token punctuation">(</span>modelBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Here we’re saying that the rows in the games table will be mapped to BoardGame
objects. We will come back to it later.
For now, we’re done; here’s the full BoardGamesContext
class:
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGamesContext</span> <span class="token punctuation">:</span> <span class="token type-list"><span class="token class-name">DbContext</span></span> <span class="token punctuation">{</span> <span class="token keyword">public</span> <span class="token return-type class-name">DbSet<span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span> Games <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnConfiguring</span><span class="token punctuation">(</span><span class="token class-name">DbContextOptionsBuilder</span> optionsBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> optionsBuilder<span class="token punctuation">.</span><span class="token function">UseNpgsql</span><span class="token punctuation">(</span>CONNECTION_STRING<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnConfiguring</span><span class="token punctuation">(</span>optionsBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">private</span> <span class="token keyword">const</span> <span class="token class-name"><span class="token keyword">string</span></span> CONNECTION_STRING <span class="token operator">=</span> <span class="token string">"Host=localhost;Port=5455;"</span> <span class="token operator">+</span> <span class="token string">"Username=postgresUser;"</span> <span class="token operator">+</span> <span class="token string">"Password=postgresPW;"</span> <span class="token operator">+</span> <span class="token string">"Database=postgresDB"</span><span class="token punctuation">;</span> <span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnModelCreating</span><span class="token punctuation">(</span><span class="token class-name">ModelBuilder</span> modelBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> modelBuilder<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">Entity</span><span class="token generic class-name"><span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span></span><span class="token punctuation">(</span>e <span class="token operator">=></span> e<span class="token punctuation">.</span><span class="token function">ToTable</span><span class="token punctuation">(</span><span class="token string">"games"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnModelCreating</span><span class="token punctuation">(</span>modelBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Add the DbContext to Program
Now that we have the BoardGamesContext
ready we have to add its reference in the Startup
class.
In the ConfigureServices
method, add the following instruction:
1 |
services<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">AddDbContext</span><span class="token generic class-name"><span class="token punctuation"><</span>BoardGamesContext<span class="token punctuation">></span></span></span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
With this instruction, you make the BoardGamesContext
context available across the whole application.
You can further configure that context using an additional parameter of type Action<DbContextOptionsBuilder>
. In this example, you can skip it, since we’ve already configured the BoardGamesContext
using the OnConfiguring
method. They are equivalent.
If you don’t like
1 2 3 |
<span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnConfiguring</span><span class="token punctuation">(</span><span class="token class-name">DbContextOptionsBuilder</span> optionsBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> optionsBuilder<span class="token punctuation">.</span><span class="token function">UseNpgsql</span><span class="token punctuation">(</span>CONNECTION_STRING<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnConfiguring</span><span class="token punctuation">(</span>optionsBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
you can do
1 2 |
services<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">AddDbContext</span><span class="token generic class-name"><span class="token punctuation"><</span>BoardGamesContext<span class="token punctuation">></span></span></span><span class="token punctuation">(</span> optionsBuilder <span class="token operator">=></span> optionsBuilder<span class="token punctuation">.</span><span class="token function">UseNpgsql</span><span class="token punctuation">(</span>CONNECTION_STRING<span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> |
The choice is yours!
Define and customize the DB Model
As we know, EF allows you to map DB rows to C# objects. So, we have to create a class and configure it in a way that allows EF Core to perform the mapping.
Here we have the BoardGame
class:
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGame</span> <span class="token punctuation">{</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">System<span class="token punctuation">.</span>ComponentModel<span class="token punctuation">.</span>DataAnnotations<span class="token punctuation">.</span>Key</span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> Id <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">string</span></span> Name <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> MinPlayers <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> MaxPlayers <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> AverageDuration <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Notice that we’ve explicitly declared that Id
is the primary key in the table.
But it’s not enough! This way the code won’t work! 😣
Have a look at the table on Postgres:
Have you noticed it? Postgres uses lowercase names, but we are using CamelCase. C# names must be 100% identical to those in the database!
Now we have two ways:
➡ Rename all the C# properties to their lowercase equivalent
1 2 |
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGame</span> <span class="token punctuation">{</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">System<span class="token punctuation">.</span>ComponentModel<span class="token punctuation">.</span>DataAnnotations<span class="token punctuation">.</span>Key</span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> id <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">string</span></span> name <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
➡ decorate all the properties with the Column
attribute.
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGame</span> <span class="token punctuation">{</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">System<span class="token punctuation">.</span>ComponentModel<span class="token punctuation">.</span>DataAnnotations<span class="token punctuation">.</span>Key</span></span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"id"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> Id <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">string</span></span> Name <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"minplayers"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> MinPlayers <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"maxplayers"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> MaxPlayers <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"averageduration"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> AverageDuration <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Using the Column
attribute is useful also when the DB column names and the C# properties differ for more than just the case, like in:
1 2 |
<span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Column</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"averageduration"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token return-type class-name"><span class="token keyword">int</span></span> AvgDuration <span class="token punctuation">{</span> <span class="token keyword">get</span><span class="token punctuation">;</span> <span class="token keyword">set</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Is it enough? Have a look again at the table definition:
Noticed the table name? It’s “games”, not “BoardGame”!
We need to tell EF which is the table that contains BoardGame
objects.
Again, we have two ways:
➡ Override the OnModelCreating
method in the BoardGamesContext
class, as we’ve seen before:
1 2 3 |
<span class="token keyword">protected</span> <span class="token keyword">override</span> <span class="token return-type class-name"><span class="token keyword">void</span></span> <span class="token function">OnModelCreating</span><span class="token punctuation">(</span><span class="token class-name">ModelBuilder</span> modelBuilder<span class="token punctuation">)</span> <span class="token punctuation">{</span> modelBuilder<span class="token punctuation">.</span><span class="token generic-method"><span class="token function">Entity</span><span class="token generic class-name"><span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span></span><span class="token punctuation">(</span>e <span class="token operator">=></span> e<span class="token punctuation">.</span><span class="token function">ToTable</span><span class="token punctuation">(</span><span class="token string">"games"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">base</span><span class="token punctuation">.</span><span class="token function">OnModelCreating</span><span class="token punctuation">(</span>modelBuilder<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
➡ Add the Table
attribute to the BoardGame
class:
1 2 3 |
<span class="token punctuation">[</span><span class="token attribute"><span class="token class-name">Table</span><span class="token attribute-arguments"><span class="token punctuation">(</span><span class="token string">"games"</span><span class="token punctuation">)</span></span></span><span class="token punctuation">]</span> <span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">BoardGame</span> <span class="token punctuation">{</span><span class="token range operator">..</span><span class="token punctuation">.</span><span class="token punctuation">}</span> |
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 a lot the way to perform such types of operations, so we can move fast in this part.
There are two main points to remember:
- to access the context we have to create a new instance of
BoardGamesContext
, which should be placed into ausing
block. - When performing operations that change the status of the DB (insert/update/delete rows), you have to explicitly call
SaveChanges
orSaveChangesAsync
to apply those changes. This is useful when performing batch operations on one or more tables (for example, inserting an order in the Order table and updating the user address in the Users table).
Create
To add a new BoardGame
, we have to initialize the BoardGamesContext
context and add a new game to the Games
DbSet.
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">async</span> <span class="token return-type class-name">Task</span> <span class="token function">Add</span><span class="token punctuation">(</span><span class="token class-name">BoardGame</span> game<span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">using</span> <span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">var</span></span> db <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token constructor-invocation class-name">BoardGamesContext</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">AddAsync</span><span class="token punctuation">(</span>game<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span><span class="token function">SaveChangesAsync</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Read
If you need a specific entity by its id you can use Find
and FindAsync
.
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">async</span> <span class="token return-type class-name">Task<span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span></span> <span class="token function">Get</span><span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">int</span></span> id<span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">using</span> <span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">var</span></span> db <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token constructor-invocation class-name">BoardGamesContext</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">FindAsync</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Or, if you need all the items, you can retrieve them by using ToListAsync
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">async</span> <span class="token return-type class-name">Task<span class="token punctuation"><</span>IEnumerable<span class="token punctuation"><</span>BoardGame<span class="token punctuation">></span><span class="token punctuation">></span></span> <span class="token function">GetAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">using</span> <span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">var</span></span> db <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token constructor-invocation class-name">BoardGamesContext</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">ToListAsync</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Update
Updating an item is incredibly straightforward: you have to call the Update
method, and then save your changes with SaveChangesAsync
.
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">async</span> <span class="token return-type class-name">Task</span> <span class="token function">Update</span><span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">int</span></span> id<span class="token punctuation">,</span> <span class="token class-name">BoardGame</span> game<span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">using</span> <span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">var</span></span> db <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token constructor-invocation class-name">BoardGamesContext</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">Update</span><span class="token punctuation">(</span>game<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span><span class="token function">SaveChangesAsync</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
For some reason, EF does not provide an asynchronous way to update and remove items. I suppose that it’s done to prevent or mitigate race conditions.
Delete
Finally, to delete an item you have to call the Remove
method and pass to it the game to be removed. Of course, you can retrieve that game using FindAsync
.
1 2 3 |
<span class="token keyword">public</span> <span class="token keyword">async</span> <span class="token return-type class-name">Task</span> <span class="token function">Delete</span><span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">int</span></span> id<span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">using</span> <span class="token punctuation">(</span><span class="token class-name"><span class="token keyword">var</span></span> db <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token constructor-invocation class-name">BoardGamesContext</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token class-name"><span class="token keyword">var</span></span> game <span class="token operator">=</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">FindAsync</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">if</span> <span class="token punctuation">(</span>game <span class="token operator">==</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token keyword">return</span><span class="token punctuation">;</span> db<span class="token punctuation">.</span>Games<span class="token punctuation">.</span><span class="token function">Remove</span><span class="token punctuation">(</span>game<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">await</span> db<span class="token punctuation">.</span><span class="token function">SaveChangesAsync</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Further readings
Entity Framework is impressive, and you can integrate it with tons of database vendors. In the link below you can find the full list. But pay attention that not all the 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 Docker image:
🔗 How to run PostgreSQL locally with Docker | Code4IT
Then, if you don’t like Entity Framework, you can perform CRUD operations using the native library, NpgSql:
🔗 CRUD operations on PostgreSQL using C# and Npgsql | Code4IT
or, maybe, if you prefer Dapper:
🔗 PostgreSQL CRUD operations with C# and Dapper | Code4IT
Finally, you can have a look at the full repository here:
🔗 Repository used for this article | GitHub
Wrapping up
This article concludes the series that explores 3 ways to perform CRUD operations on a Postgres database with C#.
In the first article, we’ve seen how to perform bare-metal queries using NpgSql. In the second article, we’ve used Dapper, which helps mapping queries results to C# DTOs. Finally, we’ve used Entity Framework to avoid writing SQL queries and have everything in place.
Which one is your favorite way to query relational databases?
What are the pros and cons of each approach?
Happy coding!
🐧
Source: https://www.code4it.dev/blog/postgres-crud-entityframework