Connect C# to PostgreSQL with Entity Framework Core

In the ever-evolving world of .NET applications, integrating databases with frameworks is more than critical. Particularly, combining PostgreSQL with Entity Framework Core (EF Core), a modern object-database mapper for .NET, provides developers with a powerful tool for data access and management. This integration not only optimizes development processes but also enhances application performance. This guide will describe how to effectively use EF Core with PostgreSQL for creating .NET applications.

Key features of Entity Framework Core 8

EF Core 8 represents a significant advancement in the .NET data access technology landscape, especially when used in conjunction with PostgreSQL. It introduces a set of features and improvements that simplify database operations and enhance developer productivity.

Here are some key features of EF Core 8 that are particularly beneficial for PostgreSQL users:

• Complex types support
• Enhanced LINQ capabilities
• Interceptors and diagnostics
• Improved migrations experience
• Raw SQL queries for unmapped types

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is the ideal data provider for all PostgreSQL-related operations, offering features like on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, enhanced ORM support, and more.

Prerequisites

• 1. Visual Studio 2022: The IDE of choice. Download and install it if you don't have the IDE on your machine.
• 2. PostgreSQL server: A free database server we will interact with.
• 3. DvdRental sample database: The sample database from the official PostgreSQL documentation.
• 4. dotConnect for PostgreSQL: The fully functional trial from the official website.
• 5. Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation feature.

Download and activate dotConnect for PostgreSQL

The trial version of dotConnect for PostgreSQL is available for 30 days, allowing you to explore all its features in real-life work scenarios. To get started, simply download and install the software on your machine. No license key is needed for the trial, so you can begin using the product right away.

Access the full version

If you choose to purchase the data provider or already have the full version, visit the Licenses page in your profile on the official website. There, you'll find a list of your licenses, including your dotConnect for PostgreSQL purchase. Click on Details next to the relevant license to access the information, including your Activation Key.

Click Download Product Versions to access the list of available product versions, select the most recent version and download the installer.

Execute the downloaded file and follow the on-screen instructions to install dotConnect for PostgreSQL on your machine. This process will install the software and activate all features associated with your license.

Integrating PostgreSQL with Entity Framework

1. Open Visual Studio and click Create New Project. In the Search Template field, type ASP.NET Core Web Application and click the corresponding search result.

2. Give your project a name. For example, ours is PostgreSQL_EF_Core.

3. Select .NET 8 as the target framework and click Create.

4. The next step is to install dotConnect for PostgreSQL in our project. To do that, click Tools, point to NuGet Package Manager, and click Manage NuGet Packages for Solution.

5. The NuGet Package Manager page opens. Click Browse, and search for Devart.Data.PostgreSQL.EFCore. Select it once found, choose your project, and click Install.

6. Run the application by pressing F5 to open the scaffolded web page.

Create EF Core model using Scaffold-DbContext

We must ensure that EF Core Tools are installed in your development environment. You can install them globally using the .NET CLI. Run the following command:

dotnet tool install --global dotnet-ef

Next, we need to install Scaffold-DbContext using the Package Manager Console. Open Visual Studio, and it also opens the Package Manager Console. Run this command:

Install-Package Microsoft.EntityFrameworkCore.Tools

Once installed, we can use Scaffold-DbContext to generate DbContext and entity classes for our PostgreSQL database. Run the below command in the Package Manager Console:

Scaffold-DbContext "Server=127.0.0.1;Port=5432;UserId=postgres;Password=password;Database=dvdrental;Schema=public;LicenseKey=your_license_key" -provider Devart.Data.PostgreSQL.Entity.EFCore -OutputDir Models

The DvdrentalContext and the Models folder containing the table entity classes will be generated. You can see them in your Solution Explorer:

Therefore, our migration has been applied, and the Actor table has been created.

In DvdrentalContext, under OnModelCreating, modify the Actor builder entity and specify that it has a primary key by replacing the first Model.Entity<Actor> block with the below code:

modelBuilder.Entity<Actor>(entity =>
{
    entity
        .ToTable("actor", "public")
        .HasKey(e => e.ActorId);

    entity.HasIndex(e => e.ActorId, "actor_pkey");

    entity.Property(e => e.ActorId)
        .HasDefaultValueSql("nextval('actor_actor_id_seq'::regclass)")
        .HasColumnName("actor_id");
    entity.Property(e => e.FirstName)
        .HasMaxLength(45)
        .HasColumnType("varchar")
        .HasColumnName("first_name");
    entity.Property(e => e.LastName)
        .HasMaxLength(45)
        .HasColumnType("varchar")
        .HasColumnName("last_name");
    entity.Property(e => e.LastUpdate)
        .HasDefaultValueSql("now()")
        .HasColumnName("last_update");
});

This way, we can avoid the issue of an entity not being properly tracked.

Next, some data from the database will be retrieved using the generated context. Right-click the PostgreSQL_EF_Core project, select Add Class, add the DatabaseConnectionClass, and paste the following code into it:

