7 min read ASP.NET Core

Using Entity Framework with Postgres and Neon

This article explores the powerful integrations between Entity Framework Core (EF Core) and PostgreSQL. We also take a look at the advantages to hosting our PostgreSQL database on Neon, and give practical guidance on handling EF migrations and database scaffolding.
Using Entity Framework with Postgres and Neon
Photo by Alex Kotliarskyi / Unsplash

This article explores the powerful integrations between Entity Framework Core (EF Core) and PostgreSQL. We also look at the advantages of hosting our PostgreSQL database on Neon and give practical guidance on handling EF migrations and database scaffolding.

man in grey shirt using grey laptop computer
Photo by Desola Lanre-Ologun / Unsplashlook at the advantages of

In the previous article, Getting Started with ASP.NET Core API, EF Core, and PostgreSQL on Neon, we explored how we can create an ASP.NET Core application that performs CRUD operations on a PostgreSQL database hosted by Neon.

Neon offers a serverless PostgreSQL platform designed to streamline database management and scalability. This makes Neon a compelling choice for hosting PostgreSQL databases, particularly for applications requiring scalability, flexibility, and cost-efficiency.

Why use PostgreSQL with .NET?

With years of continuous development, PostgreSQL is a robust, open-source object-relational database system with a solid reputation for performance, feature robustness, and dependability.

  • Compatibility and Flexibility: PostgreSQL is open-source, cross-platform, and known for being highly compatible with various programming languages, including .NET. Because of this, it's a fantastic option for developers who require a stable database system that works well with .NET Core.
  • Performance and Reliability: PostgreSQL is renowned for performance and stability, especially for complicated queries and big data sets, making it perfect for commercial applications built on .NET.
  • Feature-Rich Database: PostgreSQL supports advanced data types, full-text search, and powerful indexing, which are advantageous for .NET applications requiring complex data processing or analytics.
  • Cost-Effective Scaling on Neon: Neon provides a serverless PostgreSQL option, allowing scalable and cost-effective database management. For .NET applications, this flexibility helps control costs as usage grows.

Why does EF Core work well with PostgreSQL?

Microsoft's object-relational mapper (ORM) for.NET, Entity Framework Core (EF Core), was created to simplify database operations by enabling developers to deal directly with.NET objects rather than raw SQL queries.

  • Abstraction Layer: EF Core is an abstraction layer between your C# classes and the PostgreSQL database, allowing developers to work directly with high-level object models instead of SQL code.
  • Strong PostgreSQL Integration: EF Core provides specific support for PostgreSQL through the NPGSQL provider, which enables efficient mapping of .NET data types to PostgreSQL data types and helps optimize query performance.
  • EF Core's Optimization Techniques: EF Core provides optimizations, such as lazy loading, eager loading, and caching, that perform efficiently with PostgreSQL’s indexing and query engine.
💡
Complete PostgreSQL compatibility is made possible via EF Core's Npgsql supplier. This service optimizes query translations to work well with PostgreSQL's performance enhancements and converts C# data types to PostgreSQL types.

PostgreSQL's support for features like JSON and UUID, which are frequently used in contemporary applications, increases its usefulness in.NET applications.

How to Do Migrations and Database Updates with Neon (Code-First)

In the previous article, we reviewed setting up a Neon account. Here, we want to focus more on how EF Core works and connects with PostgreSQL. We will go ahead with a step-by-step guide on adding this code to our .NET Core projects.

💡
Elevate your EF Core development skills with Entity Framework Core - A Full Tour. Designed for developers aiming to master data access in .NET applications, this course offers in-depth coverage of Entity Framework Core's features and best practices.

Set Up Your Project

i. Ensure your project is configured to use EF Core with the Npgsql provider.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

ii. Update your appsettings.json with the Neon PostgreSQL connection string.

"ConnectionStrings": {
  "DefaultConnection": "Host=<your_neon_host>;Database=<your_db>;Username=<username>;Password=<password>"
}

You can create a new database after signing up for a free Neon account. Once you have completed the setup, navigate to the Project Dashboard and note down your PostgreSQL instance's connection credentials (Host, Database, User, Password, Port).

Replace:
<your_neon_host> with PGHOST
<your_db> with PGDATABASE
<username> with PGUSER
<password> with PGPASSWORD

Configure the DbContext

Define a DbContext that maps your C# models to database tables:

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

    public DbSet<YourEntity> YourEntities { get; set; }
}

<YourEntity> represents a C# class that models your table. While you define your data types and column names using C# syntax, as mentioned above, the NPGSQL provider will translate the native C# data types into PostgreSQL-compatible ones as needed.

Adding a Migration

Run the following command in the terminal to create a new migration:

dotnet ef migrations add <MigrationName>

This generates a migration file that captures the schema changes in code. This is at the heart of EF Core and code-first methodologies.

