【C#/Blazor】実務Webアプリ開発編 (32)CQRSのクエリ側をEF Coreで実装する ～画面の形のDTOへ、変更追跡せずに1回で読む～

var rows = await db.Orders
    .AsNoTracking()
    .Where(o => o.StoreId == staff.StoreId)
    .OrderByDescending(o => o.OrderedAt)
    .Select(o => new OrderListItemDto(
        o.Number,
        o.TotalAmount,
        o.Customer!.Name))
    .ToListAsync();

public record OrderListItemDto(
    string Number,
    decimal TotalAmount,
    string CustomerName);

src/
├── MentorApp.Application/
│   └── Contracts/
│       └── Queries/
│           ├── IDashboardStatsQueryService.cs  ← 契約とDTO
│           ├── IMentorshipQueryService.cs  ← 契約とDTO
│           ├── ITopicQueryService.cs  ← 契約とDTO
│           └── IUserQueryService.cs  ← 契約とDTO
└── MentorApp.Infrastructure/
    └── Persistence/
        └── Queries/
            ├── DashboardStatsQueryService.cs  ← 実装
            ├── MentorshipQueryService.cs  ← 実装
            ├── TopicQueryService.cs  ← 実装
            └── UserQueryService.cs  ← 実装

public record TopicDetailDto(
    Guid Id,
    Guid MentorshipId,
    string Title,
    TopicStatus Status,
    DateTimeOffset CreatedAt,
    Guid MentorUserId,
    string MentorDisplayName,
    Guid MenteeUserId,
    string MenteeDisplayName,
    IReadOnlyList<MessageDto> Messages);

public record TopicListItemDto(
    Guid Id,
    Guid MentorshipId,
    string Title,
    TopicStatus Status,
    DateTimeOffset CreatedAt,
    string MentorDisplayName,
    string MenteeDisplayName);

    public async Task<TopicDetailDto?> GetByIdAsync(Guid topicId, CurrentUser currentUser, CancellationToken cancellationToken = default)
    {
        // …（例外処理の殻・2 行省略）
            await using var dbContext = await dbContextFactory.CreateDbContextAsync(cancellationToken);

            return await dbContext.Topics
                .AsNoTracking()
                .Where(t => t.Id == topicId &&
                    (currentUser.Role == Role.Admin ||
                     t.Mentorship!.MentorUserId == currentUser.UserId ||
                     t.Mentorship.MenteeUserId == currentUser.UserId))
                .Select(t => new TopicDetailDto(
                    t.Id,
                    t.MentorshipId,
                    t.Title,
                    t.Status,
                    t.CreatedAt,
                    t.Mentorship!.MentorUserId,
                    t.Mentorship.MentorUser!.DisplayName,
                    t.Mentorship.MenteeUserId,
                    t.Mentorship.MenteeUser!.DisplayName,
                    t.Messages
                        .OrderBy(m => m.SentAt)
                        .Select(m => new MessageDto(
                            m.Id,
                            m.Content,
                            m.SentAt,
                            m.SenderUserId,
                            m.SenderUser!.DisplayName))
                        .ToList()))
                .FirstOrDefaultAsync(cancellationToken);
        // …（例外処理・7 行省略）
    }

