After establishing the separation of responsibilities between Command and Query, the next natural step is to optimize how queries are performed. In a blog-type application, where users frequently access data such as the list of posts, comments, or author profiles, query performance becomes a critical factor for the end-user experience. At this stage, we will discuss some important practices: direct projections, using AsNoTracking(), avoiding unnecessary loading through .Include(), pagination and filtering, as well as strategic caching for the most expensive queries.
1. Direct projections (Select into DTO)
One of the basic principles of separating Command and Query is that queries should not return domain entities directly, but objects optimized for display: DTOs (Data Transfer Objects).
For example, instead of loading the entire Post entity from the database, with all its relationships, we can project directly into the PostDto object:
public class GetPostsQueryHandler : IRequestHandler<GetPostsQuery, List<PostDto>>
{
private readonly BloggingDbContext _context;
public GetPostsQueryHandler(BloggingDbContext context)
{
_context = context;
}
public async Task<List<PostDto>> Handle(GetPostsQuery request, CancellationToken cancellationToken)
{
return await _context.Posts
.AsNoTracking()
.Select(p => new PostDto
{
Id = p.Id,
Title = p.Title,
AuthorName = p.Author.Name,
PublishedAt = p.PublishedAt
})
.ToListAsync(cancellationToken);
}
}
The advantage of this approach is clear: we extract from the database only the fields we need. Thus, we reduce the size of the result and avoid additional mappings in memory.
2. Avoiding .Include() if not necessary
The .Include() operator from Entity Framework is useful when we need associated relationships (e.g., posts and comments). However, excessive use leads to complex queries and redundant data.
For example, if we want to display only the title and author of a post, there is no need to load its comments as well. By using selective projections (as shown above), we completely eliminate the need for unnecessary .Include().
The basic rule is: in Queries use only the data strictly necessary for that scenario.
3. AsNoTracking() for read queries
Entity Framework, by default, tracks changes of objects loaded from the database, even if we only read them. This introduces additional performance overhead.
For Queries, where we will not modify the returned objects, it is recommended to use AsNoTracking():
var posts = await _context.Posts
.AsNoTracking()
.ToListAsync();
Thus, we eliminate entity tracking in the ChangeTracker and achieve a considerable performance increase for large queries.
4. Support for pagination, filtering, and sorting
As the database grows, it is essential not to load all records simultaneously. In a blog with hundreds or thousands of posts, pagination, filtering, and sorting are needed to provide an efficient experience for users.
An example of a Query with pagination could look like this:
public class GetPostsQuery : IRequest<PaginatedResult<PostDto>>
{
public int Page { get; set; }
public string? Filter { get; set; }
public string? Sort { get; set; }
}
public async Task<PaginatedResult<PostDto>> Handle(GetPostsQuery request, CancellationToken cancellationToken)
{
var query = _context.Posts.AsNoTracking();
if (!string.IsNullOrEmpty(request.Filter))
query = query.Where(p => p.Title.Contains(request.Filter));
if (request.Sort == "date_desc")
query = query.OrderByDescending(p => p.PublishedAt);
else
query = query.OrderBy(p => p.PublishedAt);
var total = await query.CountAsync(cancellationToken);
var items = await query
.Skip((request.Page - 1) * 10)
.Take(10)
.Select(p => new PostDto
{
Id = p.Id,
Title = p.Title,
AuthorName = p.Author.Name,
PublishedAt = p.PublishedAt
})
.ToListAsync(cancellationToken);
return new PaginatedResult<PostDto>(items, total, request.Page, 10);
}
This implementation allows the user to navigate through pages, apply filters, and sort results based on specific criteria.
5. Caching for Queries
Not all queries deserve caching, but for frequently used or costly ones (e.g., top 10 popular posts, aggregated statistics), caching can bring major benefits.
Two common options are:
-
MemoryCache – suitable for single-server applications.
-
Redis – recommended for distributed or scalable applications.
Simple example with IMemoryCache:
public class GetPopularPostsQueryHandler : IRequestHandler<GetPopularPostsQuery, List<PostDto>>
{
private readonly BloggingDbContext _context;
private readonly IMemoryCache _cache;
public GetPopularPostsQueryHandler(BloggingDbContext context, IMemoryCache cache)
{
_context = context;
_cache = cache;
}
public async Task<List<PostDto>> Handle(GetPopularPostsQuery request, CancellationToken cancellationToken)
{
return await _cache.GetOrCreateAsync("popular_posts", async entry =>
{
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5);
return await _context.Posts
.AsNoTracking()
.OrderByDescending(p => p.Views)
.Take(10)
.Select(p => new PostDto
{
Id = p.Id,
Title = p.Title,
AuthorName = p.Author.Name,
PublishedAt = p.PublishedAt
})
.ToListAsync(cancellationToken);
});
}
}
Conclusion
Query optimization is a critical step in the architecture of a CQRS-based application. By using direct projections, avoiding .Include(), applying AsNoTracking(), introducing pagination and caching, we can ensure lower response times and efficient resource usage. Thus, the application remains scalable and pleasant for users even as data volume grows.
