EF Core Advanced: Transactions, Concurrency, Raw SQL and Performance

Dot NET Programming 15 min min read Updated: Nov 16, 2026 Advanced
EF Core Advanced: Transactions, Concurrency, Raw SQL and Performance
Advanced Topic 6 of 6

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

TipWhy
AsNoTracking() for readsLess memory, faster
Select only needed columnsSmaller result sets
Avoid loading data in loops (N+1)Fewer database round trips
AsSplitQuery() for many IncludesPrevents huge joined result sets
Add indexes for filter and sort columnsFast queries
Paging with Skip / TakeNever load a whole table
One DbContext per request, short lifetimeAvoid stale tracking and memory growth
ExecuteUpdate / ExecuteDelete for bulk changesOne 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.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators