Introduction

Building performant REST APIs with Entity Framework Core (EF Core) and SQL Server is crucial for applications handling large datasets. Slow API responses can hinder user experience and scalability. This guide explores essential strategies to enhance the performance of your EF Core-based REST API, from optimizing database queries and leveraging caching to employing efficient data retrieval techniques and implementing pagination. By integrating these techniques into your C# project, you can ensure faster, more efficient API responses, ultimately leading to a more robust and scalable application.

When working with large SQL Server databases and Entity Framework (EF) in a C# project, ensuring that your REST API is performant requires a multi-faceted approach. Below are key strategies and examples to help improve the performance of your EF-based REST API:

1. Optimize Database Queries

a. Use Asynchronous Operations

EF Core supports asynchronous operations, which can help improve the responsiveness of your API by not blocking the thread while waiting for the database to respond.

public async Task<IActionResult> GetItems()
{
     var items = await _context.Items.ToListAsync();
     return Ok(items);
}

b. Efficient Querying with Projections

Fetching only the necessary data using projections can greatly reduce the amount of data transferred from the database.

public async Task<IActionResult> GetItemSummaries()
{
var itemSummaries = await _context.Items
.Select(item => new { item.Id, item.Name, item.Price })
.ToListAsync();
return Ok(itemSummaries);
}

c. Use Explicit Loading or No Tracking

Explicitly load related data only when necessary, and use .AsNoTracking() for read-only operations to avoid the overhead of change tracking.

// Explicit Loading
var order = await _context.Orders.FindAsync(orderId);
await _context.Entry(order).Collection(o => o.Items).LoadAsync();

// No Tracking
var orders = await _context.Orders.AsNoTracking().ToListAsync();

2. Caching

a. In-Memory Caching

For data that doesn’t change frequently, consider caching it in memory to avoid repeated database hits.

public async Task<IActionResult> GetCachedItems()
{
  var cacheKey = "items_cache";
  if (!_memoryCache.TryGetValue(cacheKey, out List<Item> items))
  {
    items = await _context.Items.ToListAsync();
    var cacheOptions = new MemoryCacheEntryOptions()
    .SetSlidingExpiration(TimeSpan.FromMinutes(5));

  _memoryCache.Set(cacheKey, items, cacheOptions);
}
return Ok(items);
}

b. Distributed Caching

Use distributed caching (e.g., Redis) for larger applications or applications running on multiple servers.

3. Database Indexing

Ensure that your SQL Server database has appropriate indexes to speed up data retrieval. Use tools like SQL Server Management Studio (SSMS) to analyze and optimize indexes.

CREATE INDEX IX_Items_Name
ON Items (Name);

4. Pagination

Implement pagination for API endpoints that return large datasets to reduce the amount of data transferred in each request.

public async Task<IActionResult> GetPagedItems(int pageNumber = 1, int pageSize = 10)
{
   var items = await _context. Items
   .Skip((pageNumber - 1) * pageSize)
   .Take(pageSize)
   .ToListAsync();
   return Ok(items);
}

5. Batching and Bulk Operations

For operations that involve a large number of records, use batching or bulk operations to reduce the number of round trips to the database.

public async Task<IActionResult> BulkInsertItems(List<Item> newItems)
{
  await _context.Items.AddRangeAsync(newItems);
  await _context.SaveChangesAsync();
  return Ok();
}

6. Use Stored Procedures and Views

For complex queries or operations, consider using stored procedures or database views to optimize performance.

//SQL

CREATE VIEW View_ActiveItems AS
SELECT Id, Name, Price FROM Items WHERE IsActive = 1;

public async Task<IActionResult> GetActiveItems()
{
   var activeItems = await _context.ActiveItems.ToListAsync();
   return Ok(activeItems);
}

7. Database Connection Pooling

Ensure your application is using connection pooling to manage database connections efficiently. This is often configured in your connection string.

options.UseSqlServer(“Server=.;Database=MyDb;Trusted_Connection=True;Max Pool Size=100;”);

8. Profiling and Monitoring

Use profiling tools like SQL Server Profiler or Application Insights to identify and diagnose performance bottlenecks.

// Example: Profiling with SQL Server Profiler

9. Optimize Entity Framework Settings

 

a. Query Splitting

EF Core 5.0 introduced query splitting to avoid N+1 queries when including related data.

var orders = await _context.Orders .Include(o => o.Items) .AsSplitQuery() .ToListAsync();

b. Adjust Tracking Behavior

Adjust EF Core’s tracking behavior based on the scenario. For read-only queries, disable tracking to improve performance.

var items = await _context.Items.AsNoTracking().ToListAsync();

Complete Example

Combining these strategies, here’s a complete example of an optimized controller method:

[ApiController]
[Route("api/[controller]")]
public class ItemsController : ControllerBase
{
private readonly AppDbContext _context;
private readonly IMemoryCache _memoryCache;

public ItemsController(AppDbContext context, IMemoryCache memoryCache)
{
  _context = context;
  _memoryCache = memoryCache;
}

[HttpGet("paged")]
public async Task<IActionResult> GetPagedItems(int pageNumber = 1, int pageSize = 10)
{
   var cacheKey = $"items_{pageNumber}_{pageSize}";

 if (!_memoryCache.TryGetValue(cacheKey, out List<Item> items))
 {
    items = await _context.Items
   .AsNoTracking()
  .Skip((pageNumber - 1) * pageSize)
  .Take(pageSize)
  .ToListAsync();

 var cacheOptions = new MemoryCacheEntryOptions()
 .SetSlidingExpiration(TimeSpan.FromMinutes(10));
 _memoryCache.Set(cacheKey, items, cacheOptions);
}

 return Ok(items);
}
}

Improving the performance of your EF-based REST API involves optimizing both your database and your application code. By following these strategies, you can significantly enhance the responsiveness and efficiency of your API.