Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Bulk Import with a Validation Report: Upload Excel/CSV, Staging Table, Row-Level Error File
The safe way to bring in a large, dirty Excel/CSV file is to load it into a text-column staging table with SqlBulkCopy, apply the rules to every row with set-based SQL, give the user a row-level error file and make the commit a separate, confirmed step. This post walks through a 20,000-row import with real output, including 202 Accepted plus a status endpoint, idempotency via a file hash and escaping against CSV injection.
Pagination Strategies in Depth: Offset, Keyset and Opaque Cursor Tokens
On large, changing tables keyset pagination removes offset's depth-dependent cost and page drift: in a 2-million-row test, page 5000 read 375 logical pages with offset and 6 with keyset. This post shows, with real measurements, the tie-breaker keyset condition on SQL Server, an HMAC-signed opaque cursor, the previous page and the nullable sort column trap.
Numbering: Invoice and Order Numbers, Gapless Sequences and Yearly Resets
An invoice number that must be consecutive shouldn't come from an identity, MAX + 1 or a SEQUENCE, but from a locked UPDATE on a counter table keyed by (Series, Year) inside the approval transaction; for order numbers where gaps are acceptable, a SEQUENCE is enough. The post shows GİB's 16-character document number format, gapless numbering under 50 concurrent approvals, the effect of lock duration on throughput and yearly resets by invoice date, with real output.
Settings Tables: A Global → Tenant → User Override Hierarchy
For settings that change at runtime and are overridden per tenant and per user, the most robust approach is a definition per setting (type, default, range and lowest override level) plus typed key-value rows tied to it, with the precedence rule in one service and validated, audited writes. The post compares this with EAV, typed columns, a JSON document and an external store, and shows the hidden IS NOT NULL filter EF Core adds to unique indexes on nullable columns.
Data Isolation in Multi-Tenancy: Database per Tenant, Shared Schema and Row-Level Security
For most SaaS products the best balance is shared tables with a TenantId column, a named tenant query filter in EF Core and, behind it, SQL Server row-level security fed by SESSION_CONTEXT. The post compares database per tenant, schema per tenant and shared tables, and shows tenant resolution, background jobs and automated isolation tests with real output.
Multilingual Data: Storing Names in Turkish and English — Translation Tables, JSON Columns and Fallback
For multilingual data managed by users, the most flexible option is a translation table keyed by (EntityId, Culture, Name) plus a fallback chain such as en-GB → en → tr built in one place. The post compares one column per language, translation tables, SQL Server 2025 json columns and a separate translation service, and shows the fallback with EF Core 10 LeftJoin and collation choice for the Turkish I with real output.
Polymorphic Associations: Attaching Comments and Files to Multiple Tables
Linking attachments to several tables with an EntityType/EntityId pair loses FKs, cascade delete and safe authorization; when owner types are few and stable, use an exclusive arc with one nullable FK per owner and a check constraint requiring exactly one. Derive authorization from the owner's policy, not from the attachment itself.
Hierarchical Data in SQL: Adjacency List, Recursive CTE, Closure Table and hierarchyid
When subtree queries on a department tree are frequent and moves are rare, SQL Server's hierarchyid (with LINQ support since EF Core 8) plus a ParentId for FK integrity is a good choice; use a closure table if you need database independence. Walking the tree node by node in the application causes N+1, and cycles should be blocked at move time with IsDescendantOf.
Temporal Data: Effective-Dated Records, Price History and the Bitemporal Model
Data with retroactive corrections needs two time axes: valid time (which date the fact is true for) and transaction time (when it was recorded). A SQL Server temporal table only gives transaction time; make a table with half-open [ValidFrom, ValidTo) intervals system-versioned and you can write bitemporal queries with EF Core 10.
Audit Trail: Who Changed What, When and Why?
For a reliable audit trail, write changes with a SaveChangesInterceptor as per-property old/new values plus user, correlation id and reason, in the same transaction as the business data; mask personal data and make the table append-only. Cover paths that bypass the interceptor, such as ExecuteUpdate, with summary entries and a temporal table safety net.
Doing Soft Delete Right: IsDeleted, DeletedAt, Filtered Unique Indexes and Archive Tables
To do soft delete right, filter unique indexes with WHERE DeletedAt IS NULL, link records deleted in the same operation with a DeletionId, and answer KVKK/GDPR erasure requests with anonymization rather than soft delete. If restore isn't a real requirement, an archive table or hard delete plus audit is the simpler choice.
Exposing Ids: int Identity, GUID v7 and the Public Id Split
The most balanced way to expose an id is a bigint identity as the internal clustered key, an immutable unique PublicId outside, and an ownership condition in every query. In our measurement Guid.CreateVersion7 fragmented as badly as v4 in SQL Server (99 percent, 4,987 pages), because SQL Server sorts uniqueidentifier by its last 6 bytes; EF Core's sequential generator stayed close to identity.