CQRS Pattern - SAPFIAI

What is CQRS?

CQRS (Command Query Responsibility Segregation) is a pattern that separates read operations (Queries) from write operations (Commands).

Commands

Operations that change state and return minimal data (success/failure, ID)

Queries

Operations that read data without side effects

Why CQRS?

Separation of Concerns

Commands handle business logic and validation
Queries focus on efficient data retrieval
Different optimization strategies for reads vs writes

Scalability

Scale read and write operations independently
Use read replicas for queries
Optimize write database for transactions

Maintainability

Clear intent: “What does this code do?”
Easy to find and modify operations
Reduced coupling between operations

Performance

Queries can be optimized differently than commands
Use projections and denormalization for reads
Commands focus on consistency and validation

CQRS with MediatR

SAPFIAI implements CQRS using MediatR, a mediator pattern library for .NET.

How MediatR Works

MediatR acts as a mediator between your API and your business logic, decoupling the sender from the receiver.

Commands (Write Operations)

Commands represent actions that change the system state.

Command Structure

Every command in SAPFIAI follows this structure:

Commands/CreatePermission/
├── CreatePermissionCommand.cs          # The request
├── CreatePermissionCommandHandler.cs   # Business logic
└── CreatePermissionCommandValidator.cs # Validation rules

Command Example: Create Permission

Define the Command

The command is a record that implements IRequest<TResponse>:

// src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommand.cs
using SAPFIAI.Application.Common.Models;

namespace SAPFIAI.Application.Permissions.Commands.CreatePermission;

public record CreatePermissionCommand : IRequest<Result<int>>
{
    public required string Name { get; init; }
    public string? Description { get; init; }
    public required string Module { get; init; }
}

Location: src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommand.cs:5-10

Uses record for immutability
Returns Result<int> (success with ID or failure with error)
Properties are init-only (set once during creation)

Implement the Handler

The handler contains the business logic:

// src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommandHandler.cs
using SAPFIAI.Application.Common.Interfaces;
using SAPFIAI.Application.Common.Models;
using SAPFIAI.Domain.Entities;
using Microsoft.Extensions.Logging;

namespace SAPFIAI.Application.Permissions.Commands.CreatePermission;

