Table of Contents

Data Access

Scope: This article is the NCAT implementation reference for generated behavior. Broader architectural rationale, alternatives, and tradeoffs live in ASI Backbone Learning; Learning is educational guidance, not a dependency of NCAT behavior.

Implementation Locations

EF Core, SQLite, and Database Updates

The application includes an initial EF Core data access foundation using SQLite as the default local development provider.

SQLite is used as the default development provider because it is lightweight, file-based, and does not require a separate database server.

The SQLite connection string is configured in:

src/ProjectTemplate.Web/appsettings.json

The default connection string uses a local SQLite database file:

"ConnectionStrings": {
  "ApplicationDatabase": "Data Source=application-dev.db"
}

Data Access Configuration

Data access behavior is configured under ProjectTemplate:DataAccess:

"ProjectTemplate": {
  "DataAccess": {
    "Provider": "Sqlite",
    "ConnectionStringName": "ApplicationDatabase",
    "Auditing": {
      "Enabled": true,
      "StorageMode": "Local"
    }
  }
}

The configuration values are:

Setting Purpose
Provider Selects the data access mode. Supported values are Sqlite, SqlServer, None, and Disabled. The default local development provider is Sqlite.
ConnectionStringName Identifies which named connection string should be resolved from ConnectionStrings when data access is enabled. This value is not required when Provider is None or Disabled.
Auditing:Enabled Enables or disables EF Core audit record creation during SaveChanges and SaveChangesAsync when EF Core data access is enabled.
Auditing:StorageMode Selects the audit storage path. The built-in default is Local. Outbox and ExternalSink are extension modes for consuming applications that register a custom IApplicationAuditStore.

By default, the application uses:

ProjectTemplate:DataAccess:Provider = Sqlite
ProjectTemplate:DataAccess:ConnectionStringName = ApplicationDatabase
ProjectTemplate:DataAccess:Auditing:Enabled = true
ProjectTemplate:DataAccess:Auditing:StorageMode = Local

With the default configuration, the application resolves:

ConnectionStrings:ApplicationDatabase

EF Core migrations are stored in the infrastructure project because ProjectTemplate.Infrastructure owns the ApplicationDbContext, entities, and EF Core configuration.

The web project is used as the startup project because it provides application configuration, dependency injection, provider setup, and connection-string resolution.

Disabled Data Access Mode

Applications that do not need the template's EF Core data access layer can opt out explicitly:

"ProjectTemplate": {
  "DataAccess": {
    "Provider": "None"
  }
}

Disabled is also accepted as an alias:

"ProjectTemplate": {
  "DataAccess": {
    "Provider": "Disabled"
  }
}

When data access is disabled, the application does not register:

  • ApplicationDbContext
  • IDbContextFactory<ApplicationDbContext>
  • EF-backed application services that require ApplicationDbContext

Disabled mode also does not require ProjectTemplate:DataAccess:ConnectionStringName to resolve to an existing connection string. This mode is appropriate for lightweight applications, workers, external modules, static front ends, or services that use a separate persistence strategy.

Disabled mode should not be used by applications that need EF Core migrations, audit records, external login account persistence, or any service that depends on ApplicationDbContext.

EF Core CLI Tool

The dotnet ef command requires the EF Core command-line tool.

Check whether the tool is available:

dotnet ef --version

If the command is not found, install or update the tool:

dotnet tool install --global dotnet-ef

Or update an existing global installation:

dotnet tool update --global dotnet-ef

Add a Migration

Create a new migration from the repository root:

dotnet ef migrations add MigrationName `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext `
  --output-dir Data/Migrations

Replace MigrationName with a descriptive name, such as:

dotnet ef migrations add AddExternalLoginAccounts `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext `
  --output-dir Data/Migrations

Update the Local Database

Apply pending migrations to the configured local SQLite database:

dotnet ef database update `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext

This creates or updates the local SQLite database using the ApplicationDatabase connection string resolved by the startup project.

Verify Pending Migrations

List available migrations:

dotnet ef migrations list `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext

Generate a SQL script for review:

dotnet ef migrations script `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext `
  --output migration.sql

The template does not distribute a pre-generated SQL script. Generate the script from the current migrations in the consuming application so it matches the selected provider, application version, and intended database state.

Connection String Resolution

Migration commands use the startup project to resolve configuration.

For this application, configuration may come from:

