C# | Using Entity Framework with PostgreSQL Database

Note
You can check other posts on my personal website: https://hbolajraf.net

Using Entity Framework with PostgreSQL Database in C

In this guide, we will explore how to use C# Entity Framework Core to interact with a PostgreSQL database. Entity Framework Core is a powerful Object-Relational Mapping (ORM) framework that simplifies database operations in C# applications. PostgreSQL is a robust, open-source relational database management system.

Prerequisites

• Basic knowledge of C# programming.
• Visual Studio or Visual Studio Code.
• .NET Core SDK installed.
• PostgreSQL Database Server installed and running.

Setup

Install Required Packages

To use Entity Framework Core with PostgreSQL, you need to install the following NuGet packages:

• Microsoft.EntityFrameworkCore
• Npgsql.EntityFrameworkCore.PostgreSQL

You can install them using the NuGet Package Manager or the .csproj file.

Database Connection String

Make sure to configure a connection string to your PostgreSQL database in your application's configuration. The connection string typically looks like this:

Server=myserver;Port=myport;Database=mydatabase;User Id=hbolajraf;Password=azerty_:=);

Creating a Model

A model represents a database table. Create a C# class for your model, and annotate it with the [Key] attribute for the primary key and other attributes to define the table structure.

public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Database Context

A database context represents a session with the database. Create a database context class that derives from DbContext and includes a DbSet for each model.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products { get; set; }
}

Migration

Entity Framework Core uses migrations to create and update the database schema. Run the following commands to create and apply migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

This will create the database schema based on your model.

CRUD Operations

You can now perform CRUD (Create, Read, Update, Delete) operations on your database using Entity Framework Core. Here are some examples:

• Create:

using (var context = new AppDbContext())
{
    var newProduct = new Product { Name = "Tonic Bimo", Price = 10.99 };
    context.Products.Add(newProduct);
    context.SaveChanges();
}

• Read:

using (var context = new AppDbContext())
{
    var products = context.Products.ToList();
}

• Update:

using (var context = new AppDbContext())
{
    var product = context.Products.Find(1);
    if (product != null)
    {
        product.Price = 20.99;
        context.SaveChanges();
    }
}

• Delete:

using (var context = new AppDbContext())
{
    var product = context.Products.Find(1);
    if (product != null)
    {
        context.Products.Remove(product);
        context.SaveChanges();
    }
}

Querying the Database

Entity Framework Core provides a powerful query API. You can use LINQ queries to retrieve data from the database.

using (var context = new AppDbContext())
{
    var expensiveProducts = context.Products
        .Where(p => p.Price > 20)
        .ToList();
}

You can write complex queries and join multiple tables using LINQ.

What Next?

Entity Framework Core simplifies working with a PostgreSQL database in C# applications. With the steps outlined in this guide, you can create, read, update, and delete data, as well as perform complex queries, all with the power and flexibility of Entity Framework Core and the reliability of PostgreSQL.