相関サブクエリを使ったデータ取得 - EF Core

EF Coreで相関サブクエリを使ってデータを取得するサンプルを書いてみました。

サブクエリはselect句、from句、where句に記述できますが、今回はwhere句内のサブクエリです。相関ではない単純なサブクエリを試したあと、相関サブクエリでのデータ取得を試しています。

テーブル作成、データの準備

まずはサンプル用のテーブルを作成してデータを投入します。飲食店のお品書きのようなデータです。*1

-- テーブル作成
create table dbo.MenuItem(
    Id int not null,
    Name nvarchar(6) not null,
    Category nvarchar(3) not null,
    Price decimal(3) not null,
    constraint PK_MenuItem primary key(Id)
);

-- データ投入
insert into dbo.MenuItem(Id, Name, Category, Price)
output inserted.*
values
    (1, N'純けい', N'串焼き', 500),
    (2, N'しろ', N'串焼き', 400),
    (3, N'若皮', N'串焼き', 300),
    (4, N'串カツ', N'揚げ物', 400),
    (5, N'ポテトフライ', N'揚げ物', 200),
    (6, N'レンコン揚げ', N'揚げ物', 300);
/*
Id  Name       Category  Price
--- ---------- --------- ------
1   純けい      串焼き     500
2   しろ        串焼き     400
3   若皮        串焼き     300
4   串カツ      揚げ物     400
5   ポテトフライ 揚げ物     200
6   レンコン揚げ 揚げ物     300
*/
エンティティ、DBコンテキスト

上記テーブルをマッピングするエンティティクラスを作成し、DBコンテキストも用意します。

// エンティティ
public class MenuItem {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }

    public override string ToString()
        => $"{nameof(Id)} = {Id}, {nameof(Name)} = {Name}, {nameof(Category)} = {Category}, {nameof(Price)} = {Price}";
}

// DBコンテキスト
public class AppDbContext : DbContext {
    public DbSet<MenuItem> MenuItems { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<MenuItem>().ToTable(nameof(MenuItem));
    }
}

諸々準備ができました。サブクエリを使ったデータ取得を試したいと思います。

サブクエリでデータを取得

まずは相関ではないサブクエリ。(相関サブクエリに対していい呼び方があるといいんですが、なんて言うんでしょう。)

平均価格以上のMenuItemを取得してみます。ちなみに平均価格は350です。

var context = new AppDbContext();
var items = await context.MenuItems
    .Where(item => item.Price >=
        // 平均価格を求めるサブクエリ
        context.MenuItems.Average(item => item.Price))
    .ToListAsync();
foreach (var item in items) {
    Console.WriteLine(item);
}
// 結果
// ※平均価格は350
/*
Id = 1, Name = 純けい, Category = 串焼き, Price = 500
Id = 2, Name = しろ, Category = 串焼き, Price = 400
Id = 4, Name = 串カツ, Category = 揚げ物, Price = 400
*/

Whereメソッドの条件式のうち、DbSetを使ってAverageメソッドを呼び出している部分がサブクエリになります。

EF Coreが実行したSQLを確認してみると想像通りのサブクエリでした。

-- 実行されたSQL
SELECT [m].[Id], [m].[Category], [m].[Name], [m].[Price]
FROM [MenuItem] AS [m]
WHERE [m].[Price] >= (
    SELECT AVG([m0].[Price])
    FROM [MenuItem] AS [m0])
相関サブクエリでデータを取得

続いて相関サブクエリを試してみます。

カテゴリ別の平均価格以上のMenuItemを取得してみましょう。

Whereメソッド内のサブクエリになる部分で、内側のクエリのitem2.Categoryと外側のクエリのitem1.Categoryを比較するようにします。

var context = new AppDbContext();
var items = await context.MenuItems
    .Where(item1 => item1.Price >=
        // カテゴリ別平均価格を求める相関サブクエリ
        context.MenuItems
            .Where(item2 => item2.Category == item1.Category)
            .Average(item => item.Price))
    .ToListAsync();