src/ProjectTemplate.Web/appsettings.json
src/ProjectTemplate.Web/appsettings.{Environment}.json
user secrets
environment variables
other configured providers

The application reads the configured connection string name from:

ProjectTemplate:DataAccess:ConnectionStringName

By default, this value is:

ApplicationDatabase

The application then resolves the matching named connection string from:

ConnectionStrings:ApplicationDatabase

For local development, the default SQLite value is:

Data Source=application-dev.db

Applications can override either the selected connection string name or the connection string value through normal ASP.NET Core configuration sources.

For example, an environment variable can override the connection string value:

ConnectionStrings__ApplicationDatabase=Data Source=custom-application-dev.db

An environment variable can also select a different configured connection string name:

ProjectTemplate__DataAccess__ConnectionStringName=ApplicationSqlServer

When ConnectionStringName is set to ApplicationSqlServer, the application resolves:

ConnectionStrings:ApplicationSqlServer

When Provider is set to None or Disabled, connection string resolution is skipped.

EF Core Auditing

The template includes a baseline EF Core audit trail.

ApplicationDbContext.SaveChanges and SaveChangesAsync are overridden to generate audit records for tracked entity changes when auditing is enabled.

Audit records may include:

  • Entity/table name
  • Entity state
  • Key values
  • Original values
  • Current values
  • Actor information
  • Application context
  • UTC timestamp

Auditing is enabled by default and uses local audit storage by default:

"ProjectTemplate": {
  "DataAccess": {
    "Auditing": {
      "Enabled": true,
      "StorageMode": "Local"
    }
  }
}

To disable audit record creation:

"ProjectTemplate": {
  "DataAccess": {
    "Auditing": {
      "Enabled": false
    }
  }
}

When the application starts, the data access startup log records the configured provider, connection string name, EF Core auditing status, and audit storage mode. If data access is disabled, the startup log reports that EF Core services were not registered.

When auditing is enabled with StorageMode set to Local, audit records are written to the application database through the local EF Core audit record table. The template does not include automatic pruning, retention, archival, legal hold, masking, export, or purge behavior for audit records.

For a generated --dbProvider sqlserver scaffold, NCAT intentionally omits the checked-in SQLite migration history. At startup, the data-access diagnostic inspects the compiled infrastructure assembly for EF Core migrations. If SQL Server is configured and no migration history is present, NCAT emits a prominent structured warning and continues startup.

The warning includes the explicit command to create an initial provider-compatible migration:

dotnet ef migrations add InitialSqlServer `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext `
  --output-dir Data/Migrations

Review the generated migration before applying it. NCAT does not create migrations or run dotnet ef database update automatically. SQLite scaffolds retain their checked-in migration history and do not produce this warning; None/Disabled data-access scaffolds remain unaffected.

Consuming applications are responsible for deciding how audit records are retained, archived, masked, purged, or moved to long-term storage. Before enabling auditing in production, review whether audited values may contain sensitive or regulated data.

Audit Storage Extension Path

The template owns a small audit storage seam through IApplicationAuditStore.

LocalApplicationAuditStore is the built-in default implementation. It appends audit records to ApplicationDbContext.AuditRecords and leaves persistence under the existing ApplicationDbContext.SaveChanges / SaveChangesAsync flow.

Supported storage mode names are:

Storage mode Behavior
Local Built-in default. Audit records are stored in the application database through the local EF Core audit record table.
Outbox Extension mode for applications that want to capture audit records into an outbox table or durable handoff before later export. A custom IApplicationAuditStore registration is required.
ExternalSink Extension mode for applications that want to send or adapt audit records to an external sink, governance package, SIEM pipeline, or companion package such as AsiBackbone. A custom IApplicationAuditStore registration is required.

A custom audit store can be registered in dependency injection before or after the template's data-access registration. The last registered IApplicationAuditStore wins under normal ASP.NET Core dependency injection behavior.

For non-local modes, provide a custom store intentionally:

services.AddScoped<IApplicationAuditStore, MyApplicationAuditStore>();

Then configure the desired mode:

"ProjectTemplate": {
  "DataAccess": {
    "Auditing": {
      "Enabled": true,
      "StorageMode": "ExternalSink"
    }
  }
}

The local mode is the safest default because audit records are captured through the same EF Core save pipeline as the application data. Outbox and external sink modes create a scaling boundary, but the consuming application owns failure handling, retry behavior, delivery guarantees, sensitive-data minimization, retention, and whether audit writes should block the primary data save.

