dotnet-cqrs-query-generator

CQRS Query Generator

Overview

This skill generates Queries following the CQRS pattern. Queries are read-only operations that return data without modifying state. Key principles:

• Queries never modify state - Read-only operations
• Use Dapper for reads - Bypass EF Core for performance
• Return DTOs, not entities - Projection to response models
• Direct SQL queries - Optimized for the specific use case

Quick Reference

| Query Type | Use Case | Returns | |------------|----------|---------| | GetById | Single entity by ID | Result<EntityResponse> | | GetAll | All entities (with optional filtering) | Result<IReadOnlyList<EntityResponse>> | | GetPaged | Paginated list | Result<PagedList<EntityResponse>> | | Search | Filtered/searched results | Result<IReadOnlyList<EntityResponse>> | | Exists | Check if entity exists | Result<bool> |

Query Structure

/Application/{Feature}/
├── Get{Entity}ById/
│   ├── Get{Entity}ByIdQuery.cs       # Query + Validator + Handler
│   └── {Entity}Response.cs            # Response DTO
├── GetAll{Entities}/
│   ├── GetAll{Entities}Query.cs
│   └── {Entity}ListResponse.cs
└── Get{Entities}ByOrganization/
    ├── Get{Entities}ByOrganizationQuery.cs
    └── {Entity}ByOrganizationResponse.cs

Template: Get By ID Query

// src/{name}.application/{Feature}/Get{Entity}ById/Get{Entity}ByIdQuery.cs
using System.Data;
using Dapper;
using FluentValidation;
using {name}.application.abstractions.data;
using {name}.application.abstractions.messaging;
using {name}.domain.abstractions;
using {name}.domain.{entities};

namespace {name}.application.{feature}.Get{Entity}ById;

// ═══════════════════════════════════════════════════════════════
// QUERY RECORD
// ═══════════════════════════════════════════════════════════════
public sealed record Get{Entity}ByIdQuery(Guid Id) : IQuery<{Entity}Response>;

// ═══════════════════════════════════════════════════════════════
// VALIDATOR
// ═══════════════════════════════════════════════════════════════
internal sealed class Get{Entity}ByIdQueryValidator : AbstractValidator<Get{Entity}ByIdQuery>
{
    public Get{Entity}ByIdQueryValidator()
    {
        RuleFor(x => x.Id)
            .NotEmpty()
            .WithMessage("{Entity} ID is required");
    }
}

// ═══════════════════════════════════════════════════════════════
// HANDLER
// ═══════════════════════════════════════════════════════════════
internal sealed class Get{Entity}ByIdQueryHandler 
    : IQueryHandler<Get{Entity}ByIdQuery, {Entity}Response>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public Get{Entity}ByIdQueryHandler(ISqlConnectionFactory sqlConnectionFactory)
    {
        _sqlConnectionFactory = sqlConnectionFactory;
    }

    public async Task<Result<{Entity}Response>> Handle(
        Get{Entity}ByIdQuery request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        const string sql = """
            SELECT 
                e.id AS Id,
                e.name AS Name,
                e.description AS Description,
                e.created_at AS CreatedAt,
                e.updated_at AS UpdatedAt
            FROM {table_name} e
            WHERE e.id = @Id
            """;

        var {entity} = await connection.QueryFirstOrDefaultAsync<{Entity}Response>(
            sql,
            new { request.Id });

        if ({entity} is null)
        {
            return Result.Failure<{Entity}Response>({Entity}Errors.NotFound);
        }

        return {entity};
    }
}

Response DTO

// src/{name}.application/{Feature}/Get{Entity}ById/{Entity}Response.cs
namespace {name}.application.{feature}.Get{Entity}ById;

public sealed class {Entity}Response
{
    public Guid Id { get; init; }
    public required string Name { get; init; }
    public string? Description { get; init; }
    public DateTime CreatedAt { get; init; }
    public DateTime UpdatedAt { get; init; }
}

Template: Get All Query

// src/{name}.application/{Feature}/GetAll{Entities}/GetAll{Entities}Query.cs
using System.Data;
using Dapper;
using {name}.application.abstractions.data;
using {name}.application.abstractions.messaging;
using {name}.domain.abstractions;

namespace {name}.application.{feature}.GetAll{Entities};

public sealed record GetAll{Entities}Query : IQuery<IReadOnlyList<{Entity}ListResponse>>;

internal sealed class GetAll{Entities}QueryHandler 
    : IQueryHandler<GetAll{Entities}Query, IReadOnlyList<{Entity}ListResponse>>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public GetAll{Entities}QueryHandler(ISqlConnectionFactory sqlConnectionFactory)
    {
        _sqlConnectionFactory = sqlConnectionFactory;
    }

    public async Task<Result<IReadOnlyList<{Entity}ListResponse>>> Handle(
        GetAll{Entities}Query request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        const string sql = """
            SELECT 
                e.id AS Id,
                e.name AS Name,
                e.description AS Description
            FROM {table_name} e
            ORDER BY e.name ASC
            """;

        var {entities} = await connection.QueryAsync<{Entity}ListResponse>(sql);

        return {entities}.ToList();
    }
}

Template: Get By Parent ID Query

// src/{name}.application/{Feature}/Get{Entities}ByOrganizationId/Get{Entities}ByOrganizationIdQuery.cs
using System.Data;
using Dapper;
using FluentValidation;
using {name}.application.abstractions.data;
using {name}.application.abstractions.messaging;
using {name}.domain.abstractions;

namespace {name}.application.{feature}.Get{Entities}ByOrganizationId;

public sealed record Get{Entities}ByOrganizationIdQuery(
    Guid OrganizationId) : IQuery<IReadOnlyList<{Entity}Response>>;

internal sealed class Get{Entities}ByOrganizationIdQueryValidator 
    : AbstractValidator<Get{Entities}ByOrganizationIdQuery>
{
    public Get{Entities}ByOrganizationIdQueryValidator()
    {
        RuleFor(x => x.OrganizationId).NotEmpty();
    }
}

internal sealed class Get{Entities}ByOrganizationIdQueryHandler 
    : IQueryHandler<Get{Entities}ByOrganizationIdQuery, IReadOnlyList<{Entity}Response>>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public Get{Entities}ByOrganizationIdQueryHandler(ISqlConnectionFactory sqlConnectionFactory)
    {
        _sqlConnectionFactory = sqlConnectionFactory;
    }

    public async Task<Result<IReadOnlyList<{Entity}Response>>> Handle(
        Get{Entities}ByOrganizationIdQuery request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        const string sql = """
            SELECT 
                e.id AS Id,
                e.name AS Name,
                e.description AS Description,
                e.organization_id AS OrganizationId,
                o.name AS OrganizationName
            FROM {table_name} e
            INNER JOIN organization o ON e.organization_id = o.id
            WHERE e.organization_id = @OrganizationId
            ORDER BY e.name ASC
            """;

        var {entities} = await connection.QueryAsync<{Entity}Response>(
            sql,
            new { request.OrganizationId });

        return {entities}.ToList();
    }
}

Template: Paginated Query

// src/{name}.application/{Feature}/Get{Entities}Paged/Get{Entities}PagedQuery.cs
using System.Data;
using Dapper;
using FluentValidation;
using {name}.application.abstractions.data;
using {name}.application.abstractions.messaging;
using {name}.domain.abstractions;

namespace {name}.application.{feature}.Get{Entities}Paged;

public sealed record Get{Entities}PagedQuery(
    int PageNumber,
    int PageSize,
    s