Connect C# to PostgreSQL With Entity Framework Core

Integrating PostgreSQL with Entity Framework Core (EF Core) is a key step in building efficient and maintainable .NET applications. EF Core offers a modern, high-performance ORM for streamlined data access, while PostgreSQL brings reliability and advanced database features.

In this step-by-step guide, you'll learn how to connect your C# application to PostgreSQL using dotConnect for PostgreSQL, generate EF Core models, and implement database operations with ease.

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.

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 select Create a new project. Choose Console App, and click Next.

2. Enter a project name (for example, PostgreSql_EF_Core), then click Create.

3. Right-click the project in Solution Explorer and select Manage NuGet Packages.

4. On the Browse tab, search for and install the following packages:

Create EF Core model

Once you have configured the database, you can move on to the next step—creating an EF Core model. You can do this in two ways: using Scaffold-DbContext or via Entity Developer.

Create an EF Core model via Entity Developer

Note

If you don't have Entity Developer installed, close your Visual Studio instance and install Entity Developer following the on-screen instructions.

1. Right-click anywhere inside Solution Explorer and select Add > New Item.

Add a new item

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

Add a new EF Core model

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

Choose the database-first approach

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

Specify the database connection properties

5. Select Generate From Database and click Next.

Generate the model content from database

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.

Choose the database objects to scaffold

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

Define the naming conventions

8. On the next page, configure the necessary settings, including file selection for saving the connection, and defining the names of DbContext classes. Select your EF Core version and .NET target framework, then click Next.

Define the target framework

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.

Choose the model diagram contents

10. Choose code generation templates for your objects. You can define parameters according to your preferences or apply default settings. For this tutorial, use the default settings. Click Next.

Choose code generation templates for objects

11. Your model is ready now. Click Finish.

The model is successfully created

The model you created opens.

Work with the diagram

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.

Create an EF Core model using Scaffold-DbContext

You can use Scaffold-DbContext to generate DbContext and entity classes for your database. Run the following command, replacing values with your actual credentials:

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

After you execute this command, the ModelContext file and the Models folder containing the table entity classes get generated.

Connect to PostgreSQL and retrieve data

This section connects to a PostgreSQL database and retrieves the first 10 rows from the Actor table. The dvdrentalModel DbContext is configured with a connection string in the OnConfiguring method.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new dvdrentalModel();
            var actors = context.Actors.Take(10).ToList();

            Console.WriteLine("First 10 Actors:");
            foreach (var actor in actors)
            {
                Console.WriteLine($"ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

Run the application to execute the query. The retrieved data — the first 10 records from the Actor table — will be printed in the console output.

Read data from database

Insert new records using EF Core

This section inserts a new test actor into the Actor table using EF Core’s Add and SaveChanges methods. After executing the operation, the new record is persisted to the database, and you can verify the insertion by querying the table or viewing the console output.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new dvdrentalModel();
            var newActor = new Actor
            {
                FirstName = "Test",
                LastName = "Actor",
                LastUpdate = DateTime.Now
            };

            context.Actors.Add(newActor);
            int rows = context.SaveChanges();

            Console.WriteLine($"Inserted Actor - ID: {newActor.ActorId}, Name: {newActor.FirstName} {newActor.LastName}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console shows the inserted record, confirming that the data was successfully saved to the database. You should see the newly added actor's ID along with their first and last name.

Add data into database

Update PostgreSQL data using EF Core

This section updates the test actor’s first and last names. The record is retrieved, modified, and saved with SaveChanges. Once the update is complete, the console displays the updated values, verifying that the changes were successfully applied to the database.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new dvdrentalModel();
            var actor = context.Actors.FirstOrDefault(a => a.FirstName == "Test" && a.LastName == "Actor");

            if (actor != null)
            {
                actor.FirstName = "Updated";
                actor.LastName = "Star";
                actor.LastUpdate = DateTime.Now;
                context.SaveChanges();

                Console.WriteLine($"Updated Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
            else
            {
                Console.WriteLine("Actor not found.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console displays the updated record, showing the modified first and last names. This confirms that the changes have been persisted correctly in the database.

Update data in a database

Delete PostgreSQL data using EF Core

This section deletes the test actor from the Actor table using Remove and SaveChanges. After the deletion is committed, the actor record is permanently removed from the database.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new dvdrentalModel();
            var actor = context.Actors.FirstOrDefault(a => a.FirstName == "Updated" && a.LastName == "Star");

            if (actor != null)
            {
                context.Actors.Remove(actor);
                context.SaveChanges();

                Console.WriteLine($"Deleted Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
            else
            {
                Console.WriteLine("Actor not found.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console confirms the deletion by indicating that the record has been successfully removed.

Delete data from a database

Video tutorial: Connect to PostgreSQL with EF Core

Conclusion

Integrating Entity Framework Core with PostgreSQL provides a solid foundation for building scalable and efficient .NET applications. By leveraging the capabilities of EF Core and dotConnect for PostgreSQL, developers can streamline data access, reduce boilerplate code, and maintain cleaner architecture. This guide equips you with the tools and techniques needed to develop high-performance applications backed by the reliability of PostgreSQL.