public class DatabaseConnectionClass
{
    private readonly DvdrentalContext _context;

    public DatabaseConnectionClass()
    {
        _context = new DvdrentalContext();
    }
    public int AddActor(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Add(actor);
       _context.SaveChanges();
        return actor.ActorId;
    }
    public bool DeleteActor(int Id)
    {
        var actor = _context.Actors.FirstOrDefault(a => a.ActorId == Id);
        if (actor != null)
        {
            _context.Actors.Remove(actor);
            _context.SaveChanges();
            return true;
        }
        return false;
    }

    public IEnumerable<Actor> GetActorById(int Id)
    {
        return _context.Actors.Where(a => a.ActorId == Id).ToList();
    }

    public List<Actor> GetActors()
    {
        return _context.Actors.ToList();
    }

    public Actor UpdateActor(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Update(actor);
        _context.SaveChanges();
        return actor;
    }
}

Read data from a PostgreSQL database

Let's try to read from the database table using the implementation we just created.

Add a new class called ActorsController to the Controllers folder. Inside the PostgreSQL_EF_Core.Controllers namespace, modify the class to use the code below:

    [ApiController]
    [Route("api/[controller]/[action]")]
    public class ActorsController : ControllerBase
    {
        private readonly DatabaseConnectionClass _context;

        public ActorsController()
        {
            _context = new DatabaseConnectionClass();
        }

        [HttpGet]
        public IEnumerable<Actor> GetActors()
        {
            return _context.GetActors();
        }
    }

When you run the application using F5 and go to the GetActors Endpoint, click Try it out and execute. You'll see the query results in the response body.

Inserting new data into a PostgreSQL database

Next, we will try to insert a new record into the Actors table. In ActorsController, add the following piece of code:

[HttpPost]
public int AddActor(Actor actor)
{
    return _context.AddActor(actor);
}

It uses the helper method in DatabaseConnectionClass to insert a new record and returns the ID of that record. When we run the application again, we see that the new endpoint has been added.

Click the dropdown and add the below JSON object to it:

{
  "firstName": "John",
  "lastName": "Lewis"
}

The output looks like this:

Click Execute. The response body should be the ID you assigned the data.

Updating data in a PostgreSQL database

Assume that John needs changing his surname. Therefore, we have to update the data.

Add the below method to the ActorController class:

        [HttpPut]
        public Actor UpdateActor(Actor actor)
        {
            return _context.UpdateActor(actor);
        }

The helper method in DatabaseConnectionClass is used to update a particular record.

Run the application, select UpdateActor, and add the JSON object:

{
  "actorId":201,
  "firstName": "Johnny",
  "lastName": "Mcginnis"
}

The output should look like this:

The response body should present the record with the updated last name:

Deleting data from a PostgreSQL database

The actor has left the acting business, and we need to delete his record from the table.

In ActorController, add the below code piece:

[HttpDelete("{Id}")]
        public bool DeleteActor(int Id)
        {
            return _context.DeleteActor(Id);
        }
[HttpGet("{Id}")]
public IEnumerable<Actor> GetActorsById(int Id)
{
    return _context.GetActorById(Id);
}

Essentially, this deletes a row from the table and returns true if the operation is successful. If no record for that particular ID exists or an error takes place, it returns false. After that, we can use the GetActorsById Method to verify the specific actor's record no longer exists.

Run the application again. Under DeleteActor, pass the ID that we previously created, and click Execute.

This should return true, and the record should be removed. We can verify this by trying to get the record with that ID using the GetActorsById endpoint:

We see that an empty array is returned since the record no longer exists.

Create EF Core model using Entity Developer

Another way of creating an EF Core from a PostgreSQL database is through Entity Developer. As this solution is included into the prerequisites for this tutorial, open it and click File > New Model.

In the model pane, click EF Core Model under Devart Entity Framework:

The Entity Developer Model Wizard opens. Select Database First and click Next.

The Setup data connection properties window pops up. Fill in the required PostgreSQL database connection details. Make sure to use the actual User ID and Password which were configured while setting up PostgreSQL. Click Next.

Choose the modal contents: select Generate From Database. Click Next.

Choose the database objects to scaffold. You can select all, but since we have been using the Actors table, we will choose it only. Click Next.

Set up the naming rules defining the naming convention you want for the property names in the database objects. Here we will leave the default settings. Click Next.

Set up Model properties like name space and entity container. The only thing that will change on this page is the Target Framework - we are using .NET 8, so choose it (or the framework your project uses) and click Next.

After that, choose the contents of the Model Diagram. You can use all entities, split the entities by database, or make a custom selection. For this tutorial, we will use All Entities, so select that and click Next.

Choose Code Generation Templates for our objects. Here, you can define different parameters for the object to follow. We use the default settings.

The model is complete:

Conclusion

Integrating Entity Framework Core with PostgreSQL can significantly enhance your .NET applications, offering a robust, efficient, and flexible data management solution. Our guide helps you fully harness the capabilities of both technologies, leading to more productive development experiences and powerful applications.