Connect C# to PostgreSQL With Entity Framework Core

One of the most essential steps in .NET application development is integrating databases with frameworks, such as combining PostgreSQL with Entity Framework Core (EF Core).

EF Core is a modern ORM for .NET, and it provides developers with a powerful tool for data access and management. This integration not only optimizes development processes but also improves application performance. So, let us see how to effectively use EF Core with PostgreSQL for creating .NET applications, we have prepared a detailed step-by-step guide.

Key features of Entity Framework Core

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 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: high-performance ADO.NET data provider that grants simple access to PostgreSQL databases.
• 5. Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation feature.

Download and activate dotConnect for PostgreSQL

30-day free trial version

Download and install dotConnect for PostgreSQL directly on your machine, or install the Devart.Data.PostgreSQL NuGet package.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

To activate a connection in your application, add the License Key to your connection string.

Create a .NET project

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

Once you have configured the database, you can move on to the next step - creating an EF Core model, an ORM (Object-Relational Mapper) for .NET applications which lets us interact with databases using C# objects instead of writing SQL queries. We can create an EF Core model in two ways: using Scaffold-DbContext or via Entity Developer.

Create an 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.

Create an EF Core model via Entity Developer

Install Entity Developer following the on-screen instructions.

1. Right-click the PostgreSQL_EF_Core project in the Solution Explorer and select Add > New Item.

2. Go to Installed > C# > ASP.NET Core > Data, select Devart EF Core Model, and click Add.

3. In the Create Model Wizard, select Database First. Click Next.

4. Fill in the details of your PostgreSQL database connection. Click Next.

5. Select Generate From Database. Click Next.

6. Choose the database objects you want to scaffold. You can select all, but since we've been using the Actor table, let's select only it.

7. Define the naming convention for the property names in the database object. We suggest keeping the default settings this time.

8. On the Model properties page, change only the Target Framework field. Select .NET 8 (or a different framework your project uses).

9. Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.

10. Choose code generation templates for your objects. You can define different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.

Your model is ready now.

11. Clear the Download 'Devart.Data.PostgreSql.EFCore' NuGet package checkbox because we've already added this package to the project. Finally, click Finish.

The model opens. You can work with the diagram and edit its fields and relations. It also allows you to generate the SQL code for altering the database, or you can update the database directly from the model.

This guideline was designed with Visual Studio in mind. However, if you prefer VS Code, JetBrains Rider, or another similar tool, you can use ED as a standalone application to work with the models efficiently.

Now, move on to creating a connection class.

Create a connection class

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 PostgreSQL

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.

Insert new data into PostgreSQL

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.

Update PostgreSQL data

Assume that we need to change John's last name. 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:

Delete data from PostgreSQL

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.

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.