Use an outbox-style store when audit durability should remain local first but export can happen later. Use an external sink adapter when another package or platform owns the downstream governance/audit workflow.

Persisted String Canonicalization

The template canonicalizes string scalar values for added entities and modified string properties before EF Core persists changes.

Canonicalization decodes bounded HTML/entity encoding and normalizes Unicode into a stable representation before save. This prevents common single-encoded or double-encoded values from being stored inconsistently while preserving raw special characters such as apostrophes, quotation marks, ampersands, and accented characters.

This behavior is intended for persistence consistency and defense-in-depth. It is not a replacement for SQL injection protection.

The primary SQL injection protections remain:

  • EF Core parameterized commands.
  • Avoiding manually concatenated SQL.
  • Validating application input according to domain rules.
  • Keeping output encoding context-specific.

The template does not blanket HTML-encode values before database storage. Razor/UI output encoding and any API-specific encoding rules remain the responsibility of the output layer.

String Comparison and Lookup Normalization

The template separates display values from lookup values.

Persisted display strings preserve user-facing casing where practical, but lookup-sensitive values use explicit normalization rules so behavior is not accidentally determined by provider-specific collation behavior.

Default rules:

Value Rule
Provider name Trim, Unicode Form C, preserve display casing
Normalized provider name Trim, Unicode Form C, uppercase invariant
Provider user ID Trim, Unicode Form C, case-sensitive
Display name Trim, Unicode Form C, preserve casing
Email Trim, Unicode Form C, preserve casing
Normalized email Trim, Unicode Form C, uppercase invariant

The external login unique key uses NormalizedProviderName plus ProviderUserId. This makes provider-name matching case-insensitive while preserving provider user IDs as exact, case-sensitive external identifiers.

SQLite and SQL Server can differ in default collation and case-sensitivity behavior. The template avoids relying on those defaults for provider-name lookup by storing a normalized provider-name value. Future applications that add slugs, usernames, tenant names, email login, or other lookup-sensitive fields should follow the same pattern: preserve the display value, store a normalized lookup value, and place uniqueness constraints on the normalized value.

Date and Time Persistence

System and audit timestamps use UTC DateTime properties with a Utc suffix.

The template treats these values as persistence timestamps, not display timestamps. They are generated in UTC, normalized to millisecond precision before save, and should be converted to a user's local time zone only at the display/API boundary when a consuming application needs that behavior.

Provider notes:

  • SQL Server supports explicit precision for DateTime/DateTimeOffset values. UTC DateTime system fields are configured with millisecond precision.
  • SQLite stores these values as TEXT; provider precision behavior differs from SQL Server, so the application normalizes timestamp values before persistence to keep tests and comparisons stable.
  • Future application fields that need to preserve a caller-provided offset should use DateTimeOffset intentionally rather than overloading UTC DateTime fields.

Do not store local time in system/audit *Utc columns.

Decimal Precision and Scale

The generated baseline currently defines no persisted decimal entity properties. NCAT's model-validation contract requires future persisted decimal properties to declare precision and scale explicitly rather than inheriting provider defaults. ApplicationDbContextDecimalPrecisionTests fails when that invariant is violated.

Consuming applications remain responsible for domain-appropriate precision, scale, range, and rounding semantics.

Raw SQL and Parameterization Safety

The generated baseline does not require raw SQL command construction. The NCAT contract is that dynamic database values are parameterized rather than concatenated into SQL text.

If a consuming application adds raw SQL, use EF Core interpolated/parameter APIs or explicit provider parameters. Persisted-string canonicalization is not a substitute for SQL parameterization.

Optimistic Concurrency

The template includes baseline optimistic concurrency detection for entities that inherit from DataEntity.

Each data entity includes a ConcurrencyStamp value. EF Core configures this value as a concurrency token. When an entity is updated or deleted, EF Core includes the original concurrency token value in the database update check. If another context or application instance has already changed the row, the update affects zero rows and EF Core throws DbUpdateConcurrencyException.

The template uses an application-managed string concurrency stamp instead of a database-generated SQL Server rowversion. This keeps the default behavior provider-safe for SQLite local development while still supporting SQL Server-oriented production paths.

ApplicationDbContext.SaveChanges and SaveChangesAsync refresh the concurrency stamp for modified entities before saving. Concurrency conflicts are logged and rethrown. The template does not automatically retry, merge, or overwrite conflicting changes.

