EF Core Connecting to PostgreSQL Databases

Introduction to PostgreSQL Databases

PostgreSQL is a powerful open source object relational database management system (RDBMS). Originally born in 1986 in the POSTGRES project at the University of California, Berkeley, PostgreSQL is known for its stability, flexibility, and extensibility. It supports rich data types, complex queries, transaction integrity, multi-version concurrency control (MVCC), stored procedures and other advanced features.

PostgreSQL is fully compliant with SQL standards and supports ACID attributes (Atomicity, Consistency, Isolation, and Durability), making it suitable for highly concurrent and data-heavy application scenarios. In addition, it is widely extensible, allowing users to define their own data types, indexing methods, functions, operators, etc. PostgreSQL's strong community constantly provides support and extensions to keep it competitive in the ever-evolving database technology. It is widely used in data analytics, financial services, web development, and other fields.

Official website address:

GitHub Address:/postgres/postgres

PostgreSQL:The World's Most Advanced Open Source Relational Database.

PosegreSQL: the world's most advanced open source relational database.

Entity Framework Corepresent (sb for a job etc)

EF Core is a modern object database mapper designed for . It supports LINQ queries, change tracking, updates, and schema migration.EF Core supports integration with SQL Server, Azure SQL Database, SQLite, Azure Cosmos DB, MySQL, PostgreSQL, and other databases through the Provider Plugin interface.

With EF Core, developers can more efficiently develop data-driven applications for a variety of application scenarios such as web applications, desktop applications, microservices, and more. Its constantly updated version and active community support make it one of the preferred ORM frameworks for .

GitHub Address:/dotnet/efcore

Document Address:/zh-cn/ef/core

fulfill

IDE：Visual Studio 2022

NET version: .NET 8

Create a new Web Api project.

This practice requires the use of 3 packages:

The first one is EF Core.

second reason is a NuGet package that provides command line tool support for Entity Framework Core. This toolkit is mainly used for creating, updating, and managing database migrations, generating database context classes and entity classes, and so on. By using this tool, developers can perform operations such as creating new migrations, applying migrations, generating database scripts, etc. from the command line or package manager console. This helps in maintaining the consistency of the database schema with the code model during the development process.

third is a provider package for using Entity Framework Core (EF Core) with PostgreSQL databases. It provides support for PostgreSQL databases with EF Core, enabling developers to use EF Core features to work with data in PostgreSQL databases. This package enables developers to use EF Core features such as LINQ querying, automatic migration, model validation, etc., as well as take advantage of PostgreSQL-specific features such as JSONB data types, full-text searching, arrays, and more.

GitHub Address:/npgsql/

First create a folder named Data in the project root directory and create an AppDbContext class.

Let's start with a general overview of the class:

First it inherits from the DbContext class.

In Entity Framework Core (EF Core), theDbContext class is one of the core components that manages all interactions with the database. It acts as a bridge between the application and the database, providing functions to query the database, save data, and configure the model.

Main functions and roles

1. Data access and query：DbContext provides a set of methods and properties that allow developers to query the database using LINQ. This is accomplished by accessing theDbSet<TEntity> attribute to perform query operations on specific entity types.
2. Object tracking and change detection：DbContext Tracks the state of entity objects retrieved from the database. When the state of an object changes (e.g., is modified, added, or deleted), theDbContext is responsible for logging these changes and callingSaveChanges() method when applying these changes to the database.
3. transaction management：DbContext Supports the management of transactions through theSaveChanges() method ensures that database operations are atomic, i.e., all operations are either all successful or all rolled back.
4. Model Configuration：DbContext Allowed by overridingOnModelCreating method to configure the mapping relationships between entity models and database tables. This includes setting primary keys, indexes, foreign key relationships, constraints, and so on.
5. life cycle management：DbContext is a configurable class whose lifecycle management can be configured as needed. Typically, it is created and released within the scope of a request or operation to ensure efficient management of database connections.

There is also a property of type IConfiguration which is injected via the constructor.IConfiguration is an interface that is primarily used to obtain configuration data for an application. It provides a standardized way to access application configuration information such as connection strings, application settings, keys for external services, etc.IConfiguration Allows developers to read configurations from a variety of sources (e.g., JSON files, environment variables, command line arguments, etc.) and uniformly map those configurations to a structured object.

In Web Api, we generally write some configurations in.

This example is shown below:

Stores the PostgreSQL connection string.