public class CreatePermissionCommandHandler 
    : IRequestHandler<CreatePermissionCommand, Result<int>>
{
    private readonly IApplicationDbContext _context;
    private readonly ILogger<CreatePermissionCommandHandler> _logger;

    public CreatePermissionCommandHandler(
        IApplicationDbContext context, 
        ILogger<CreatePermissionCommandHandler> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task<Result<int>> Handle(
        CreatePermissionCommand request, 
        CancellationToken cancellationToken)
    {
        _logger.LogInformation("Creating permission: {PermissionName}", request.Name);
        
        // Check if permission already exists
        var exists = await _context.Permissions
            .AnyAsync(p => p.Name == request.Name, cancellationToken);

        if (exists)
        {
            _logger.LogWarning("Permission already exists: {PermissionName}", request.Name);
            return Result.Failure<int>(
                new Error("PermissionExists", "El permiso ya existe"));
        }

        // Create the entity
        var permission = new Permission
        {
            Name = request.Name,
            Description = request.Description,
            Module = request.Module,
            IsActive = true,
            CreatedAt = DateTime.UtcNow
        };

        // Save to database
        _context.Permissions.Add(permission);
        await _context.SaveChangesAsync(cancellationToken);

        _logger.LogInformation("Permission created successfully: {PermissionId}", permission.Id);
        return Result.Success(permission.Id);
    }
}

Location: src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommandHandler.cs:8-47

Add Validation

FluentValidation rules ensure data integrity:

// src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommandValidator.cs
namespace SAPFIAI.Application.Permissions.Commands.CreatePermission;

public class CreatePermissionCommandValidator 
    : AbstractValidator<CreatePermissionCommand>
{
    public CreatePermissionCommandValidator()
    {
        RuleFor(x => x.Name)
            .NotEmpty().WithMessage("El nombre del permiso es requerido")
            .MaximumLength(100).WithMessage("El nombre no puede exceder 100 caracteres")
            .Matches("^[a-z0-9._-]+$")
            .WithMessage("Use formato: modulo.accion (ej: users.create)");

        RuleFor(x => x.Module)
            .NotEmpty().WithMessage("El módulo es requerido")
            .MaximumLength(50).WithMessage("El módulo no puede exceder 50 caracteres");

        RuleFor(x => x.Description)
            .MaximumLength(500).WithMessage("La descripción no puede exceder 500 caracteres")
            .When(x => !string.IsNullOrEmpty(x.Description));
    }
}

Location: src/Application/Permissions/Commands/CreatePermission/CreatePermissionCommandValidator.cs:3-20

Validation runs automatically through the ValidationBehaviour pipeline before the handler executes.

Use in API

The Web layer sends the command via MediatR:

// src/Web/Endpoints/Permissions.cs
private static async Task<IResult> CreatePermission(
    IMediator mediator, 
    [FromBody] CreatePermissionCommand command)
{
    var result = await mediator.Send(command);
    return result.ToCreatedResult(id => $"/api/permissions/{id}");
}

Location: src/Web/Endpoints/Permissions.cs:83-86

Command Characteristics

State Changing

Commands modify the database or system state

Return Minimal Data

Usually return success/failure and ID of created entity

Side Effects

Can trigger events, send emails, or update caches

Validated

Always run through validation pipeline

More Command Examples

Update Command
Delete Command
Assign Command

// src/Application/Permissions/Commands/UpdatePermission/UpdatePermissionCommand.cs
public record UpdatePermissionCommand : IRequest<Result>
{
    public int PermissionId { get; init; }
    public required string Name { get; init; }
    public string? Description { get; init; }
    public required string Module { get; init; }
    public bool IsActive { get; init; }
}

Update commands include the entity ID and return Result (success/failure without data).

// src/Application/Permissions/Commands/DeletePermission/DeletePermissionCommand.cs
public record DeletePermissionCommand : IRequest<Result>
{
    public int PermissionId { get; init; }
}

Delete commands only need the ID and return simple success/failure.

// src/Application/Permissions/Commands/AssignPermissionToRole/AssignPermissionToRoleCommand.cs
public record AssignPermissionToRoleCommand : IRequest<Result>
{
    public required string RoleId { get; init; }
    public int PermissionId { get; init; }
}

Commands for relationships include both entity IDs.

Queries (Read Operations)

Queries retrieve data without modifying state.

Query Structure

Queries/GetPermissions/
├── GetPermissionsQuery.cs       # The request
└── GetPermissionsQueryHandler.cs # Data retrieval logic

Queries typically don’t need validators since they don’t modify data. Simple validation (like required IDs) can be done in the handler.

Query Example: Get Permissions

Define the Query

// src/Application/Permissions/Queries/GetPermissions/GetPermissionsQuery.cs
using SAPFIAI.Application.Common.Models;

namespace SAPFIAI.Application.Permissions.Queries.GetPermissions;

public record GetPermissionsQuery : IRequest<List<PermissionDto>>
{
    public bool ActiveOnly { get; init; } = false;
}

Location: src/Application/Permissions/Queries/GetPermissions/GetPermissionsQuery.cs:5-8

Queries return DTOs (Data Transfer Objects) like PermissionDto, not domain entities.

Implement the Handler

// src/Application/Permissions/Queries/GetPermissions/GetPermissionsQueryHandler.cs
using SAPFIAI.Application.Common.Interfaces;
using SAPFIAI.Application.Common.Models;

namespace SAPFIAI.Application.Permissions.Queries.GetPermissions;

public class GetPermissionsQueryHandler 
    : IRequestHandler<GetPermissionsQuery, List<PermissionDto>>
{
    private readonly IApplicationDbContext _context;

    public GetPermissionsQueryHandler(IApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<List<PermissionDto>> Handle(
        GetPermissionsQuery request, 
        CancellationToken cancellationToken)
    {
        var query = _context.Permissions.AsQueryable();

        // Apply filters
        if (request.ActiveOnly)
        {
            query = query.Where(p => p.IsActive);
        }

        // Project to DTO
        return await query
            .Select(p => new PermissionDto
            {
                Id = p.Id,
                Name = p.Name,
                Description = p.Description,
                Module = p.Module,
                IsActive = p.IsActive,
                CreatedAt = p.CreatedAt
            })
            .ToListAsync(cancellationToken);
    }
}

Location: src/Application/Permissions/Queries/GetPermissions/GetPermissionsQueryHandler.cs:6-36

Use in API

// src/Web/Endpoints/Permissions.cs
private static async Task<IResult> GetPermissions(
    IMediator mediator, 
    [FromQuery] bool activeOnly = false)
{
    var permissions = await mediator.Send(
        new GetPermissionsQuery { ActiveOnly = activeOnly });
    return Results.Ok(permissions);
}

Location: src/Web/Endpoints/Permissions.cs:65-68

Query Characteristics

Read-Only

Never modify the database or system state

Return Data

Return DTOs optimized for the client’s needs

Optimized

Use projections and includes for performance

Cacheable

Safe to cache since they don’t change state

More Query Examples

Get By ID
Get with Relationship
Paginated Query

// src/Application/Permissions/Queries/GetPermissionById/GetPermissionByIdQuery.cs
public record GetPermissionByIdQuery : IRequest<PermissionDto?>
{
    public int PermissionId { get; init; }
}

Returns PermissionDto? (nullable) since the permission might not exist.

// src/Application/Permissions/Queries/GetRolePermissions/GetRolePermissionsQuery.cs
public record GetRolePermissionsQuery : IRequest<List<PermissionDto>>
{
    public required string RoleId { get; init; }
}

Queries can filter by relationships (e.g., permissions for a specific role).

// Example: Paginated query
public record GetPermissionsWithPaginationQuery 
    : IRequest<PaginatedList<PermissionDto>>
{
    public int PageNumber { get; init; } = 1;
    public int PageSize { get; init; } = 10;
    public string? SearchTerm { get; init; }
}

For large datasets, use pagination with PaginatedList<T>.

MediatR Pipeline Behaviors

Behaviors are like middleware for MediatR requests, running before/after handlers.

Registered Behaviors

// src/Application/DependencyInjection.cs
services.AddMediatR(cfg => {
    cfg.RegisterServicesFromAssembly(Assembly.GetExecutingAssembly());
    cfg.AddBehavior(typeof(IPipelineBehavior<,>), typeof(UnhandledExceptionBehaviour<,>));
    cfg.AddBehavior(typeof(IPipelineBehavior<,>), typeof(AuthorizationBehaviour<,>));
    cfg.AddBehavior(typeof(IPipelineBehavior<,>), typeof(ValidationBehaviour<,>));
    cfg.AddBehavior(typeof(IPipelineBehavior<,>), typeof(PerformanceBehaviour<,>));
});

Location: src/Application/DependencyInjection.cs:14-20

Behavior Execution Order

UnhandledExceptionBehaviour

Catches and logs any unhandled exceptions:

// src/Application/Common/Behaviours/UnhandledExceptionBehaviour.cs
public async Task<TResponse> Handle(
    TRequest request, 
    RequestHandlerDelegate<TResponse> next, 
    CancellationToken cancellationToken)
{
    try
    {
        return await next();
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Unhandled exception for {Name}", 
            typeof(TRequest).Name);
        throw;
    }
}

Location: src/Application/Common/Behaviours/UnhandledExceptionBehaviour.cs

AuthorizationBehaviour

Checks if the user has required permissions:

// src/Application/Common/Behaviours/AuthorizationBehaviour.cs
public async Task<TResponse> Handle(...)
{
    var authorizeAttributes = request.GetType()
        .GetCustomAttributes<AuthorizeAttribute>();

    if (authorizeAttributes.Any())
    {
        // Check if user is authenticated
        if (_user.Id == null)
        {
            throw new UnauthorizedAccessException();
        }

        // Check permissions
        var authorizeAttributesWithPermissions = authorizeAttributes
            .Where(a => !string.IsNullOrWhiteSpace(a.Permissions));

        if (authorizeAttributesWithPermissions.Any())
        {
            // Validate user has required permissions
            foreach (var permission in authorizeAttributesWithPermissions
                .SelectMany(a => a.Permissions.Split(',')))
            {
                var hasPermission = await _identityService
                    .AuthorizeAsync(_user.Id, permission.Trim());
                
                if (!hasPermission)
                {
                    throw new ForbiddenAccessException();
                }
            }
        }
    }

    return await next();
}

Location: src/Application/Common/Behaviours/AuthorizationBehaviour.cs

ValidationBehaviour

Runs FluentValidation rules automatically:

// src/Application/Common/Behaviours/ValidationBehaviour.cs
public async Task<TResponse> Handle(
    TRequest request, 
    RequestHandlerDelegate<TResponse> next, 
    CancellationToken cancellationToken)
{
    if (_validators.Any())
    {
        var context = new ValidationContext<TRequest>(request);

        var validationResults = await Task.WhenAll(
            _validators.Select(v => 
                v.ValidateAsync(context, cancellationToken)));

        var failures = validationResults
            .Where(r => r.Errors.Any())
            .SelectMany(r => r.Errors)
            .ToList();

        if (failures.Any())
            throw new ValidationException(failures);
    }
    return await next();
}

Location: src/Application/Common/Behaviours/ValidationBehaviour.cs:15-34

PerformanceBehaviour

Logs slow requests for monitoring:

// src/Application/Common/Behaviours/PerformanceBehaviour.cs
public async Task<TResponse> Handle(...)
{
    var stopwatch = Stopwatch.StartNew();
    var response = await next();
    stopwatch.Stop();

    var elapsedMilliseconds = stopwatch.ElapsedMilliseconds;

    if (elapsedMilliseconds > 500) // Log if > 500ms
    {
        _logger.LogWarning(
            "Long Running Request: {Name} ({ElapsedMilliseconds} ms)",
            typeof(TRequest).Name, 
            elapsedMilliseconds);
    }

    return response;
}

Location: src/Application/Common/Behaviours/PerformanceBehaviour.cs

Behaviors run for every MediatR request, providing consistent cross-cutting concerns.

Best Practices

Command Best Practices

Do:

Use record types for immutability
Return Result<T> for operations that can fail
Always create validators for commands
Log important actions in handlers
Use descriptive command names (CreatePermissionCommand, not PermissionCommand)

Don’t:

Return full entities from commands (use IDs instead)
Put UI logic in command handlers
Make commands depend on each other
Skip validation for “simple” commands

Query Best Practices

Do:

Return DTOs, not domain entities
Use AsNoTracking() for read-only queries
Project early with Select() to reduce data transfer
Use pagination for large datasets
Cache query results when appropriate

Don’t:

Modify state in query handlers
Return IQueryable (execute queries in handler)
Include unnecessary data in DTOs
Forget to handle null cases

Naming Conventions

Type	Convention	Example
Command	`{Verb}{Entity}Command`	`CreatePermissionCommand`
Command Handler	`{CommandName}Handler`	`CreatePermissionCommandHandler`
Validator	`{CommandName}Validator`	`CreatePermissionCommandValidator`
Query	`Get{Entity/Entities}Query`	`GetPermissionsQuery`
Query Handler	`{QueryName}Handler`	`GetPermissionsQueryHandler`
DTO	`{Entity}Dto`	`PermissionDto`

Testing CQRS

Unit Test Example

// tests/Application.UnitTests/Permissions/Commands/CreatePermissionCommandTests.cs
[Test]
public async Task Handle_ValidCommand_CreatesPermission()
{
    // Arrange
    var context = ApplicationDbContextFactory.Create();
    var logger = Mock.Of<ILogger<CreatePermissionCommandHandler>>();
    var handler = new CreatePermissionCommandHandler(context, logger);
    
    var command = new CreatePermissionCommand
    {
        Name = "users.create",
        Description = "Create users",
        Module = "users"
    };

    // Act
    var result = await handler.Handle(command, CancellationToken.None);

    // Assert
    result.IsSuccess.Should().BeTrue();
    result.Value.Should().BeGreaterThan(0);
    
    var permission = await context.Permissions
        .FindAsync(result.Value);
    permission.Should().NotBeNull();
    permission!.Name.Should().Be("users.create");
}

Functional Test Example

// tests/Application.FunctionalTests/Permissions/CreatePermissionTests.cs
[Test]
public async Task CreatePermission_ReturnsCreated()
{
    // Arrange
    await RunAsAdministratorAsync();
    
    var command = new CreatePermissionCommand
    {
        Name = "users.create",
        Description = "Create users",
        Module = "users"
    };

    // Act
    var response = await _client.PostAsJsonAsync("/api/permissions", command);

    // Assert
    response.StatusCode.Should().Be(HttpStatusCode.Created);
    response.Headers.Location.Should().NotBeNull();
}

Next Steps

Creating Use Cases

Learn to create your own Commands and Queries

Validation

Deep dive into FluentValidation

Testing

Testing Commands and Queries

Result Pattern

Error handling with Result<T>

Get Started

Architecture

Core Concepts

Development

Security

Deployment

​What is CQRS?

Commands

Queries

​Why CQRS?

​CQRS with MediatR

​How MediatR Works

​Commands (Write Operations)

​Command Structure

​Command Example: Create Permission

​Command Characteristics

State Changing

Return Minimal Data

Side Effects

Validated

​More Command Examples

​Queries (Read Operations)

​Query Structure

​Query Example: Get Permissions

​Query Characteristics

Read-Only

Return Data

Optimized

Cacheable

​More Query Examples

​MediatR Pipeline Behaviors

​Registered Behaviors

​Behavior Execution Order

​Best Practices

​Command Best Practices

​Query Best Practices

​Naming Conventions

​Testing CQRS

​Unit Test Example

​Functional Test Example

​Next Steps

Creating Use Cases

Validation

Testing

Result Pattern

Build docs developers (and LLMs) love

What is CQRS?

Why CQRS?

CQRS with MediatR

How MediatR Works

Commands (Write Operations)

Command Structure

Command Example: Create Permission

Command Characteristics

More Command Examples

Queries (Read Operations)

Query Structure

Query Example: Get Permissions

Query Characteristics

More Query Examples

MediatR Pipeline Behaviors

Registered Behaviors

Behavior Execution Order

Best Practices

Command Best Practices

Query Best Practices

Naming Conventions

Testing CQRS

Unit Test Example

Functional Test Example

Next Steps