How to 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?

Advanced ORM support

Fully supports EF Core, Dapper, NHibernate, LinqConnect, and other modern data access technologies for efficient and reliable data management.

Full ADO.NET compliance

Conforms to the latest ADO.NET standards and recent industry innovations for seamless and consistent integration with .NET applications.

PostgreSQL-specific data types

Includes many PostgreSQL-specific features and fully supports all unique data types for accurate and complete data representation.

Secure connection options

Provides robust security with support for SSL/SSH connections, connecting via proxy servers, embedded servers, and HTTP tunneling.

IDE integration

Features native integration with Visual Studio and complete design-time support for accelerated development.

Priority support & frequent updates

Includes priority support, detailed documentation, and regular updates for continuous improvement.

Download and activate dotConnect for PostgreSQL

You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:

30-day free trial version

dotnet add package Devart.Data.PostgreSql

Install-Package Devart.Data.PostgreSql

Start using dotConnect for PostgreSQL in your project today with a free trial

Free 30-day trial

Create an EF Core model

Once you have configured the database, you can proceed 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

Entity Developer allows you to visually design and generate EF Core models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.

Follow the detailed illustrated guide to create your database model using Entity Developer. When this process is complete, the model you created opens, allowing you to work with it.

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.

Connection strings

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.

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.

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.

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.

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.

FAQ