foreach (var item in items) {
    Console.WriteLine(item);
}
// 結果
// ※串焼きの平均価格は400、揚げ物の平均価格は300
/*
Id = 1, Name = 純けい, Category = 串焼き, Price = 500
Id = 2, Name = しろ, Category = 串焼き, Price = 400
Id = 4, Name = 串カツ, Category = 揚げ物, Price = 400
Id = 6, Name = レンコン揚げ, Category = 揚げ物, Price = 300
*/

ログから実行されたSQLを確認してみると、だいたい想像した通りの相関サブクエリになっていました。

-- 実行されたSQL
SELECT [m].[Id], [m].[Category], [m].[Name], [m].[Price]
FROM [MenuItem] AS [m]
WHERE [m].[Price] >= (
    SELECT AVG([m0].[Price])
    FROM [MenuItem] AS [m0]
    WHERE ([m0].[Category] = [m].[Category]) OR ([m0].[Category] IS NULL AND [m].[Category] IS NULL))

というより実際はこういったSQLをイメージしながらLINQを組み立てた気もします。

以上、EF Coreでは生SQLを書かなくても相関サブクエリを実行してデータ取得できるというサンプルでした。

参考

*1:このデータは架空であり、実在するものとは一切関係ありません。

ModalParitalTagHelperを作ってみた - ASP.NET Core MVC

BootstrapのModalを使っていると、ほとんど同じマークアップで一部だけを変更したいことがあると思います。

あるあるな要件かなと思いますが、

  • サイト内で使うモーダルはだいたい同じHTMLで統一したい(class属性も揃えたい)
  • モーダルのタイトルや本文はそれぞれで設定したい
  • さらにモーダル本文はテキストではなくHTMLを指定したい

といったことを解決するModalParitalTagHelperを作ってみましたというお話です。

ModalではなくCardでもToastでも何でもいいですしBootstrapに限った話でもないですが、とありえずBootstrapのModalで話を進めます。

モーダルの部分ビュー

まずモーダルのHTMLを部分ビュー(_Modal.cshtml)として用意しました。 モーダルのid属性、タイトル、本文はビューモデルのプロパティを使ってレンダリングできるようにしています。

@model ModalPartialViewModel
<div id="@Model.Id" class="modal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                @* モーダルのタイトルはテキストで *@
                <h5 class="modal-title">@Model.Title</h5>
                <button type="button" class="close" data-dismiss="modal">
                    <span>&times;</span>
                </button>
            </div>
            <div class="modal-body">
                @* モーダルの本文はHTMLで *@
                @Model.Body
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary">Save changes</button>
            </div>
        </div>
    </div>
</div>

実際にはボタンなど他にも変更したい部分があるとは思いますが、サンプルなので設定できるのはとりあえずこの3箇所で。

ビューモデル

次は部分ビューのビューモデル。 Titleプロパティは単なる文字列ですが、BodyプロパティはHTMLを設定するのでエンコードされないようにIHtmlContentです。

// _Modal.cshtmlのビューモデル
public class ModalPartialViewModel {
    // id属性
    public string Id { get; set; }
    // モーダルのタイトル
    public string Title { get; set; }
    // モーダルの本文(HTML)
    public IHtmlContent Body { get; set; }
}
PartialTagHelperを使った場合

PartialTagHelperをそのまま使っても部分ビューをレンダリングできます。

@{
    var model = new ModalPartialViewModel {
        Id = "sample-modal",
        Title = "Modal title",
        Body = new HtmlString("<p>Modal body text goes here.</p>"),
    };
    <partial name="_Modal" model="model" />
}

<!-- 生成されるHTML -->
<div id="sample-modal" class="modal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Modal title</h5>
                <button type="button" class="close" data-dismiss="modal">
                    <span>&times;</span>
                </button>
            </div>
            <div class="modal-body">
                <p>Modal body text goes here.</p>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary">Save changes</button>
            </div>
        </div>
    </div>
</div>

ただBodyプロパティにモーダル本文を設定している部分がちょっといまいちかなと思います。 インテリセンスは効きませんし、コンテンツが多いモーダルだと長い文字列になってメンテナンスしにくいでしょう。 HTMLを文字列で指定することはできれば避けたいところです。

ModalPartialTagHelper

次のコードでモーダルの部分ビューをレンダリングできると良さげかなと思います。 モーダル本文はエディタによる補完機能を使いつつHTMLとして埋め込むと。