Configuration of the connection string is performed in the OnConfiguring method.

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     (("SKApiDatabase"));
 }

Now create a new Student class:

 public class Student
 {
     public int Id { get; set; }
     public string? Name { get; set; }
     public string? Home { get; set; }
 }

Add the Student table to the AppDbContext class:

 public DbSet<Student> Students { get; set; }

We are using Code First, a development pattern in the Entity Framework (including Entity Framework Core) that focuses on creating and managing database schemas by defining the application's domain model (usually using classes). This approach emphasizes writing the code to define the data models in the first place, while the database table structure is automatically generated and maintained by these data models.

1. Definition of a domain model: In the Code First pattern, the developer first defines the domain model, i.e., uses classes and properties to represent entities and relationships in the database. Through annotations (Data Annotation Properties) or fluent APIs (Fluent API), developers can specify database tables, columns, primary keys, foreign keys, indexes, and other database structure information.
2. database context class: Define a system that inherits fromDbContext class, which contains a class for the entity'sDbSet<TEntity> Attributes. These attributes correspond to tables in the database thatDbContext Classes are also responsible for managing data access and object life cycles.
3. migration: EF Core provides the Migration tool that generates and applies database update scripts based on model changes in the code. Migration helps developers upgrade the database architecture from one version to another while preserving the data.
4. Automatic database generation: At application runtime, EF Core can automatically generate a database schema based on a defined model. If the database already exists, EF Core can check and apply migrations to update the database.

vantage

1. High development efficiency: Developers can focus on code development without having to write SQL scripts directly to define the database structure.
2. Easy maintenance: Changes to the model can be easily synchronized to the database through code and migration tools.
3. type safety: The code and database model are tightly bound, reducing errors due to mismatches.

drawbacks

1. Support for complex databases: Code First may not be appropriate for large, complex databases that already exist, especially when dealing with specific database optimizations and configurations.
2. Performance issues: Automatically generated SQL may not be as efficient as manually optimized SQL.

Overall, the Code First pattern is an effective way to simplify the development and maintenance of database architectures, and is particularly well suited to new projects starting from scratch.

Now it's time to use it, open the package manager console and type in

Add-Migration "Remarks information"

After success, you will find an additional Migrations folder in the project root directory:

Migrations Folders are used to store migration files generated by the migration tool. These files record the history of changes to the database architecture, allowing developers to manage and apply those changes to maintain consistency between the database and the code model. Specifically.Migrations The role of folders and their contents includes the following:

1. Tracking changes to the database architecture: Whenever a developer makes changes to a domain model (entity class) or database context class (e.g., adds a new entity, modifies an attribute type, etc.) and generates a migration, EF Core willMigrations folder to create a new migration file. This file contains instructions that describe how the database changes from one state to another.
2. Generate and maintain migration scripts: The migration file contains two main sections:Up methodology andDown Methods.Up method defines the operations that are performed when the application is migrated, such as creating tables, adding columns, and so on. WhileDown The methods define the actions to undo these changes. With these methods, EF Core can generate SQL scripts for different database providers in order to execute the corresponding changes in the database.
3. Application Migration to Database: AdoptionUpdate-Database command or the code in the() method, EF Core applies theMigrations Migration in the folder to update the database architecture. This helps to maintain database consistency across development, test, and production environments.
4. Version control and collaboration: Migration files are common code files that can be incorporated into a version control system such as Git. This makes it easy for team members to track changes to the database architecture, review and discuss differences between versions.

Open the 20240806093127_init class and take a look:

Like the second point above says, it contains two main parts:Up methodology andDown Methods.Up method defines the operations that are performed when the application is migrated.

Down method then defines the action of undoing those changes.

Open the Package Manager console and type

Update-Database

Now open pg Admin:

pgAdmin is an open source graphical user interface tool for managing PostgreSQL databases. It provides a user-friendly interface that helps users perform database administration tasks, write and execute SQL queries, monitor database status, and more. pgAdmin is available for a variety of platforms, including Windows, macOS, and Linux.

It was found that two tables were successfully generated in our database:

One is the Students table defined in the AppDbContext class, and one is the automatically generated __EFMigrationsHistory table for recording migration history.

Above successfully used EF Core in Web Api to connect to the PostgreSQL database, then you can start to enjoy the CRUD.

consultation

1、Setup PostgreSQL in .NET with Entity Framework ()