> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Use .NET and Entity Framework Core with ClickHouse Managed Postgres

> Connect an ASP.NET Core minimal API to ClickHouse Managed Postgres with Entity Framework Core and Npgsql, run EF Core migrations, and query over verified TLS

export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <span>Beta</span>
            </a>;
  }
  return <a href="https://clickhouse.com/docs/reference/settings/beta-and-experimental-features#beta-features" className="betaBadge">
            <span>Beta feature</span>
        </a>;
};

<BetaBadge link="https://clickhouse.com/cloud/postgres" galaxyTrack={true} galaxyEvent="docs.managed-postgres.guides-dotnet-beta" />

[Entity Framework Core](https://learn.microsoft.com/ef/core/) (EF Core) is the ORM for .NET, and [Npgsql](https://www.npgsql.org/efcore/) is its PostgreSQL provider. In this guide, you build an ASP.NET Core minimal API, create its table with an EF Core migration, and serve create, read, update, and delete endpoints. Every connection uses TLS with full certificate verification.

<h2 id="prerequisites">
  Prerequisites
</h2>

* The [.NET SDK](https://dotnet.microsoft.com/download) 10. This guide was tested with .NET SDK 10.0.401, EF Core 10.0.12, and `Npgsql.EntityFrameworkCore.PostgreSQL` 10.0.3 (Npgsql 10.0.3).
* A ClickHouse Cloud account
* [`psql`](https://www.postgresql.org/download/), to create the database. You can also run the `CREATE DATABASE` statement in the [SQL console](/integrations/connectors/sql-clients/sql-console).

<h2 id="create-service">
  Create a ClickHouse Managed Postgres service
</h2>

In the ClickHouse Cloud console, click **New service** and select **Postgres**. The instance is ready in a few minutes. See the [quickstart](/products/managed-postgres/quickstart) for a walkthrough.

<h2 id="connection-details">
  Get your connection details
</h2>

Click **Connect** in the left sidebar of your service. Keep **Directly** selected, turn on **Use SSL**, and copy the password and the server hostname. Then click **Download CA certificate**. You can also download the certificate later from **Settings → CA Certificate**. It's unique to your instance, so Npgsql can use it to verify that it's talking to your server.

This guide connects **directly** to Postgres on port `5432`, for both the app and EF Core migrations. An ASP.NET Core app is a long-lived process, and Npgsql already pools connections in it (up to 100 per connection string by default), so it doesn't need a second pooler. If you run many instances of the app, see [Use PgBouncer](#pgbouncer).

The Connect modal has no .NET tab, and Npgsql doesn't accept the `postgresql://` URL from the **url** tab. It uses `Keyword=Value` connection strings instead. Take the values from the URL and map them to Npgsql keywords:

| Connect modal (**url** tab) | Npgsql keyword |
| - | - |
| Host, for example `your-instance.pg.clickhouse.cloud` | `Host=your-instance.pg.clickhouse.cloud` |
| Port: `5432` (**Directly**) or `6432` (**via PgBouncer**) | `Port=5432` |
| Path, for example `/postgres` | `Database=postgres` |
| User and password | `Username=postgres;Password=<PASSWORD>` |
| `sslmode=verify-full` | `SSL Mode=VerifyFull` |
| `sslrootcert=<service-name>-ca-certificate.pem` | `Root Certificate=ca-certificate.pem` |

<h2 id="project-setup">
  Set up the project
</h2>

Create an ASP.NET Core project, add the Npgsql EF Core provider and the EF Core design-time package, and install the `dotnet ef` command-line tool:

```bash theme={null}
dotnet new web -o TodoApi
cd TodoApi
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef
```

<Tip>
  **Adding to an existing app?**

  Skip `dotnet new web`. If your app uses EF Core with SQLite or another provider:

  * Run `dotnet remove package Microsoft.EntityFrameworkCore.Sqlite` and `dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL`. Also install `Microsoft.EntityFrameworkCore.Design` and the `dotnet-ef` tool if you don't have them.
  * In `Program.cs`, replace `UseSqlite` with `UseNpgsql`, and remove the SQLite connection string from `appsettings.json`.
  * Migrations are provider-specific, so delete the `Migrations` folder. You create a new initial migration in [Create the schema](#migrations).

  Then continue with [Configure the connection](#configure-connection), using your app's connection string name instead of `TodoDb`, and skip [Define the model](#model).
</Tip>

<Warning>
  **On macOS, turn on TLS 1.3**

  ClickHouse Managed Postgres only accepts TLS 1.3. By default, .NET on macOS doesn't support TLS 1.3, so every connection fails with `Exception while performing SSL handshake` and the inner error `bad protocol version`. .NET 10 can use Apple's Network.framework instead, which supports TLS 1.3. Add this to the `<Project>` element of `TodoApi.csproj`:

  ```xml theme={null}
  <ItemGroup>
    <RuntimeHostConfigurationOption Include="System.Net.Security.UseNetworkFramework" Value="true" />
  </ItemGroup>
  ```

  The setting applies to the app and to `dotnet ef`, and has no effect on Linux or Windows. You can also set the environment variable `DOTNET_SYSTEM_NET_SECURITY_USENETWORKFRAMEWORK=1` instead.
</Warning>

<h2 id="configure-connection">
  Configure the connection
</h2>

Move the CA certificate you downloaded into the project directory and rename it to `ca-certificate.pem`.

Create a database for the app. Replace `<PASSWORD>` and the host with the values from the **Connect** modal:

```bash theme={null}
psql "postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/postgres?sslmode=verify-full&sslrootcert=ca-certificate.pem" -c "CREATE DATABASE guide_dotnet;"
```

```text theme={null}
CREATE DATABASE
```

Store the connection string with the [Secret Manager](https://learn.microsoft.com/aspnet/core/security/app-secrets), so that the password stays out of `appsettings.json` and source control:

```bash theme={null}
dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:TodoDb" "Host=your-instance.pg.clickhouse.cloud;Port=5432;Database=guide_dotnet;Username=postgres;Password=<PASSWORD>;SSL Mode=VerifyFull;Root Certificate=ca-certificate.pem"
```

ASP.NET Core reads user secrets in the `Development` environment, which `dotnet run` and `dotnet ef` use by default. In production, set the environment variable `ConnectionStrings__TodoDb` instead.

`SSL Mode=VerifyFull` makes Npgsql check that the server certificate is signed by the CA in `Root Certificate` and that it matches the hostname. Npgsql resolves a relative `Root Certificate` path against the current working directory, which is the project directory when you use `dotnet run` and `dotnet ef`. Use an absolute path if you start the app from somewhere else.

<Note>
  If the CA is wrong or missing, or the host doesn't match the certificate (for example, when you connect by IP address), the connection fails with `Exception while performing SSL handshake` and the inner error `The remote certificate was rejected by the provided RemoteCertificateValidationCallback`. If the `Root Certificate` file doesn't exist, the inner error is `Could not find file`. Also note that:

  * Npgsql's default is `SSL Mode=Prefer`, and `SSL Mode=Require` doesn't verify the certificate either, even when you set `Root Certificate`. Always set `SSL Mode=VerifyFull`.
  * Npgsql reads the `PGSSLROOTCERT` and `PGPASSWORD` environment variables, but not `PGHOST` or `PGSSLMODE`. The variables from the **env** tab of the Connect modal therefore aren't enough on their own.
</Note>

<h2 id="model">
  Define the model
</h2>

Create `Todo.cs` with the entity class:

```csharp title="Todo.cs" theme={null}
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDone { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}
```

Create `TodoDb.cs` with the `DbContext`:

```csharp title="TodoDb.cs" theme={null}
using Microsoft.EntityFrameworkCore;

public class TodoDb(DbContextOptions<TodoDb> options) : DbContext(options)
{
    public DbSet<Todo> Todos => Set<Todo>();
}
```

Replace the contents of `Program.cs`. It registers `TodoDb` with the Npgsql provider and maps one endpoint per operation. In an existing app, you only need the `AddDbContext` call with `UseNpgsql`:

```csharp title="Program.cs" theme={null}
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<TodoDb>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("TodoDb")));

var app = builder.Build();

app.MapGet("/todos", async (TodoDb db) =>
    await db.Todos.OrderBy(t => t.Id).ToListAsync());

app.MapGet("/todos/{id}", async (int id, TodoDb db) =>
    await db.Todos.FindAsync(id) is Todo todo ? Results.Ok(todo) : Results.NotFound());

app.MapPost("/todos", async (Todo todo, TodoDb db) =>
{
    db.Todos.Add(todo);
    await db.SaveChangesAsync();
    return Results.Created($"/todos/{todo.Id}", todo);
});

app.MapPut("/todos/{id}", async (int id, Todo input, TodoDb db) =>
{
    var todo = await db.Todos.FindAsync(id);
    if (todo is null) return Results.NotFound();

    todo.Title = input.Title;
    todo.IsDone = input.IsDone;
    await db.SaveChangesAsync();
    return Results.Ok(todo);
});

app.MapDelete("/todos/{id}", async (int id, TodoDb db) =>
    await db.Todos.Where(t => t.Id == id).ExecuteDeleteAsync() == 1
        ? Results.NoContent()
        : Results.NotFound());

// Reports the TLS version of the app's own database session
app.MapGet("/db/tls", async (TodoDb db) =>
    await db.Database
        .SqlQuery<string>($"SELECT coalesce(version, 'none') AS \"Value\" FROM pg_stat_ssl WHERE pid = pg_backend_pid()")
        .SingleAsync());

app.Run();
```

The `/db/tls` endpoint reads `pg_stat_ssl` for the app's own database session, so you can check that the connection is encrypted.

<h2 id="migrations">
  Create the schema
</h2>

Generate a migration from the model and apply it:

```bash theme={null}
dotnet ef migrations add InitialCreate
dotnet ef database update
```

```text theme={null}
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Build started...
Build succeeded.
Failed executing DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20261001144437_InitialCreate'.
Done.
```

`dotnet ef migrations add` writes the migration to the `Migrations` folder, and `dotnet ef database update` applies it and records it in the `__EFMigrationsHistory` table. On the first run, EF Core logs a failed `SELECT` from `__EFMigrationsHistory` before it creates the table; you can ignore it.

Before applying migrations, EF Core takes a lock so that two deployments can't migrate the same database at the same time. With Npgsql, the lock is a `LOCK TABLE` on `__EFMigrationsHistory` inside the migration transaction, not a session-level advisory lock, so it's released when the transaction ends.

<h2 id="verify">
  Run and verify
</h2>

Start the app on port `3115`:

```bash theme={null}
dotnet run --urls http://localhost:3115
```

```text theme={null}
info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://localhost:3115
```

In a second terminal, create two todos, mark the first one done, and delete the second:

```bash theme={null}
curl -w "\n" -X POST localhost:3115/todos -H "Content-Type: application/json" -d '{"title": "Try EF Core"}'
curl -w "\n" -X POST localhost:3115/todos -H "Content-Type: application/json" -d '{"title": "Write a migration"}'
curl -w "\n" -X PUT localhost:3115/todos/1 -H "Content-Type: application/json" -d '{"title": "Try EF Core", "isDone": true}'
curl -w "\n" -X DELETE localhost:3115/todos/2
```

```text theme={null}
{"id":1,"title":"Try EF Core","isDone":false,"createdAt":"2026-10-01T14:44:52.0478356Z"}
{"id":2,"title":"Write a migration","isDone":false,"createdAt":"2026-10-01T14:44:52.8462161Z"}
{"id":1,"title":"Try EF Core","isDone":true,"createdAt":"2026-10-01T14:44:52.047835Z"}

```

The `DELETE` request returns `204 No Content`, so its line is empty.

Check that the app's database session uses TLS:

```bash theme={null}
curl localhost:3115/db/tls
```

```text theme={null}
TLSv1.3
```

Open [http://localhost:3115/todos](http://localhost:3115/todos) in your browser to list the remaining todos:

```json theme={null}
[{"id":1,"title":"Try EF Core","isDone":true,"createdAt":"2026-10-01T14:44:52.047835Z"}]
```

To see the data in the console, open **SQL console** in the left sidebar of your service, expand `guide_dotnet` and then `public`, and click the `Todos` table. EF Core uses the class and property names as quoted identifiers, so the table is `"Todos"` and the columns are `"Id"`, `"Title"`, and so on. The table contains the remaining row:

| Id | Title | IsDone | CreatedAt |
| - | - | - | - |
| 1 | Try EF Core | true | 2026-10-01 14:44:52.047835+00 |

<h2 id="pgbouncer">
  Use PgBouncer
</h2>

Npgsql opens up to 100 connections per app instance (`Maximum Pool Size`). If many instances together approach the Postgres connection limit, connect the app through the bundled [PgBouncer](/products/managed-postgres/connection#pgbouncer) instead. Select **via PgBouncer** in the **Connect** modal, and change the port in the connection string to `6432`:

```bash theme={null}
dotnet user-secrets set "ConnectionStrings:TodoDb" "Host=your-instance.pg.clickhouse.cloud;Port=6432;Database=guide_dotnet;Username=postgres;Password=<PASSWORD>;SSL Mode=VerifyFull;Root Certificate=ca-certificate.pem"
```

The same CA certificate works. PgBouncer runs in transaction pooling mode, so each transaction can run on a different Postgres connection. With the default EF Core and Npgsql settings, you don't need to change anything else:

* **Prepared statements:** EF Core doesn't prepare statements, and Npgsql's automatic preparation is off by default (`Max Auto Prepare=0`), so queries use unnamed statements, which work through PgBouncer. Keep it that way. Through PgBouncer, prepared statements from `Max Auto Prepare` or `NpgsqlCommand.Prepare` fail intermittently with `08P01: prepared statement did not exist` (`No Reset On Close=true` prevents this), and after a migration changes a column's type, they keep failing with `cached plan must not change result type`, even on new connections. If you need prepared statements, connect directly.
* **Session state:** settings made with `SET`, including `search_path`, don't carry over between transactions. Use `SET LOCAL` inside a transaction instead. PgBouncer rejects the `Search Path` and `Options` connection string keywords with `unsupported startup parameter`; `Timezone` and `Application Name` work.
* **Migrations:** `dotnet ef database update` also works through PgBouncer, because the migration lock is a table lock that ends with its transaction.
* **TLS check:** `pg_stat_ssl` describes the connection between PgBouncer and Postgres, so `/db/tls` returns `none`. Your app's connection to PgBouncer is still verified: a wrong CA certificate fails in the same way.

<h2 id="next-steps">
  Next steps
</h2>

* [Connection](/products/managed-postgres/connection): connection strings, PgBouncer, and TLS
* [Settings](/products/managed-postgres/settings): Postgres and PgBouncer parameters, such as `max_connections`
* [Read replicas](/products/managed-postgres/read-replicas): send read-only queries to a replica
* [Security](/products/managed-postgres/security): IP access lists and private networking