<modal-partial id="sample-modal" title="Modal title">
    @* モーダルの本文はここに埋め込みたい *@
    <p>Modal body text goes here.</p>
</modal-partial>

ということで前置きが長くなりましたが、今回作ってみたのがこの<modal-partial></modal-partial>のModalPartialTagHelperです。

// モーダルの部分ビュータグヘルパー
public class ModalPartialTagHelper : TagHelper {
    // ビューモデル
    private readonly ModalPartialViewModel _model = new ModalPartialViewModel();
    // 部分ビュータグヘルパー
    private readonly PartialTagHelper _inner;

    public ModalPartialTagHelper(ICompositeViewEngine viewEngine, IViewBufferScope viewBufferScope) {
        _inner = new PartialTagHelper(viewEngine, viewBufferScope) {
            Name = "_Modal",
            Model = _model,
        };
    }

    // PartialTagHelperにViewContextが必要みたい(これがないとNullReferenceException)
    [HtmlAttributeNotBound]
    [ViewContext]
    public ViewContext ViewContext {
        get => _inner.ViewContext;
        set => _inner.ViewContext = value;
    }

    // モーダルのID
    public string Id {
        get => _model.Id;
        set => _model.Id = value;
    }

    // モーダルのタイトル
    public string Title {
        get => _model.Title;
        set => _model.Title = value;
    }

    public override async Task ProcessAsync(TagHelperContext context, TagHelperOutput output) {
        // 子コンテンツをモーダルのボディ用HTMLとする
        _model.Body = await output.GetChildContentAsync();

        await _inner.ProcessAsync(context, output);
    }
}

使わせたくない属性(プロパティ)を公開しないために、PartialTagHelperを継承しないでフィールドで持つことにしました。 ICompositeViewEngine、IViewBufferScope、ViewContextAttributeはまだよく分からないのですがPartialTagHelperのソースを参考にしています。

今回作ったModalPartialTagHelperはHTMLを複数箇所指定できませんが、1箇所だけでもそこそこ使える場面はあるんじゃないかなと思います。

前後にタグを出力するTagHelperを作ってみた - ASP.NET Core MVC

TagHelperを調べているとTagHelperOutputクラスに次のプロパティを見つけました。

このあたりのプロパティを使うと、タグの前後にタグを出力して例えばタグを入れ子にするといったこともできるようです。

ということでサンプルのTagHelperを作ってみました。(上記プロパティを試すのが目的で実用的なTagHelperではありません。)

public class SampleTagHelper : TagHelper {
    public override Task ProcessAsync(TagHelperContext context, TagHelperOutput output) {
        // 自身をpreタグに変換
        output.TagName = "pre";

        // 自身のpreタグにclass属性を設定
        output.Attributes.Add("class", "content");

        // preタグをdivタグで囲む
        output.PreElement.SetHtmlContent($"<div class=\"outer\">");
        output.PostElement.SetHtmlContent($"</div>");

        // コンテンツ(preタグの中)をcodeタグで囲む
        output.PreContent.SetHtmlContent($"<code class=\"inner\">");
        output.PostContent.SetHtmlContent($"</code>");

        return Task.CompletedTask;
    }
}

プロパティ名の"Element"はTagHelper自身のhtml要素のことで、"Content"はTagHelperのhtml要素の中身のことですね。

使ってみるとなんとなく想像した通りのhtmlを出力できました。

@* SampleTagHelperを使ってみる *@
<sample>
    <span>Content</span>
</sample>

@* 生成されるhtml(※わかりやすいように整形してあります) *@
<div class="outer">
    <pre class="content">
        <code class="inner">
            <span>Content</span>
        </code>
    </pre>
</div>
参考

ASP.NET Core MVC - アクション名のAsyncサフィックスが削除されることを確認する

ASP.NET Core の破壊的変更 - .NET Core | Microsoft Docs

上記ドキュメントにある「コントローラアクション名からAsync サフィックスが削除される」ことを確認してみました。

MVC: コントローラー アクション名から Async サフィックスを削除 aspnet/AspNetCore#4849 への対処の一環として、ASP.NET Core MVC では、アクション名から Async サフィックスが既定で削除されます。 ASP.NET Core 3.0 以降、この変更はルーティングとリンク生成の両方に影響します。