SELECT ...
FROM (
    SELECT TOP(1) ...
    FROM [Topics] AS [t]
    INNER JOIN [Mentorships] AS [m] ON [t].[MentorshipId] = [m].[Id]
    INNER JOIN [Users] AS [u] ON [m].[MentorUserId] = [u].[Id]
    INNER JOIN [Users] AS [u0] ON [m].[MenteeUserId] = [u0].[Id]
    WHERE [t].[Id] = @topicId AND ([m].[MentorUserId] = @currentUser_UserId OR [m].[MenteeUserId] = @currentUser_UserId)
) AS [s]
LEFT JOIN (
    SELECT ...
    FROM [Messages] AS [m0]
    INNER JOIN [Users] AS [u1] ON [m0].[SenderUserId] = [u1].[Id]
) AS [s0] ON [s].[Id] = [s0].[TopicId]
ORDER BY [s].[Id], [s].[Id0], [s].[Id1], [s].[Id2], [s0].[SentAt], [s0].[Id]

        try
        {
            await using var dbContext = await dbContextFactory.CreateDbContextAsync(cancellationToken);

            return await dbContext.Topics

            return await dbContext.Topics
                .AsNoTracking()
                .Where(t => t.Id == topicId &&
                    (currentUser.Role == Role.Admin ||

            return await dbContext.Topics
                .AsNoTracking()
                .Where(t => t.Id == topicId &&
                    (currentUser.Role == Role.Admin ||
                     t.Mentorship!.MentorUserId == currentUser.UserId ||
                     t.Mentorship.MenteeUserId == currentUser.UserId))
                .Select(t => new TopicDetailDto(
                    t.Id,
                    t.MentorshipId,

            return await dbContext.Topics
                .AsNoTracking()
                .Where(t => t.Id == topicId &&
                    (currentUser.Role == Role.Admin ||
                     t.Mentorship!.MentorUserId == currentUser.UserId ||
                     t.Mentorship.MenteeUserId == currentUser.UserId))
                .Select(t => new TopicDetailDto(
                    t.Id,

SELECT TOP(1) ...
FROM [Topics] AS [t]
INNER JOIN [Mentorships] AS [m] ON [t].[MentorshipId] = [m].[Id]
INNER JOIN [Users] AS [u] ON [m].[MentorUserId] = [u].[Id]
INNER JOIN [Users] AS [u0] ON [m].[MenteeUserId] = [u0].[Id]
WHERE [t].[Id] = @topicId

                            m.SenderUser!.DisplayName))
                        .ToList()))
                .FirstOrDefaultAsync(cancellationToken);
        }
        catch (Exception ex)

            return await dbContext.Topics
                .AsNoTracking()
                .Where(t => t.Id == topicId &&
                    (currentUser.Role == Role.Admin ||
                     t.Mentorship!.MentorUserId == currentUser.UserId ||
                     t.Mentorship.MenteeUserId == currentUser.UserId))
                .Select(t => new TopicDetailDto(
                    t.Id,

    public async Task<TopicDetailDto?> GetByIdAsync(Guid topicId, CurrentUser currentUser, CancellationToken cancellationToken = default)
    {
        try

    public async Task<TopicDetailDto?> GetByIdAsync(Guid topicId, CurrentUser currentUser, CancellationToken cancellationToken = default)
    {
        try
        {
            // …（クエリ本体・28 行省略）
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "トピックの取得に失敗しました: TopicId={TopicId}, CurrentUserId={CurrentUserId}",
                topicId, currentUser.UserId);
            throw;
        }
    }

    public async Task<Mentorship> CompleteMentorshipAsync(
        Guid mentorshipId,
        CurrentUser currentUser,
        CancellationToken cancellationToken = default)
    {
        try
        {
            await using var uow = await unitOfWorkFactory.CreateAsync(cancellationToken);

            var mentorship = await uow.Mentorships.FindByIdAsync(mentorshipId, cancellationToken)
                ?? throw new KeyNotFoundException($"メンタリング関係が見つかりません: {mentorshipId}");

            var isMentor = currentUser.Role == Role.Mentor && mentorship.MentorUserId == currentUser.UserId;
            if (currentUser.Role != Role.Admin && !isMentor)
                throw new UnauthorizedAccessException("このメンタリングを完了する権限がありません。");

            var now = timeProvider.GetUtcNow();
            mentorship.Complete(now);
            await uow.SaveChangesAsync(cancellationToken);

            logger.LogInformation(
                "メンタリング関係を完了しました: MentorshipId={MentorshipId}, MentorUserId={MentorUserId}, MenteeUserId={MenteeUserId}, CurrentUserId={CurrentUserId}",
                mentorship.Id, mentorship.MentorUserId, mentorship.MenteeUserId, currentUser.UserId);

            return mentorship;
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "メンタリング関係の完了処理に失敗しました: MentorshipId={MentorshipId}, CurrentUserId={CurrentUserId}", mentorshipId, currentUser.UserId);
            throw;
        }
    }