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

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);
        }
    }
}