ルーティング

次のコントローラのSampleAsyncアクションメソッドは~/default/sampleのURLで呼び出せますが、~/default/sampleasyncでは呼び出せません。

public class DefaultController : Controller {
    public async Task<IActionResult> SampleAsync() {
        await Task.Delay(0);
        return View();
    }
}
URLの生成

タグヘルパーやHTMLヘルパー、URLヘルパーでURLを生成するときは、アクション名からAsyncを取り除く必要があります。

うっかりAsyncを付けてしまいそう。

<a asp-action="Sample" asp-controller="Default">@Url.Action("Sample", "Default")</a>

<!-- 生成されるHTML -->
<a href="/default/sample">/default/sample</a>
ビュー名

ビュー名を省略した場合のViewResultは、Asyncを取り除いたアクション名のファイルを検索します。例えばSampleAsyncアクションではSample.cshtmlを検索します。

もちろんPartialViewResultも同じ動きでした。

public class DefaultController : Controller {
    public async Task<IActionResult> SampleAsync() {
        await Task.Delay(0);
        return View();
    }
    // 検索するcshtmlのパス
    // /Views/Default/Sample.cshtml
    // /Views/Shared/Sample.cshtml

    public async Task<IActionResult> SamplePartialAsync() {
        await Task.Delay(0);
        return PartialView();
    }
    // 検索するcshtmlのパス
    // /Views/Default/SamplePartial.cshtml
    // /Views/Shared/SamplePartial.cshtml
}
おまけ

AsyncアクションメソッドとAsyncを省略した同名のアクションメソッドを同じコントローラに定義できますが、実行してURLにアクセスするとAmbiguousMatchExceptionがスローされます。

注意しましょう。作らないかな。

public class DefaultController : Controller {
    // AmbiguousMatchExceptionがスローされる
    public IActionResult Sample() {
        return View();
    }

    public async Task<IActionResult> SampleAsync() {
        await Task.Delay(0);
        return View();
    }
}

SQL Server - シーケンスを使う

SQL Serverでシーケンスを使うサンプルです。

シーケンスを久しぶりに使おうと思ったらあまり覚えておらず、以前に試したので記事が残ってるかと探してみましたが、見つからなかったのでさらっと残しておきます。

ざっくり言うとシーケンスは連番を生成するオブジェクトです。 テーブルのIDENTITY列がオブジェクトとして独立したものというと伝わるでしょうか。

使い方のポイントは2つ。

  • シーケンスオブジェクトを用意する
    • create sequence
  • シーケンスオブジェクトから連番を取得する
    • next value for関数
    • (複数の値をまとめて取得するなら)sp_sequence_get_rangeストアドプロシージャ

確認していきましょう。

シーケンスオブジェクトの作成する

create sequence文でシーケンスオブジェクトを生成します。

-- シーケンスを作成
-- as {型}
-- start with {値}: next valueを呼び出す取得できる最初の値
create sequence dbo.SQ_ItemId
    as bigint
    start with 1;

start withは連番の開始値(最初に取得できる値)を指定します。この例では1から取得できます。

シーケンスオブジェクトから連番を取得する

next value for関数(これ関数でいいのか?)を使ってシーケンスオブジェクトから連番を取得してみましょう。 以下はselect文でのサンプルですがinsert文でももちろん利用できます。

-- 次の値を取得
select next value for dbo.SQ_ItemId;

連番を範囲で取得するにはsp_sequence_get_rangeストアドプロシージャを利用します。

-- 次の値を範囲で取得
declare @name nvarchar(776) = N'dbo.SQ_ItemId';
declare @size bigint = 5;  -- 取得する数
declare @first sql_variant;  -- 最初の値
declare @increment sql_variant; -- 増分

execute sp_sequence_get_range
    @sequence_name = @name,
    @range_size = @size,
    @range_first_Value = @first output,
    @sequence_increment = @increment output;

-- @firstと@incrementがあれば取得した連番がわかるはず
select @first, @increment;

シーケンスオブジェクトは、insertする前にIDの値を把握できる点が便利だと思います。 その必要がなければIDENTITY列のほうがお手軽でしょう。 他にもメリット・デメリットがあるのでシーンによって使い分けするものかなと思います。

参考