Zenn
👌

Dapper マルチマッピングで多対多の関係先を生成する

2024/05/06に公開
C#
MySQL
Blazor
#
dappar
tech

はじめに

  • Dapperで、多対多のテーブルからマルチマッピングを使って関係先を含む情報を取得します。
  • 表示には、.NET 8のBlazor Web App + MudBlazorを使います。
  • 作法や効率、例外処理などは無視して、単に「こういう使い方ができる」という実証実験です。

Dapper とは

  • Dapperは、.NET向けのシンプルなORMです。

https://github.com/DapperLib/Dapper

環境

  • Windows 11
    • VisualStudio 2022 17.9.6
    • .NET 8.0.4
    • Dapper 2.1.35
    • MySqlConnector 2.3.7
    • MudBlazor 6.19.1

プロジェクトの構成

  • VisualStudioで新規「Blazor Web App」プロジェクトを以下の想定で作ります。
    • フレームワークは.NET 8.0にします。
    • 認証の種類は「なし」にします。
    • HTTPS用の構成にします。
    • Interactive render modeはAuto(Server and WebAssembly)またはServerにします。
    • Interactivity locationはPer page/componentにします。
    • ソリューションをプロジェクトと同じディレクトリに配置します。

NuGet パッケージの導入

  • パッケージマネージャコンソール、または、管理タブで、NuGetパッケージDapperを導入します。
  • 適合するDBコネクタを導入します。
    • このプロジェクトでは、MySqlConnectorを導入しています。
  • さらに、このプロジェクトではMudBlazorを導入しています。

起動の構成

Program.cs
using <project>.Components;
using MySqlConnector;
using System.Data;
using MudBlazor;
using MudBlazor.Services;

var builder = WebApplication.CreateBuilder (args);
var connectionString = $"database=<project>;{builder.Configuration.GetConnectionString ("Host")}{builder.Configuration.GetConnectionString ("Account")}";

// Add services to the container.
builder.Services.AddRazorComponents ()
    .AddInteractiveServerComponents ();
// MySql Connection
builder.Services.AddTransient<IDbConnection> (db => new MySqlConnection (connectionString));
// MudBlazor
builder.Services.AddMudServices (config => {
    config.SnackbarConfiguration.PositionClass = Defaults.Classes.Position.BottomLeft;
    config.SnackbarConfiguration.PreventDuplicates = false;
    config.SnackbarConfiguration.NewestOnTop = false;
    config.SnackbarConfiguration.ShowCloseIcon = true;
    config.SnackbarConfiguration.VisibleStateDuration = 10000;
    config.SnackbarConfiguration.HideTransitionDuration = 500;
    config.SnackbarConfiguration.ShowTransitionDuration = 500;
    config.SnackbarConfiguration.SnackbarVariant = Variant.Filled;
});

var app = builder.Build ();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment ()) {
    app.UseWebAssemblyDebugging ();
} else {
    app.UseExceptionHandler ("/Error", createScopeForErrors: true);
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts ();
}

app.UseHttpsRedirection ();

app.UseStaticFiles ();
app.UseAntiforgery ();

app.MapRazorComponents<App> ()
    .AddInteractiveServerRenderMode ();

System.Diagnostics.Debug.WriteLine ("Initialized");
app.Run ();

対象データ

> show columns from Books;
+-------------+----------------+------+-----+---------+----------------+
| Field       | Type           | Null | Key | Default | Extra          |
+-------------+----------------+------+-----+---------+----------------+
| Id          | int            | NO   | PRI | NULL    | auto_increment |
| Title       | longtext       | YES  |     | NULL    |                |
| Description | longtext       | YES  |     | NULL    |                |
| PublishDate | datetime(6)    | YES  |     | NULL    |                |
| Publisher   | longtext       | YES  |     | NULL    |                |
| Series      | longtext       | YES  |     | NULL    |                |
| Price       | decimal(65,30) | NO   |     | NULL    |                |
+-------------+----------------+------+-----+---------+----------------+

> show columns from Authors;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| Id             | int          | NO   | PRI | NULL    | auto_increment |
| Name           | varchar(255) | YES  | MUL | NULL    |                |
| AdditionalName | varchar(255) | YES  |     | NULL    |                |
| Description    | longtext     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

> show columns from AuthorBook;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| AuthorsId | int  | NO   | PRI | NULL    |       |
| BooksId   | int  | NO   | PRI | NULL    |       |
+-----------+------+------+-----+---------+-------+

多対多の関係テーブルからマルチマッピングで取り出す

関係先オブジェクトを生成する

Home.razor
@using System.Data
@using Dapper

@page "/"
@inject IDbConnection connection

<PageTitle>Home</PageTitle>

<MudText Typo="Typo.h3">Hello, World!</MudText>
<MudAlert>Welcome to your new app.</MudAlert>

@if (books == null) {
    <MudProgressCircular Indeterminate="true" />
} else {
    <MudSimpleTable class="my-2" Elevation="3" Dense="true" Hover="true" Striped="true">
        <thead>
            <tr><th>Id</th><th>Title</th><th>Authors(Book Ids)</th></tr>
        </thead>
        <tbody>
            @foreach (var book in books) {
                @if (book != null) {
                    <tr><td>@book.Id</td><td>@book.Title</td><td>@(string.Join (",", book.Authors.ConvertAll (a => $"{a.Name}({string.Join (",", a.Books.ConvertAll (b => b.Id))})")))</td></tr>
                }
            }
        </tbody>
    </MudSimpleTable>
}

@code {
    /// <summary>書籍</summary>
    protected class Book {
        public int Id;
        public string Title = "";
        //public string Authors = "";
        public List<Author> Authors = new List<Author> ();
    }

    /// <summary>著者</summary>
    protected class Author {
        public int Id;
        public string Name = "";
        public List<Book> Books = new List<Book> ();
    }

    /// <summary>書籍一覧</summary>
    protected List<Book>? books = null;

    /// <summary>初期化</summary>
    protected override async Task OnInitializedAsync () {
        // マルチマッピングを利用して書籍一覧と著者情報を同時に取得
        books ??= (await connection.QueryAsync<Book, Author, Book, Book> ( // 3個のマップ対象クラスと1個の返却クラス
            @"-- 書籍
            select Books.Id, Books.Title, Authors.Id, Authors.Name, aBooks.Id, aBooks.Title
            from Books
            -- 著者
            left join AuthorBook on Books.Id = AuthorBook.BooksId
            left join Authors on Authors.Id = AuthorBook.AuthorsId
            -- 著書
            left join AuthorBook as aAuthorBook on Authors.Id = aAuthorBook.AuthorsId
            left join Books as aBooks on aBooks.Id = aAuthorBook.BooksId
            -- 発行日降順
            order by Books.PublishDate DESC;",
            // マルチマッピング
            (book, author, abook) => {
                // 行毎に、引数で得た3個のインスタンスを使って著者を生成
                book.Authors.Add (new Author { Id = author.Id, Name = author.Name, Books = new List<Book> {abook} });
                // 生成した著者を持たせた書籍を返す
                return book;
            },
            splitOn: "Id" // カラムIdで区切って各モデルにクラスするマップする
        )).GroupBy (b => b.Id).Select (g => {
            // 書籍Idが同じ書籍の行に対して
            var book = g.First (); // 代表書籍
            // 著者一覧を生成
            book.Authors = g.GroupBy (b => b.Authors.Single ().Id).Select (g => {
                // 著者Idが同じ書籍の行に対して
                var author = g.First ().Authors.Single (); // 代表書籍の代表著者
                // 著書一覧を生成
                author.Books = g.Select (b => b.Authors.Single ().Books.Single ()).ToList (); // 著書を一覧
                return author; // 代表著者(生成した著書一覧を持つ)
            }).ToList ();
            return book; // 代表書籍(生成した著者一覧を持つ)
        }).ToList();
        // 著者情報中の書籍オブジェクトを書籍一覧のものに差し替える
        foreach (var book in books) {
            foreach (var author in book.Authors) {
                author.Books = author.Books.ConvertAll(b => books.Find (ab => ab.Id == b.Id) ?? b);
            }
        }
    }
}
  • left outer joinで書籍、著者、著書を一覧します。
    • マルチマッピングを使って、それぞれBook,Author,Book型にマップします。
    • ジャンクションテーブルはオブジェクト化しません。
  • 書籍一覧を生成します。
    • 書籍毎に著者一覧を生成します。
      • 著者毎に著書一覧を生成します。
        • 同じ著書でも著者毎に、さらには、書籍毎に別オブジェクトが生成されます。
        • 最終的に、著書一覧のオブジェクトは、書籍一覧のオブジェクトに置き換えられます。
      • 同じ著者でも書籍毎に別オブジェクトが生成されます。
  • 書籍Id、書名、著者(著書Id,…)の形で一覧表にします。

https://www.learndapper.com/relationships#dapper-many-to-many-relationships

関係先リストをIdのままにして、個別の一覧を生成する

Home.razor
@using System.Data
@using Dapper

@page "/"
@inject IDbConnection connection

<PageTitle>Home</PageTitle>

<MudText Typo="Typo.h3">Hello World!</MudText>
<MudAlert>Welcome to your new app.</MudAlert>

@if (books == null || authors == null) {
    <MudProgressCircular Indeterminate="true" />
} else {
    <MudSimpleTable class="my-2" Elevation="3" Dense="true" Hover="true" Striped="true">
        <thead>
            <tr><th>Id</th><th>Title</th><th>Authors(Book Ids)</th></tr>
        </thead>
        <tbody>
            @foreach (var book in books) {
                @if (book != null) {
                    <tr><td>@book.Id</td><td>@book.Title</td><td>@(string.Join (",", book.Authors (authors).ConvertAll (a => $"{a.Name}({string.Join (",", a.Books (books).ConvertAll (b => b.Id))})")))</td></tr>
                }
            }
        </tbody>
    </MudSimpleTable>
}

@code {
    /// <summary>書籍</summary>
    protected class Book {
        public int Id;
        public string Title = "";
        public List<int> _relatedIds = new List<int>();
        public List<Author> Authors (List<Author>? authors) => _relatedIds.ConvertAll (id => authors?.Find (item => item.Id == id) ?? new Author ());
    }

    /// <summary>著者</summary>
    protected class Author {
        public int Id;
        public string Name = "";
        public List<int> _relatedIds = new List<int> ();
        public List<Book> Books (List<Book>? books) => _relatedIds.ConvertAll (id => books?.Find (item => item.Id == id) ?? new Book ());
    }

    /// <summary>書籍一覧</summary>
    protected List<Book>? books = null;

    /// <summary>著者一覧</summary>
    protected List<Author>? authors = null;

    /// <summary>初期化</summary>
    protected override async Task OnInitializedAsync () {
        books ??= (await connection.QueryAsync<Book, int, Book> (
            @"select Books.Id, Books.Title, AuthorBook.AuthorsId
                from Books
                left join AuthorBook on Books.Id = AuthorBook.BooksId
                order by Books.PublishDate DESC;",
            (book, authorsId) => {
                book._relatedIds.Add (authorsId);
                return book;
            },
            splitOn: "AuthorsId"
        )).GroupBy (b => b.Id).Select (g => {
            var book = g.First ();
            book._relatedIds = g.Select (g => g._relatedIds.Single ()).ToList ();
            return book;
        }).ToList ();
        authors ??= (await connection.QueryAsync<Author, int, Author> (
            @"select Authors.Id, Authors.Name, AuthorBook.BooksId
                from Authors
                left join AuthorBook on Authors.Id = AuthorBook.AuthorsId
                order by Authors.Name ASC;",
            (author, booksId) => {
                author._relatedIds.Add (booksId);
                return author;
            },
            splitOn: "BooksId"
        )).GroupBy (a => a.Id).Select (g => {
            var author = g.First ();
            author._relatedIds = g.Select (g => g._relatedIds.Single ()).ToList ();
            return author;
        }).ToList ();
    }
}
  • left outer joinで書籍、著者Idを一覧します。
    • マルチマッピングを使って、それぞれBook,int型にマップします。
      • 書籍毎に著者Id一覧を生成します。
    • ジャンクションテーブルはオブジェクト化しません。
  • 書籍一覧を生成します。
    • 書籍毎に著者Id一覧を生成します。
  • 同様に著書Id一覧を含む著者一覧を生成します。
  • 書籍Id、書名、著者(著書Id,…)の形で一覧表にします。

トランザクションにする

Home.razor
/// <summary>初期化</summary>
protected override async Task OnInitializedAsync () {
    connection.Open ();
    using (var transaction = connection.BeginTransaction()) {
        books ??= (await connection.QueryAsync<Book, int, Book>(
                @"select Books.Id, Books.Title, AuthorBook.AuthorsId
            from Books
            left join AuthorBook on Books.Id = AuthorBook.BooksId
            order by Books.PublishDate DESC;",
            (book, authorsId) =>
            {
                book._relatedIds.Add(authorsId);
                return book;
            },
            splitOn: "AuthorsId",
            transaction: transaction
        )).GroupBy(b => b.Id).Select(g =>
        {
            var book = g.First();
            book._relatedIds = g.Select(g => g._relatedIds.Single()).ToList();
            return book;
        }).ToList();
        authors ??= (await connection.QueryAsync<Author, int, Author>(
                @"select Authors.Id, Authors.Name, AuthorBook.BooksId
            from Authors
            left join AuthorBook on Authors.Id = AuthorBook.AuthorsId
            order by Authors.Name ASC;",
            (author, booksId) =>
            {
                author._relatedIds.Add(booksId);
                return author;
            },
            splitOn: "BooksId",
            transaction: transaction
        )).GroupBy(a => a.Id).Select(g =>
        {
            var author = g.First();
            author._relatedIds = g.Select(g => g._relatedIds.Single()).ToList();
            return author;
        }).ToList();
        transaction.Commit();
    }
    connection.Close ();
}
  • トランザクションにすることで、二つのクエリの間に改変が生じることを防ぐことができます。
  • トランザクションを開始する前に、明示的にコネクションを開く必要があります。
    • 開いていないと、Connection is not open.という例外が生じます。
    • トランザクションでないクエリの際には、開いていないと勝手に開いて、クエリの実行後に勝手に閉じるようです。
    • この場合、明示的に閉じなくても、Disposeされる際には閉じられるように思います。
      • しかし、注入されているコネクションのライフサイクルに依存するので、明示的に閉じた方が安全ですね。
  • クエリにtransaction: transactionを渡す必要があります。
    • 渡さないと、The transaction associated with this command is not the connection's active transaction.という例外が生じます。

結論

  • 無理に関係先までオブジェクト化するよりも、関係先はIdのままにして、独立した書籍一覧と著者一覧を生成した方が使いやすそうに思いました。

おわりに

  • 最後までお読みいただきありがとうございました。
  • お気づきの点があればコメントいただけると助かります。

Discussion