# ASP.NET Core

Integrate Socigy.OpenSource.DB into an ASP.NET Core application using the generated DI extension methods.

## Prerequisites

- `socigy.json` configured in your DB class library with `generateDbConnectionFactory: true` and `generateWebAppExtensions: true`
- At least one successful `dotnet build -c DB_Migration` run (generates the DI extension methods)
- Your API project references the DB class library

## Registering the database

Call the generated `AddAuthDb()` extension in `Program.cs`. Three overloads are available depending on your host builder type:

```csharp
// WebApplicationBuilder (most common for ASP.NET Core)
builder.AddAuthDb();

// IServiceCollection (generic host, test fixtures, etc.)
builder.Services.AddAuthDb();

// HostApplicationBuilder (generic host)
builder.AddAuthDb();
```

Replace `AuthDb` with whatever `databaseName` you set in `socigy.json`.

## What gets registered

| Service | Lifetime | Key |
|---------|----------|-----|
| `IDbConnectionFactory` | Singleton | `"AuthDb"` |
| `IMigrationManager` | Singleton | `"AuthDb"` |

Both services are registered as [keyed services](https://learn.microsoft.com/en-us/dotnet/core/extensions/dependency-injection#keyed-services). If you have multiple databases, call `AddAuthDb` and `AddUserDb` (and so on) independently — each registers under its own key.

## appsettings.json

The generated factory reads the connection string from `ConnectionStrings.{databaseName}`:

```json
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default": "Host=localhost;Port=5432;Username=app;Password=secret;Pooling=true;Minimum Pool Size=2;Maximum Pool Size=20"
    }
  }
}
```

> **NOTE** Do **not** include `Database=` — the factory appends it automatically using `databaseName` from `socigy.json`.

Add `appsettings.Development.json` for local overrides and keep production secrets out of source control.

## Applying migrations at startup

Call the generated `EnsureLatestAuthDbMigration()` extension after building the app:

```csharp
var app = builder.Build();

await app.EnsureLatestAuthDbMigration();  // runs pending migrations before serving traffic

app.MapControllers();
app.Run();
```

This is safe to call on every startup — it only applies migrations that have not yet been recorded in `_scg_migrations`.

## Full Program.cs example

```csharp
using MyApp.DB;  // generated extension methods live in the DB project's namespace

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

// Register the DB — reads ConnectionStrings.AuthDb from appsettings.json
builder.AddAuthDb();

var app = builder.Build();

// Apply any pending schema migrations
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();
```

## Injecting IDbConnectionFactory

Inject `IDbConnectionFactory` into services, controllers, or minimal API handlers using the `[FromKeyedServices]` attribute:

```csharp
public class UserService(
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db)
{
    public async Task<User?> FindByUsernameAsync(string username)
    {
        await using var conn = db.Create();  // gets the "Default" connection
        await conn.OpenAsync();

        await foreach (var user in User.Query(x => x.Username == username)
            .WithConnection(conn).ExecuteAsync())
        {
            return user;
        }
        return null;
    }
}
```

## Multiple connection keys

If your `appsettings.json` defines multiple sub-keys (e.g. `Default` and `ReadOnly`), pass the sub-key name to `Create()`:

```csharp
// Read-heavy query — use the read replica
await using var conn = db.Create("ReadOnly");
await conn.OpenAsync();

await foreach (var report in Report.Query()
    .WithConnection(conn).ExecuteAsync())
{ ... }
```

## Multiple databases

Run the generator for each DB class library with its own `databaseName`. Register each in `Program.cs`:

```csharp
builder.AddAuthDb();        // registers IDbConnectionFactory keyed "AuthDb"
builder.AddUserDb();        // registers IDbConnectionFactory keyed "UserDb"
builder.AddAnalyticsDb();
```

Inject the one you need by key:

```csharp
public class ReportService(
    [FromKeyedServices("AnalyticsDb")] IDbConnectionFactory analyticsDb,
    [FromKeyedServices("UserDb")]      IDbConnectionFactory userDb)
{ ... }
```

## Minimal API example

```csharp
app.MapGet("/users/{username}", async (
    string username,
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db) =>
{
    await using var conn = db.Create();
    await conn.OpenAsync();

    await foreach (var user in User.Query(x => x.Username == username)
        .WithConnection(conn).ExecuteAsync())
    {
        return Results.Ok(user);
    }

    return Results.NotFound();
});
```

> **TIP** Each `db.Create()` call constructs a new connection from Npgsql's connection pool. This is efficient for most workloads. For extreme throughput scenarios, create an `NpgsqlDataSource` manually and register it as a singleton alongside the factory (see [Manual connections](../integrations/manual-connections)).

## Database context & observability (v0.2.0)

Register the testable context factory alongside the existing connection factory, and wire OpenTelemetry to the library's source/meter:

```csharp
var builder = WebApplication.CreateBuilder(args);

builder.AddAuthDb();                  // IDbConnectionFactory + IMigrationManager (existing)
builder.Services.AddAuthDbContext();  // ISocigyDatabaseFactory<IAuthDb> (new)

builder.Services.AddOpenTelemetry()
    .WithTracing(t => t.AddSource("Socigy.OpenSource.DB").AddOtlpExporter())
    .WithMetrics(m => m.AddMeter("Socigy.OpenSource.DB").AddOtlpExporter());

var app = builder.Build();

// SQL logging (parameter values stay off unless explicitly enabled)
SocigyDbDiagnostics.Configure(o =>
{
    o.LoggerFactory = app.Services.GetRequiredService<ILoggerFactory>();
    o.LogLevel = LogLevel.Information;
});

await app.EnsureLatestAuthDbMigration();
app.Run();
```

Inject `ISocigyDatabaseFactory<IAuthDb>` into your services and run work inside `ExecuteAsync` / `ExecuteTransactionAsync` — see [Database context & unit of work](/database/0.3.0/core-concepts/database-context).