Skip to content

Troubleshooting — Database

Problems with the three SQL Server databases, the EF Core migrations, and data that behaves unexpectedly. Background: Database and Installation — Database Setup.

Connections

Connection string Database If it is wrong
HrConnection NKO_DB — core, 140 entities Nothing works; sign-in fails first.
WareConnection NKO_DB_Warehouse — 21 entities Sign-in and HR work. Warehouse screens (/Stock, /Asset, …) fail. That split is the tell-tale sign.
HangfireConnection The Hangfire job store Startup fails, or warehouse background jobs never run.

The two application databases are separate, with no foreign keys between them: warehouse rows refer to core employees by plain integer id. A mismatch between them — for example after restoring only one — shows up as warehouse records naming the wrong people, not as an error. Always back up and restore the pair together (see Backup & Rollback).

Migrations

Symptom Cause What to do
The Entity Framework tools version '5.0.5' is older than that of the runtime '6.0.15' The local tool manifest pins dotnet-ef 5.0.5 (R5-2). Use dotnet-ef 6.0.x — see Database Setup.
More than one DbContext was found There are two contexts. Always pass --context NKO_DBEntities1 or --context WarehouseDBContext, with the matching --project.
Invalid column name … or Invalid object name … right after a release The new code is running against an old schema. Migrations are never applied at startup — nothing calls Migrate(). Apply the release's scripts for both contexts.
There is already an object named … in the database The migrations are being run against a database that already has those tables — typically a restored backup whose __EFMigrationsHistory is behind its schema, or a database built by hand. Compare __EFMigrationsHistory with the migration list, and use migrations script --idempotent so already-applied steps are skipped.
Data gone after a migration Nine existing migrations contain DROP TABLE or DROP COLUMN. Restore from the pre-release backup — see Which migrations destroy data.

Expected counts on a correctly migrated system: 147 tables in NKO_DB (146 plus __EFMigrationsHistory), 22 in NKO_DB_Warehouse, and 18 and 5 rows in the two __EFMigrationsHistory tables.

Rows that "disappear"

A record exists in SQL but the application behaves as if it does not. Four entities carry an EF global query filter, applied to every query unless the code opts out:

Entity Filter What it hides
AppUser !IsDeleted && Status == 1 Soft-deleted employees and every employee not yet Approved. A new hire in draft cannot be picked as a manager or approver, and if their login is enabled early, sign-in crashes (R6-8).
AppUserSign !Deleted Deleted e-signatures, so HasSign returns false.
Project !IsDeleted Soft-deleted projects.
BudgetLine !IsDeleted Soft-deleted budget lines.

When a screen cannot find an employee, check the row directly:

SELECT UserId, Status, IsDeleted FROM AppUser WHERE UserId = …;

The opposite also happens. Many other tables have an IsDeleted or Deleted column but no filter, so each query must exclude deleted rows by hand. Where one forgets, deleted rows reappear (R2-4). The warehouse context has no filters at all.

Every page is slow

The top bar (TopbarViewComponent) builds its "waiting on me" badge on every authenticated page. That means about ten COUNT queries — advances, leave, overtime, training, jobs, payroll, evaluations, timesheets, notes, pending signatures — filtering on NextUserId, StatusId and CreatedBy, plus a calculation of the user's attendance delays for the current month. Only one index is declared in the whole model, the unique index on Attendance (R2-2). On a large database every page render therefore includes several table scans.

To confirm, open any simple page and watch SQL Server's active requests or Query Store: the same set of COUNT queries appears once per page view. Any index added directly in SQL is invisible to EF, so a later migration will not know about it — record it alongside the release scripts.

The payroll grid is slow for a different reason: it looks up each approver's name with a blocking call per row (R8-8).

Deleting master data

132 relationships in the core model are configured to cascade. Deleting a principal row — through the application or in SQL — silently deletes its dependents (R2-3). The per-entity Database pages list the delete behaviour of every relationship. Check them, and take a backup, before deleting anything in SQL.

After a restore

Symptom Cause
Requests waiting on the wrong person NextUserId was correct when the backup was taken. Run Refresh Next users (All) at /Admin/TriggerNextUser — mind R8-11.
Warehouse jobs fail or reference missing rows The Hangfire store still holds jobs queued against the newer data. Clear it.
Invalid column name The deployed build is newer than the restored schema. Redeploy the matching build.