Consuming applications should decide how to handle conflicts based on domain needs. Common options include showing the user a reload-and-retry message, re-querying the current database values, or implementing an application-specific merge workflow.

Automatic Startup Migrations

The application does not automatically run EF Core migrations during application startup.

This is intentional.

Automatic startup migration execution can be useful for small local development scenarios, but it can be unsafe in production because multiple application instances may start at the same time, schema changes may require review, and failed migrations can prevent the application from starting cleanly.

For now, database migration execution should remain an explicit developer or deployment action.

A future issue may add an opt-in startup migration feature for development-only or controlled hosting scenarios, but it should include clear safeguards before being used.

Production database updates should be handled outside normal application startup.

Recommended options include:

  • CI/CD pipeline migration steps.
  • Reviewed SQL migration scripts.
  • Manual DBA-approved migration execution.
  • Dedicated deployment jobs that run before the application is released.
  • Environment-specific connection strings supplied through deployment secrets or secure configuration.

Before applying migrations to production: 1. Review the generated migration. 2. Generate and inspect the SQL script. 3. Back up the target database when appropriate. 4. Apply the migration through a controlled deployment process. 5. Confirm the application version and database schema are compatible.

Example script generation command:

dotnet ef migrations script `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext `
  --output migration.sql

SQLite does not support EF Core's --idempotent script option. Generate a script for the known source and target migration state, review it, and apply it through the controlled deployment process. After creating provider-compatible SQL Server migrations, SQL Server consumers can add --idempotent when the target database may have only some migrations applied.

SQLite Development Flow

A common local development flow is:

dotnet restore
dotnet build --configuration Release
dotnet ef database update `
  --project src/ProjectTemplate.Infrastructure `
  --startup-project src/ProjectTemplate.Web `
  --context ApplicationDbContext
dotnet test --configuration Release

To recreate the local SQLite database from scratch, stop the application, delete the local .db file, and run dotnet ef database update again.

Only do this for disposable local development databases.

Troubleshooting

If dotnet ef is not recognized, install or update the EF Core CLI tool:

dotnet tool install --global dotnet-ef

If the startup project cannot be built, run:

dotnet build --configuration Release

If the connection string cannot be found, confirm that ConnectionStrings:ApplicationDatabase exists in the startup project configuration.

If migrations are not discovered, confirm that the command uses:

--project src/ProjectTemplate.Infrastructure
--startup-project src/ProjectTemplate.Web
--context ApplicationDbContext

If data access provider configuration fails, confirm that ProjectTemplate:DataAccess:Provider is configured. Use Sqlite or SqlServer when EF Core data access is required. Use None or Disabled only when the application intentionally does not need EF Core registrations.

SQLite remains the default development provider and retains the template's checked-in migration history. A project generated with --dbProvider sqlserver omits the SQLite Data/Migrations folder and SQLite migration-specific tests. Generate and review a fresh SQL Server migration before applying database updates.

When changing an existing generated application from SQLite to SQL Server before deployment, delete the SQLite migration folder and regenerate the history with the SQL Server provider selected. Do not delete or replace migrations that have already been applied to a shared or production database without an explicit migration and data-preservation plan.

External Login Account Linking Persistence

The application includes an optional EF Core persistence model for applications that need to link external provider identities to local application users.

This is different from claims-only sign-in.

Claims-only sign-in uses the external provider claims from the current authentication session and does not require a local account-linking table. This is sufficient when the application only needs to authenticate the current request.

Local account linking is useful when an application needs to associate one or more external identities with a local application user profile, preserve account-link audit history, support provider migration, or allow multiple providers to sign in to the same local account.

The external login persistence model stores:

  • Local user ID
  • Provider name
  • Provider user ID
  • Display name
  • Email
  • Created, updated, and last-login timestamps

Provider tokens are not stored by default. Applications that need token persistence should add that behavior intentionally and review the security, encryption, rotation, and retention requirements before enabling it.

Contract References

Representative contracts are covered by DataAccessServiceExtensionsTests.cs, InfrastructureDataAccessServiceExtensionsTests.cs, concurrency/timestamp/decimal tests, and ADR-0004.

For deeper NCAT implementation detail, see EF Core Save Pipeline and DbContext Audit State Isolation.

Learn the Pattern

For general EF Core boundary design and transaction reasoning, see Data-Access Boundaries and Transaction Reasoning with EF Core.

Edit this page