Data access
Use EF Core as the default ORM. Send a change to many rows as one statement, and load entities only for the writes that need the change tracker.
Writes
-
Use
ExecuteUpdateAsyncandExecuteDeleteAsyncfor updates and deletes that don't need the entities loaded. Loading the entities, changing them, and callingSaveChangespulls every row into the change tracker, then sends a statement for each one. The set-based APIs issue oneUPDATEorDELETE. (Mukesh: Bulk operations in EF Core 10, Jovanović: What you need to know about EF Core bulk updates) -
Keep audited writes on
SaveChanges. The set-based APIs bypass the change tracker.SaveChangesinterceptors don't fire, and audit and outbox logic inSaveChangesdoesn't run. ADbCommandInterceptorstill sees each statement. The set-based APIs also can't apply concurrency control automatically, because no tracked entity holds the original concurrency token. To check it yourself, put the token in theWhereclause, and treat zero affected rows as a conflict. (Mukesh: Bulk operations in EF Core 10, Jovanović: EF Core bulk updates, Microsoft Learn: ExecuteUpdate and ExecuteDelete, dotnet/efcore: RelationalShapedQueryCompilingExpressionVisitor.cs, dotnet/efcore: RelationalCommand.cs) - Insert with
AddRangeand oneSaveChangescall, and switch to bulk copy only when a measured insert misses its time budget. Don't switch on row count alone. The batched call keeps interceptors and audit trails working, and it's fast enough for ordinary writes. Jovanović recommends it for "Small datasets (under 1,000 rows)", and Mukesh says: "For a few thousand rows, AddRange plus a single SaveChanges is plenty." The gap grows with the row count: Jovanović measured about 200 ms for 10,000 rows, against about 70 ms forSqlBulkCopy. Bulk copy skipsSaveChangestoo, so audited inserts stay on the batched call, however many rows there are. Never add and save entities one at a time in a loop: the same 10,000 rows took him almost 9 seconds. (Mukesh: Fastest way to bulk insert thousands of rows in EF Core, Jovanović: Fast SQL bulk inserts with C# and EF Core)- If you do use
SqlBulkCopy, passSqlBulkCopyOptions.CheckConstraintsandSqlBulkCopyOptions.FireTriggerswhen the table depends on either. "By default, constraints are not checked", and the server fires insert triggers only "When specified". (Microsoft Learn: SqlBulkCopyOptions)
- If you do use
Dates and times
- Model dates and times with the types in datetime.md, and let EF Core map them. On EF Core 8 and later,
DateOnlyandTimeOnlymap to SQLdateandtimecolumns. Don't store a date in aDateTime. For provider differences, and for UTC versus local storage, see datetime.md. -
On PostgreSQL, store instants as
DateTimewithKind.Utc, which Npgsql maps totimestamptz. Despite its name,timestamptzstores a UTC instant and no time zone, so aDateTimeOffset's offset has nowhere to go. Npgsql:- Rejects a
DateTimeOffsetwith a non-zero offset. - Maps
Kind.Utctotimestamptz. - Maps
LocalandUnspecifiedtotimestamp.
This "UTC everywhere" rule describes what the column can store without loss. It doesn't contradict the split in datetime.md. The instant column is UTC either way, and a future or recurring event that a person schedules still needs its local time and IANA zone ID in columns beside it. (Rojansky: PostgreSQL/.NET timestamp mapping)
- Rejects a
Indexes
- Choose indexes from the queries the application runs, and prove each one with
EXPLAIN ANALYZE. In a B-tree index, put equality columns first and the range or sort column last. Compare the plan before and after: it's the only evidence that the database uses the index. In Jovanović's demo with a million comments, a count drops from a 17 ms sequential scan to a 0.6 ms index-only scan, and a query for the newest open issues, each with its latest comment, drops from 436 ms to about 0.5 ms. Reproduce numbers like these on your own data. (Jovanović: SQL indexing explained: composite indexes and column order)
Raw SQL
-
Write raw SQL with the interpolated methods, and never pass input to a
*Rawmethod. "The FromSql and FromSqlInterpolated methods are safe against SQL injection, and always integrate parameter data as a separate SQL parameter. However, the FromSqlRaw method can be vulnerable to SQL injection attacks, if improperly used". The same split applies toExecuteSqlandExecuteSqlRaw, and toSqlQueryandSqlQueryRaw. (Microsoft Learn: SQL Queries) Use a*Rawmethod only for SQL your own code builds, such as a table name from a fixed list, and never for a value from a request.
Tenant isolation
- Enforce tenant isolation in the database with row-level security, under your global query filter. EF Core adds the filter only to the SQL it generates.
ExecuteSql, an attached entity you save, and anything behindIgnoreQueryFilters()skip it. A row-level security policy sits in the database, so it still holds when someone forgets the filter: "The database system applies the access restrictions every time that data access is attempted from any tier". (Jovanović: PostgreSQL row-level security with EF Core and Npgsql, Microsoft Learn: Row-level security) -
On PostgreSQL, make the policy apply to every role, and match no rows when no tenant is set:
- Connect the application as a role that doesn't own the tables, isn't a superuser, and doesn't have
BYPASSRLS, because a role with any of those skips policies. Run migrations as a separate role that owns the tables. - Set
FORCE ROW LEVEL SECURITYon each tenant table as well asENABLE ROW LEVEL SECURITY. The policy then covers the owner too, so a migration script can't change every tenant's rows by accident. - Compare the tenant column with
NULLIF(current_setting('app.tenant_id', true), '')in the policy. A missing or empty setting then becomesNULL, which matches no rows. - Insert into a tenant table in batches with
AddRange, however many rows there are. Bulk copy fails there: "COPY FROMis not supported for tables with row-level security".
(Jovanović: PostgreSQL row-level security with EF Core and Npgsql, PostgreSQL: COPY)
- Connect the application as a role that doesn't own the tables, isn't a superuser, and doesn't have
-
On SQL Server, add a block predicate to the security policy beside the filter predicate. A filter predicate hides other tenants' rows from
SELECT,UPDATE, andDELETE, but "The application canINSERTrows, even if they'll be filtered during any other operation".- Add an
AFTER INSERTblock predicate that uses the same function. - Add an
AFTER UPDATEblock predicate too, or denyUPDATEon the tenant column. - Read the tenant from
SESSION_CONTEXTin the predicate function.
The policy needs no equivalent of
FORCE: it already applies todbo, to members ofdb_owner, and to the table owner. (Microsoft Learn: Row-level security)On SQL Server 2019 CU 14 and later, enable trace flag 11042, which makes
SESSION_CONTEXTrun serially. Without it, "You might encounter AV exceptions or wrong results when theSESSION_CONTEXTfunction runs within a parallel execution plan when the session is reset for reuse", and a pooled connection is reset each time it's reused. Microsoft lists the issue as open, with this workaround. Drop this note once Microsoft resolves it. (Microsoft Learn: SESSION_CONTEXT (Transact-SQL)) Review by 2027-05-31. - Add an
-
Set the tenant when the connection opens, not when the request starts. EF Core opens and closes a connection for each command, and Npgsql resets pooled session state. A
SETat the start of a request runs on a connection the next query doesn't use. Disabling the reset is worse: the next request then gets the previous tenant. Override bothConnectionOpenedandConnectionOpenedAsyncin aDbConnectionInterceptor, which runs on whichever physical connection the pool hands out. EF Core calls the async method afterOpenAsync, so an interceptor with only the sync one leaves every async query with no tenant set. EF Core opens only a connection that isn't already open, so one that the application opens and passes to theDbContextskips both callbacks: set its tenant yourself, and never reuse it for another tenant. On PostgreSQL, this needs a pool that keeps the session, such as Npgsql's own or PgBouncer in session mode. PgBouncer's transaction mode never supportsSET, and each transaction can run on a different server connection, so a query can get no tenant or another client's. Behind it, set the tenant withset_config(..., true)inside an explicit transaction that holds every query, which a connection interceptor can't do. Withis_localset totrue, "the new value will only apply during the current transaction". On SQL Server, set it withsp_set_session_contextand@read_only = 1, so nothing can change the tenant until the connection returns to the pool. (Jovanović: PostgreSQL row-level security with EF Core and Npgsql, Microsoft Learn: Interceptors, Microsoft Learn: Row-level security, dotnet/efcore: RelationalConnection.cs, PgBouncer: Features, PostgreSQL: Configuration Settings Functions)
Source redundancy
The benchmark numbers under Writes come from milan-jovanovic, and this repository hasn't reproduced them. code-with-mukesh, marked Corroborate., also reports speed-ups, which this file leaves out because no unmarked source reports them. Both support the general rule: use set-based writes for set-based work, and accept losing change tracker behaviour. Before you quote a number, check it against the EF Core documentation on Microsoft Learn.
shay-rojansky has a recorded independence limit: he maintains Npgsql and works on Microsoft's EF Core team. This file cites him only for how Npgsql maps timestamps, under Dates and times, as his notes allow. When to use each type comes from jon-skeet, in datetime.md.