Introduction
For experienced C# developers, working with databases is more than just writing CRUD operations. It’s about mastering performance, security, and maintainability at scale. This article explores advanced C# database techniques using ADO.NET, Entity Framework Core, and Dapper equipping you with patterns and optimizations that go beyond the basics.
Whether you're building high-throughput APIs, complex reporting dashboards, or data-intensive background workers, these tricks will help you maximize your .NET applications' database capabilities.
1. Leverage Compiled Queries in Entity Framework Core
Why
EF Core's LINQ queries are parsed and translated at runtime. For frequently executed queries, this adds overhead.
How
static readonly Func<DbContext, string, Task<Student?>> GetStudentById =
EF.CompileAsyncQuery((SchoolContext context, string id) =>
context.Students.FirstOrDefault(s => s.Id == id));
var student = await GetStudentById(context, "123");
Benefit
- Up to 3x faster query execution.
- Reduces runtime parsing cost.
2. Use Table-Valued Parameters with ADO.NET
Why
Efficiently pass large lists or tables to SQL Server in a single call.
How
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Rows.Add(1);
table.Rows.Add(2);
var cmd = new SqlCommand("GetStudentsByIds", connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@Ids", table);
SQL Side
CREATE TYPE dbo.IdList AS TABLE (Id INT);
CREATE PROCEDURE GetStudentsByIds @Ids dbo.IdList READONLY
AS
SELECT * FROM Students WHERE Id IN (SELECT Id FROM @Ids);
Benefit
Clean, performant bulk filtering.
3. Execute Multiple Result Sets
Why
Reduce round-trips by returning multiple results from a single stored procedure.
How with Dapper
using var multi = connection.QueryMultiple("sp_GetStudentAndGrades", new { id = 1 });
var student = multi.Read<Student>().First();
var grades = multi.Read<Grade>().ToList();
Benefit
Fewer database calls = lower latency and resource use.
4. Context Pooling for EF Core
Why
Creating and disposing of DbContext instances is expensive.
How
builder.Services.AddDbContextPool<SchoolContext>(options =>
options.UseSqlServer(connectionString));
Benefit
- Reuses DbContext instances safely.
- Ideal for high-traffic APIs.
5. SQL Server TVP + Dapper Integration
Why
Combine Dapper's speed with TVP flexibility.
How
var param = new DynamicParameters();
param.Add("@Ids", table.AsTableValuedParameter("dbo.IdList"));
var result = connection.Query<Student>("GetStudentsByIds", param, commandType: CommandType.StoredProcedure);
Benefit
Get the best of both worlds: TVPs + high-performance micro ORM.
6. Read-Only Contexts for Queries
Why
Avoid unintentional change tracking for queries.
How
public class ReadOnlyContext : SchoolContext
{
public ReadOnlyContext(DbContextOptions<SchoolContext> options)
: base(options) => ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
Benefit
Better performance and memory usage in reporting and read-heavy queries.
7. Use Interceptors to Log/Rewrite Queries
Why
Intercept SQL, apply custom logic, and audit.
How
public class SqlLoggingInterceptor : DbCommandInterceptor
{
public override InterceptionResult<int> NonQueryExecuting(
DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
{
Console.WriteLine($"SQL: {command.CommandText}");
return base.NonQueryExecuting(command, eventData, result);
}
}
Register
options.AddInterceptors(new SqlLoggingInterceptor());
Benefit
Helps in debugging, security auditing, or query shaping.
Conclusion
Advanced C# database techniques go far beyond basic ORM usage. By combining performance tricks like compiled queries, TVPs, Dapper integration, and EF Core optimization patterns, you can dramatically improve the scalability and maintainability of your data access layer.
Whether you're optimizing legacy systems or building high-throughput services, mastering these tools helps you write more efficient and professional .NET applications.
Would you like a GitHub sample project combining all these techniques?