💡
Code-First allows developers to keep schema changes in sync with code changes. Migrations provide an easy way to version-control schema updates, ensuring smooth updates to the Neon database as your application evolves.

Applying the Migration

Use the update command to apply the migration to your Neon PostgreSQL database:

dotnet ef database update

This command generates migration files that define the initial schema based on your models.

Apply the Migration to the Neon Database

Use the update command to apply the migration to the database:

dotnet ef database update

This command connects to your Neon PostgreSQL database and applies the schema changes.

Scaffolding a Database on Neon (Database-First)

Neon introduces a novel approach to database version control by implementing a branching mechanism akin to code versioning systems like Git. This feature enables developers to create isolated, instantaneous copies of their databases, facilitating efficient development, testing, and deployment workflows.

Choosing between Code-First development with EF Core and utilizing Neon's database branching depends on the specific needs of your project. For applications where schema evolution is a primary concern, Code-First development offers robust tools for managing migrations and aligning the database schema with the application's data model. Conversely, if the focus is on creating isolated environments for development and testing, Neon's branching capabilities provide a powerful solution. In many cases, combining both approaches can yield the best results, leveraging the strengths of each to enhance development agility and maintain data integrity.

If you have chosen to develop the database on Neon and would like to use it in your project with EF Core, then the Database-First method is your best option. It is helpful to create models for a database schema you already have in Neon.

Use the dotnet ef DB context scaffold command to generate the EF Core model based on your existing database schema. Run the following command in your project's root directory:

dotnet ef dbcontext scaffold "Host=<your_neon_host>;Database=<your_db>;Username=<username>;Password=<password>" Npgsql.EntityFrameworkCore.PostgreSQL -o Models

This command generates entity classes that map to each table and a DbContext class in a Models folder. The -o option specifies the output directory.

💡
If you're reading about .NET, Entity Framework and C# development but aren't familiar with C#, don't worry—I highly recommend checking out my course, C# Console and Windows Forms Development w/ Entity Framework.

Updating the Scaffolded Models

If the Neon database schema changes, you must rerun the scaffold command to update your models. When this command is run, it will naturally fail if it detects that existing models and mapped tables exist. To force a rewrite, you must add the --force flag. This overwrites existing files in the specified output directory. The command then becomes:

dotnet ef dbcontext scaffold "Host=<your_neon_host>;Database=<your_db>;Username=<username>;Password=<password>" Npgsql.EntityFrameworkCore.PostgreSQL --force -o Models

To scaffold specific parts of your Neon-hosted database, you can utilize the --schema and --table options with the dotnet ef DB context scaffold command. This approach allows you to generate entity classes and a DbContext for selected schemas or tables, providing more control over the scaffolding process.

If your database contains multiple schemas and you want to scaffold entities from a particular schema, use the --schema option. For example, to scaffold all tables within the sales schema, run the following:

dotnet ef dbcontext scaffold "Host=your_host;Database=your_db;Username=your_user;Password=your_password" Npgsql.EntityFrameworkCore.PostgreSQL --schema sales --force -o Models/Sales

To scaffold specific tables, use the --table option. For instance, to scaffold the customers and orders tables, execute:

dotnet ef dbcontext scaffold "Host=your_host;Database=your_db;Username=your_user;Password=your_password" Npgsql.EntityFrameworkCore.PostgreSQL --table customers --table orders --force -o Models/SelectedTables

Using the --force flag will overwrite existing files without confirmation, which can lead to losing any custom modifications you've made to the generated classes. So, it is recommended that you use partial classes in a separate directory to keep more permanent modifications that should transcend each scaffolding event.

💡
Database-First scaffolding is helpful for applications where the schema is defined first or managed outside the application. By generating C# models based on the existing schema, you can quickly integrate and begin using an external database in your .NET application.

Conclusion

Entity Framework Core (EF Core) and PostgreSQL form a powerful combination for .NET developers seeking robust, efficient, and scalable data solutions. EF Core's versatility and PostgreSQL's advanced features create a harmonious environment for building high-performance applications. EF Core's compatibility with PostgreSQL allows developers to leverage PostgreSQL's rich feature set, including support for complex data types, full-text search, and JSON data handling. This integration enables the development of sophisticated applications with minimal friction.

Hosting your PostgreSQL database on Neon.tech further amplifies these benefits. Neon.tech offers a serverless PostgreSQL platform with features like autoscaling, database branching, and scale-to-zero capabilities. These functionalities streamline development workflows, reduce operational overhead, and optimize resource utilization.

The combination of EF Core and PostgreSQL provides a solid foundation for developing modern, data-intensive applications. By leveraging Neon.tech's advanced hosting solutions, developers can enhance their productivity, scalability, and cost-efficiency, positioning their applications for success in today's competitive landscape.

Published by:

Trevoir Williams

Member discussion