Data access
Use EF Core as the default ORM. Write set-based work as set-based SQL, and keep the change tracker for the writes that need it.
Opinions
-
Use
ExecuteUpdateAsyncandExecuteDeleteAsyncfor updates and deletes that don't need the entities loaded. Load-modify-SaveChangespulls every row into the change tracker to emit statements the database could run by itself. The set-based APIs issue oneUPDATEorDELETE. Mukesh and Jovanović measure them hundreds of times faster on 10,000-row operations. (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. Interceptors don't fire, audit and outbox logic inSaveChangesdoesn't run, and global query filters don't apply to the predicate, so a soft-delete filter is silently missing. Write the filter into theWhereclause, or keep the write onSaveChanges. (Mukesh: Bulk operations in EF Core 10, Jovanović: EF Core bulk updates) - 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
-
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 on issues and users 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) - Enforce tenant isolation with PostgreSQL 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 applies to every statement from every non-exempt role, so it still holds when someone forgets the filter. To make it hold:- Connect as a role that owns nothing, because owners, superusers, and
BYPASSRLSroles skip policies. Run migrations as a separate owner role. - Add
FORCE ROW LEVEL SECURITY, so the policy covers the owner too. - Write the predicate so that an unset tenant matches no rows. (Jovanović: PostgreSQL row-level security with EF Core and Npgsql)
- Connect as a role that owns nothing, because owners, superusers, and
- 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. OverrideConnectionOpenedin aDbConnectionInterceptor, which runs on whichever physical connection the pool hands out. (Jovanović: PostgreSQL row-level security with EF Core and Npgsql) -
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. -
Insert with
AddRangeand oneSaveChangescall, and switch to bulk copy only above about 10,000 rows. The batched call keeps interceptors and audit trails working, and it's fast enough for ordinary writes. Never add and save entities one at a time in a loop: it's about ten times slower. (Mukesh: Fastest way to bulk insert thousands of rows in EF Core)
Source redundancy
The benchmark numbers in the first opinion come from code-with-mukesh, marked Corroborate., and milan-jovanovic, and this repository hasn't reproduced them. Both support the general rule: use set-based writes for set-based work, and accept losing change tracker behaviour. Before you quote the ratios, check them 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 the mapping works, as his notes allow. When to use those types comes from jon-skeet, in datetime.md.