EF Core Advanced: Transactions, Concurrency, Raw SQL and Performance
Transactions
A single SaveChanges call is already one transaction. When you need several SaveChanges calls (or SQL commands) to succeed or fail together, start your own.
csharp
await using var tx = await db.Database.BeginTransactionAsync();
try
{
db.Accounts.First(a => a.Id == 1).Balance -= 100;
await db.SaveChangesAsync();
db.Accounts.First(a => a.Id == 2).Balance += 100;
await db.SaveChangesAsync();
await tx.CommitAsync();
}
catch
{
await tx.RollbackAsync();
throw;
}
Concurrency: Two People Edit the Same Row
User A and user B both open a product and change the price. Whoever saves last would silently overwrite the other. Optimistic concurrency detects this.
csharp
public class Product
{
public int Id { get; set; }
public required string Name { get; set; }
public decimal Price { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; } = []; // SQL Server updates it automatically
}
csharp
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
// somebody else changed the row after you read it
// option 1: reload the values and ask the user again
// option 2: let the user choose which version wins
}
Other providers:
[Timestamp] works natively with SQL Server. For SQLite or PostgreSQL use [ConcurrencyCheck] on a version number or timestamp that you update yourself, or the PostgreSQL xmin system column.
Raw SQL When You Need It
csharp
int minYear = 1900;
var books = await db.Books
.FromSql($"SELECT * FROM Books WHERE Year > {minYear}") // safe: becomes a parameter
.ToListAsync();
await db.Database.ExecuteSqlAsync($"UPDATE Books SET Price = Price * 1.05 WHERE Year < {minYear}");
Never concatenate: With
FromSql and ExecuteSql the $ interpolation is converted into parameters. Do not build the SQL string yourself with + or string.Format.
Performance Checklist
| Tip | Why |
|---|---|
AsNoTracking() for reads | Less memory, faster |
Select only needed columns | Smaller result sets |
| Avoid loading data in loops (N+1) | Fewer database round trips |
AsSplitQuery() for many Includes | Prevents huge joined result sets |
| Add indexes for filter and sort columns | Fast queries |
Paging with Skip / Take | Never load a whole table |
One DbContext per request, short lifetime | Avoid stale tracking and memory growth |
ExecuteUpdate / ExecuteDelete for bulk changes | One SQL statement |
csharp
var authors = await db.Authors
.Include(a => a.Books).ThenInclude(b => b.Tags)
.AsSplitQuery()
.ToListAsync();
Seeing the Real SQL
csharp
var query = db.Books.Where(b => b.Price > 10m);
Console.WriteLine(query.ToQueryString());
DbContext is not thread-safe: Never share one
DbContext between threads or parallel tasks. In ASP.NET Core it is registered as scoped, which gives each request its own.
Conclusion
Use transactions for multi-step changes, optimistic concurrency to avoid lost updates, interpolated FromSql for raw SQL and follow the performance checklist.
Quick Summary: Transaction = all or nothing. RowVersion detects clashes. AsNoTracking + Select + Include wisely.

