WebSurfer's Home

トップ > Blog 1   |   ログイン
APMLフィルター

各グループ内でレコードに連番を振る方法

by WebSurfer 2023年2月28日 15:48

Linq の GroupBy を使ってグループ分けを行い、グループに属するレコード一覧を取得する際に 1, 2, 3 ... という連番を振る方法を書きます。

ASP.NET Core MVC アプリ

上の画像は ASP.NET Core MVC アプリのもので、Northwind サンプルデータベースの Products テーブルのレコードを Supplier でグループ分けし、各グループに含まれるレコードの ProductName と UnitPrice を取得するとともに ProductId 順に 1, 2, 3 ... という Index (連番) を振って表示したものです。

先の記事「Entity Framework で ROW_NUMBER」で、Linq to Objects で SQL 文の ROW_NUMBER を使った場合と同様な連番を振る方法を紹介しました。それを GroupBy でグループ分けした各グループ内で行うものです。

Linq の GroupBy メソッドを使ってグループ分けすると IEnumerable<IGrouping<TKey, TElement>> オブジェクトが得られます。IGrouping<TKey, TElement> オブジェクトは共通のキー TKey を持つ TElement のコレクションになります。

Linq の Select メソッドを使って IGrouping<TKey, TElement> オブジェクトを反復処理する際に TElement にアクセスして必要な値を取得できますが、それにオーバーロードの一つである Select<TSource,TResult>(IEnumerable<TSource>, Func<TSource,Int32,TResult>) を使うと、同時に 0 番から始まる連番の index を取得することができます。

上の画像の ASP.NET Core MVC アプリのコードを下に載せておきます。使用したコンテキストクラスとエンティティクラスは先の記事「Linq の GroupBy と Aggregate」のものと同じです。上に述べた連番を振る具体例は下のコードの最後の方の Controller / Action Method のコードを見てください。

Model

namespace MvcNet7App.Models
{
    public class ProductDTO
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; } = null!;
        public string Supplier { get; set; } = null!;
        public decimal? UnitPrice { get; set; }
        public int Index { get; set; } // 連番用
    }
}

View

@model Dictionary<string, IEnumerable<ProductDTO>>

@{
    ViewData["Title"] = "GroupBy";
}

@foreach (var item in Model)
{
    <h4>Supplier: @Html.DisplayFor(m => item.Value.First().Supplier)</h4>
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(m => item.Value.First().ProductId)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.Value.First().Index)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.Value.First().ProductName)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.Value.First().UnitPrice)
                </th>
            </tr>
        </thead>
        <tbody>
            @foreach (var product in item.Value)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(m => product.ProductId)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.Index)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.ProductName)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.UnitPrice)
                    </td>
                </tr>
            }
        </tbody>
    </table>
}

Controller / Action Method

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using MvcNet7App.Data;
using MvcNet7App.Models;
using System.ComponentModel.DataAnnotations;

namespace MvcNet7App.Controllers
{
    public class ProductsController : Controller
    {
        private readonly NorthwindContext _context;

        public ProductsController(NorthwindContext context)
        {
            _context = context;
        }

        public async Task<IActionResult> GroupBy()
        {
            // いきなり _context.Products.GroupBy(p => p.SupplierId) と
            // はできないので一旦 List<Product> 型オブジェクトを作り、            
            List<Product> productList = await _context.Products
                                              .Include(p => p.Supplier)
                                              .ToListAsync();

            // Linq to Objects として GroupBy を適用する
            IEnumerable<IGrouping<string, Product>> groups = 
                productList.GroupBy(p => p.Supplier!.CompanyName)
                .OrderBy(g => g.Key);

            // グループごとに連番を取得して IEnumerable<ProductDTO> オブ
            // ジェクトを作り、Dictionary<string, IEnumerable<ProductDTO>>
            // に詰め替えて View にモデルとして渡す
            var dic = new Dictionary<string, IEnumerable<ProductDTO>>();
            foreach (IGrouping<string, Product> group in groups)
            {                
                var groupDTO = group
                       .OrderBy(p => p.ProductId)
                       .Select((p, index) => new ProductDTO
                       {
                           Supplier = p.Supplier!.CompanyName,
                           ProductId = p.ProductId,
                           Index = index + 1,
                           ProductName = p.ProductName,                           
                           UnitPrice = p.UnitPrice                           
                       });

                dic.Add(group.Key, groupDTO);
            }

            return View(dic);
        }
    }
}

Tags: , , ,

ADO.NET

Linq の GroupBy と Aggregate

by WebSurfer 2023年2月24日 16:56

Linq の GroupBy を使ってグループ分けを行い、グループ分けに指定したフィールドや Sum メソッド等で取得できる集計値だけでなく、それ以外のフィールドの値を取得する方法を書きます。

ASP.NET Core MVC アプリ

SQL Server に投げる SQL 文でそのようなデータを取る方法は自分の知る限りなさそうですが、.NET アプリで Linq を使うと何とかなるということで、その方法を備忘録として書いておくことにしました。

上の画像は ASP.NET Core MVC アプリのもので、Northwind サンプルデータベースの Products テーブルのレコードを Supplier と Category でグループ化し、グループに含まれる製品価格の最小値と最大値をそれぞれ MinPrice と MaxPrice として表示すると共に、グループに含まれる製品の名前一覧をカンマ区切りで ProductNames に表示したものです。

ポイントは、グループ化された結果の IGrouping<TKey, TElement> オブジェクトから Select メソッドで ProductName のコレクションを取得し、それらを Enumerable.Aggregate メソッドを使って連結したところです。この記事の最後の方に載せた Controller / Action Method のコードを見てください。

上の画像の ASP.NET Core MVC アプリのコードを下に載せておきます。

コンテキストクラスとエンティティクラスは、リバースエンジニアリングで既存の Northwind の Products, Categories, Supplers テーブルから生成したものです。

エンティティクラスの構造は以下のようになっています。赤茶色と赤はナビゲーションプロパティ、緑は主キーのプロパティ、青は FK 制約付きのフィールドのプロパティです。

エンティティクラス

Model

namespace MvcNet7App.Models
{    public class GroupedProduct
    {
        public string Supplier { get; set; } = null!;
        public string Category { get; set; } = null!;
        public decimal MaxPrice { get; set; }
        public decimal MinPrice { get; set; }
        public string ProductNames { get; set; } = null!;
    }
}

View

@model IEnumerable<GroupedProduct>

@{
    ViewData["Title"] = "GroupBy2";
}

<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Supplier)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Category)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.MinPrice)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.MaxPrice)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.ProductNames)
            </th>
        </tr>
    </thead>
    <tbody>
        @foreach (var product in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(m => product.Supplier)
                </td>
                <td>
                    @Html.DisplayFor(m => product.Category)
                </td>
                <td>
                    @Html.DisplayFor(m => product.MinPrice)
                </td>
                <td>
                    @Html.DisplayFor(m => product.MaxPrice)
                </td>
                <td>
                    @Html.DisplayFor(m => product.ProductNames)
                </td>
            </tr>
        }
    </tbody>
</table>

Controller / Action Method

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using MvcNet7App.Data;
using MvcNet7App.Models;

namespace MvcNet7App.Controllers
{
    public class ProductsController : Controller
    {
        private readonly NorthwindContext _context;

        public ProductsController(NorthwindContext context)
        {
            _context = context;
        }

        public async Task<IActionResult> GroupBy2()
        {
            // 下のコードで使った Aggregate メソッドは SQL 文に
            // 変換できず Linq to Entities では使えないので、こ
            // こで List<Product> オブジェクトを取得し、
            var products = await _context.Products
                           .Include(p => p.Category)
                           .Include(p => p.Supplier)
                           .ToListAsync();

            // 以下で Linq to Objects として処理する
            var grouped = products
                .GroupBy(p => new
                {
                    p.Supplier!.CompanyName,
                    p.Category!.CategoryName
                })
                .Select(g => new GroupedProduct
                {
                    Supplier = g.Key.CompanyName,
                    Category = g.Key.CategoryName,
                    MaxPrice = g.Max(p => p.UnitPrice)!.Value,
                    MinPrice = g.Min(p => p.UnitPrice)!.Value,
                    ProductNames = g.Select(p => p.ProductName)
                                    .Distinct()
                                    .Aggregate((a, b) => $"{a}, {b}")
                })
                .OrderBy(gp => gp.Supplier);

            return View(grouped);
        }
    }
}

Tags: , ,

ADO.NET

PlanetScale を使ってみました

by WebSurfer 2023年2月15日 19:11

ASP.NET Core MVC アプリで PlanetScale をデーターベースに使ってみました。紆余曲折がありましたが何とか接続して使えるようになった結果が下の画像です。

PlanetScale を使ったアプリ

その紆余曲折と言うか、アプリの作成で自分が遭遇した問題は以下の通りです。

  1. EF Code First でテーブルを作成できない。
  2. 既存の DB からリバースエンジニアリングができない。
  3. BeginTransaction メソッドが使えない。

その顛末を以下に備忘録として書いておきます。上の各項目についても説明します。

PlanetScale とは何かですが、"PlanetScale is the world’s most advanced serverless MySQL platform" ということだそうで、要するに MySQL を使ったクラウド上のデータベースサーバーらしいです。制約として外部キー制約がつけられないということがあるそうです。

利用するにあたって、まず、PlanetScale にサインインしてデータベース名と地域を設定し、データベースを作成する必要があります。以下の画像がその設定の例です。

データベース名と地域を設定

データベースの作成に成功すると、以下のように自分が作成したデーターベース情報が表示されます。

自分の DB 情報

上の画像の[Connect]ボタンをクリックすると、下の画像のように接続情報が表示されます。Username と Password が表示されるのは初回のみなので必ず記録しておいてください

接続情報

さらに、上の画像の赤枠の[.NET]を選択すると ASP.NET Core アプリの appsettings.json に設定するための接続文字列の例が表示されますので、これも忘れないように記録しておくことをお勧めします。

以上はデータベース作成時からデフォルトで存在している main ブランチでの話です。このあと開発作業用にブランチを追加するのが普通ということなので自分もそうしてみました。気をつけなければならないのは追加した開発作業用ブランチへの接続用の Username と Password は main ブランチのものとは異なることです。

自分はそれを知らなくて半日ぐらいハマりました。開発作業用ブランチを作ってそれにテーブルを作成し、アプリからアクセスして SELECT クエリを投げたのですが、MySqlException がスローされてそのテーブルは見つからないと言われます。原因は Username と Password に main ブランチのものを使っていたので main ブランチに接続され、main ブランチにはそのテーブルは存在しないからでした。開発作業用ブランチに接続するには、開発作業用ブランチの Username と Password を使用する必要があるようです。

以上で PlanetScale 側のデータベースの準備は完了したはずなので、Visual Studio 2022 のテンプレートを使って .NET 6.0 の ASP.NET Core MVC のプロジェクトを新規に作成し、それから PlanetScale を使ってみます。

(1) EF Code First でテーブルを作成できない

最初に EF Code First の機能を使って PlanetScale にアプリが使うテーブルを作成してみます。先の記事「MySQL で Movie チュートリアル (CORE)」に書いた手順で、EF Code First の機能を利用して PlanetScale に Movie テーブルの作成をトライしてみました。

Add-Migration では問題なく Migration のためのクラスファイルが作成されます。しかし、Update-Database でのテーブルの作成に失敗します。

プロバイダに MySql.EntityFrameworkCore 6.0.10 を使った場合は、Update-Database の実行で KeyNotFoundException がスローされて失敗します。

Pomelo.EntityFrameworkCore.MySql 6.0.2 を使った場合は、ALTER DATABASE CHARACTER SET utf8mb4 というコマンドで失敗し "alter database is not supported" というエラーメッセージが出て、テーブルの作成に失敗します。

.NET 7.0 でも試してみましたが同じエラーで失敗します。

(2) リバースエンジニアリングができない

解決方法が分からないので、EF Code First による Movie テーブルの作成は諦めて、PlanetScale のサイトにアクセスして Console から手動で Movie テーブルを作成しました。下の画像の movie というのがそれです。

Console から手動で Movie テーブル作成

(注: __EFMigrationsHistory というテーブルもありますが、それは上に書いた EF Code First の操作でできたものです。その時は肝心の Movie テーブルは作成されていません)

先に作ったプロジェクトは放置して新たにゼロから ASP.NET Core MVC のプロジェクトを作成し、プロバイダには Pomelo.EntityFrameworkCore.MySql 6.0.2 を使って、リバースエンジニアリングでコンテキストクラスとエンティティクラスを作成をトライしました。

エラーなしで完了するもののコンテキストクラスしか生成されずその内容も不完全です。エンティティクラスは全く作成されません。

ちなみに、プロバイダに MySql.EntityFrameworkCore 6.0.10 を使った場合は KeyNotFoundException がスローされて何も生成されずに終わってしまいます。

これでは先に進めないので、不完全なコンテキストクラスは手直しして、エンティティクラスは自分でコードを書いて追加しました。その内容は以下の通りです。

コンテキストクラス

using Microsoft.EntityFrameworkCore;
using PlanetScaleMovie2.Models;

namespace PlanetScaleMovie2.Data
{
    public partial class myplanetscaleContext : DbContext
    {
        public myplanetscaleContext()
        {
        }

        public myplanetscaleContext(DbContextOptions<myplanetscaleContext> options)
            : base(options)
        {
        }

        // 以下の一行だけは自動生成されないので手動で追加
        public virtual DbSet<Movie> Movies { get; set; } = null!;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // 自動生成されたコードは不要なので削除
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.UseCollation("utf8mb4_0900_ai_ci")
                .HasCharSet("utf8mb4");

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

エンティティクラス

using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace PlanetScaleMovie2.Models
{
    [Table("movie")]
    public partial class Movie
    {
        [Key]
        public int Id { get; set; }
        [Column(TypeName = "text")]
        public string? Title { get; set; }
        [Column(TypeName = "datetime")]
        public DateTime ReleaseDate { get; set; }
        [Column(TypeName = "text")]
        public string? Genre { get; set; }
        public decimal Price { get; set; }
    }
}

そのあとスキャフォールディング機能を使って Controller と View を作成します。

Controller と View の作成

Program.cs でコンテキストクラスを DI コンテナに登録します。

var serverVersion = new MySqlServerVersion(new Version(8, 0, 32));
var coonecctionString = builder.Configuration.GetConnectionString("PlanetScaleMovieContext");
builder.Services.AddDbContext<myplanetscaleContext>(options =>
    options.UseMySql(coonecctionString, serverVersion));

appsettings.json に上のコードの "PlanetScaleMovieContext" という名前で接続文字列を追加します。Movie テーブル を開発作業用ブランチに作った場合は、接続文字列の user と password は開発作業用ブランチ用のもの���することに注意してください。

以上でアプリは今度は問題なく動きました。その結果がこの記事の一番上の画像です。

(3) BeginTransaction メソッドが使えない

これはプロバイダに Oracle 製の MySql.EntityFrameworkCore を使った場合です。以下のように BeginTransaction メソッドの行で KeyNotFoundException がスローされます。

BeginTransaction メソッド

プロバイダに Pomelo.EntityFrameworkCore.MySql を使った場合は問題ありません。Oracle 製はどうも相性が良くないようです。

なお、上記 (1), (2) の件については planetscale / discussionDoes PlanetScale support the .NET 6/7 EF Code First and reverse engineering? という質問を出しました。解決に向けて進展がありましたらこの記事に追記します。

Tags: , , ,

CORE

About this blog

2010年5月にこのブログを立ち上げました。主に ASP.NET Web アプリ関係の記事です。

Calendar

<<  2024年3月  >>
252627282912
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar