The 10-Day .Net Aspire Challenge – Day 3: Add PGSQL Component

Step-by-step guide on how to use the .Net Aspire PGSQL component in Visual Studio.

Introduction

.Net Aspire framework is used to develop cloud and production-ready distributed applications. It consists of components to handle cloud-native concerns such as Redis, Postgres etc.

Prerequisites

Install .Net 8
Install Visual Studio 2022 version 17 or higher
.Net Aspire Workload
Container runtime such as Docker Desktop
10 Day .Net Aspire Challenge

Objectives

Learn how to create a starter project using .Net Aspire with the PGSQL EF Core component.

Github Sample: The solution structure is divided into the following projects

DotnetAspireChallenge.ApiService
DotnetAspireChallenge.AppHost
DotnetAspireChallenge.ServiceDefaults
DotnetAspireChallenge.Web

Getting Started

Step 1: Install the following NuGet package

Install the following Nuget package into the subsequent project “DotnetAspireChallenge.AppHost”

dotnet add package Aspire.Hosting.PostgreSQL

In the above project, register a server database and customise the PGSQL connection using the following code.

var postgres = builder.AddPostgres(“postgres”)
.AddDatabase(“pgsqldata”);

var apiService = builder.AddProject<Projects.DotnetAspireChallenge_ApiService>(“apiservice”)
.WithReference(postgres);

Step 2: Install another NuGet package

Install the following Nuget package into the subsequent project “DotnetAspireChallenge.ApiService”

dotnet add package Aspire.Npgsql.EntityFrameworkCore.PostgreSQL

then register the context into the Program.cs file as follows

builder.AddNpgsqlDbContext<PgsqlDbContext>(“pgsqldata”);

Step 3: Create a “Customer” class

public class Customer
{
public int Id { get; set; }

[Required]
public string Title { get; set; } = string.Empty;

[Required]
public string Description { get; set; } = string.Empty;
}

Step 4: Create an extension class

Create an extension class and register a minimal API get method to demonstrate the PGSQL context usage in the API Service

public static class AspirePgsqlExtension
{
public static void MapPgsqlAspireEndpoint(this WebApplication app)
{
app.MapGet(“/pgsql”, async (PgsqlDbContext pgsqlDbContext) =>
{
await pgsqlDbContext.CustomersPgsql.AddAsync(new Customer()
{
Title = “test@gmail.com”,
Description = “sukh”
});
int rows = await pgsqlDbContext.SaveChangesAsync();
if (rows > 0)
{
return await pgsqlDbContext.CustomersPgsql.FirstOrDefaultAsync();
}
else
{
return null;
}
});
}
}

internal class PgsqlDbContext(DbContextOptions options) : DbContext(options)
{
public DbSet<Customer> CustomersPgsql => Set<Customer>();
}

and finally, register in the Program.cs file

app.MapPgsqlAspireEndpoint();

Step 5: Hit the GET endpoint

Finally, navigate to the GET URL in your browser, as shown below. It will insert the specified customer into the PGSQL database, retrieve the most recently inserted row, and display it as a response.

Add additional connection string properties using the JSON syntax

{
“Aspire”: {
“Npgsql”: {
“EntityFrameworkCore”: {
“PostgreSQL”: {
“ConnectionString”: “YOUR_CONNECTIONSTRING”,
“DbContextPooling”: true,
“DisableHealthChecks”: true,
“DisableTracing”: true,
“AnotherDbContext”: {
“ConnectionString”: “AnotherDbContext_CONNECTIONSTRING”,
“DisableTracing”: false
}
}
}
}
}
}

Congratulations..!! You’ve successfully integrated the PGSQL component into the .Net Aspire project.

View Metrics

The .NET Aspire component will produce the following metrics using OpenTelemetry:

Microsoft.EntityFrameworkCore

ecMicrosoftEntityFrameworkCoreactivedb_contexts
ecMicrosoftEntityFrameworkCoretotalqueries
ecMicrosoftEntityFrameworkCorequeriesper_second
ecMicrosoftEntityFrameworkCoretotalsave_changes
ecMicrosoftEntityFrameworkCoresavechangespersecond
ecMicrosoftEntityFrameworkCorecompiledquerycachehit_rate
ecMicrosoftEntitytotalexecutionstrategyoperation_failures
ecMicrosoftEexecutionstrategyoperationfailurespersecond
ecMicrosoftEntityFramewtotaloptimisticconcurrencyfailures
ecMicrosoftEntityFoptimisticconcurrencyfailuresper_second

Npgsql

ecNpgsqlbyteswrittenper_second
ecNpgsqlbytesreadper_second
ecNpgsqlcommandspersecond
ecNpgsqltotal_commands
ecNpgsqlcurrent_commands
ecNpgsqlfailed_commands
ecNpgsqlpreparedcommandsratio
ecNpgsqlconnection_pools
ecNpgsqlmultiplexingaveragecommandsperbatch
ecNpgsqlmultiplexingaveragewritetimeper_batch

Github Project

GitHub – ssukhpinder/DotnetAspireChallenge: 10 Day .Net Aspire Challenge

More Cheatsheets

Cheat Sheets — .Net

C# Programming🚀

Thank you for being a part of the C# community! Before you leave: