Ktouth Brand. on Web

け〜くんこと K.Ktouth のだらだらした日常と突発的に作るプログラムや読み物とかの雑多サイト



[2012年02月05日]

ある時点での最新の項目の一覧を取得する Linq

2012年02月06日 21:04更新 筆者:K.Ktouth

昨日黙々と考えていたことー
忘れないようにメモ。

  1. プライマリキーとグループキー、日付と有効フラグを持つテーブルがある。

    CREATE TABLE Sample (
      Id INT PRIMARY KEY UNIQUE NOT NULL,
      GroupId INT NOT NULL,
      Date DATETIME NOT NULL,
      IsVisible BIT NOT NULL DEFAULT 1
    );

  2. 一定の日付を指定すると「その日付以前で各グループの最新のレコード」の一覧を取得する。
    ただし日付はグループごとに重複する可能性が有り、その場合は最新のIdのものを取得する。
    また、最新のレコードの有効フラグが false の場合は除外する。

この条件を満たす SQL をリファレンスに首ったけで必死に構築。

SELECT Sample.Id, Sample.GroupId, Sample.Date, Sample.IsVisible, Sample.Comment
FROM  Sample INNER JOIN
    (SELECT MAX(T_Base2.Id) AS Id
    FROM  Sample AS T_Base2 INNER JOIN
        (SELECT GroupId, MAX(Date) AS Date
        FROM  Sample AS T_Base
        WHERE (Date <= '2012/1/29')
        GROUP BY GroupId) AS T_Date ON T_Base2.GroupId = T_Date.GroupId AND T_Base2.Date = T_Date.Date
    GROUP BY T_Base2.GroupId) AS T_Id ON Sample.Id = T_Id.Id
WHERE (Sample.IsVisible = 1)

これをさらに Linq で使いやすくするために拡張メソッドとして実装……

public interface IJournal
{
  int Id { get; }
  int GroupId { get; }
  DateTime Date { get; }
  bool IsVisible { get; }
}

static class ExLinq
{
  public static IQueryable<T> DateFirst<T>(this IQueryable<T> table, DateTime time)
    where T : IJournal
  {
    var tDates = from _b in table
           where _b.Date <= time
           group _b by _b.GroupId into _gb
           select new { GroupId = _gb.Key, Date = _gb.Max(m => m.Date) };
    var tIds = from _b in table
          join _d in tDates on _b.GroupId equals _d.GroupId
          where _b.Date == _d.Date
          group _b by _b.GroupId into _gb
          select new { Id = _gb.Max(m => m.Id) };
    return from i in table
        join _i in tIds on i.Id equals _i.Id
        where i.IsVisible
        select i;
  }
  public static IQueryable<Sample> DateFirst(this IQueryable<Sample> table, DateTime time)
  {
    var tDates = from _b in table
           where _b.Date <= time
           group _b by _b.GroupId into _gb
           select new { GroupId = _gb.Key, Date = _gb.Max(m => m.Date) };
    var tIds = from _b in table
          join _d in tDates on _b.GroupId equals _d.GroupId
          where _b.Date == _d.Date
          group _b by _b.GroupId into _gb
          select new { Id = _gb.Max(m => m.Id) };
    return from i in table
        join _i in tIds on i.Id equals _i.Id
        where i.IsVisible
        select i;
  }
}

さて、これでうまく行くか……?

(2/6追記) 未検証だったため、色々バグ多発。最後のコードをまるっと修正。

  1. ジェネリック指定が抜けてた orz
  2. グループ要素は各項目の参照は出来ないので、グループキーは #Key プロパティで参照。
  3. テーブル連結のキーは一対しか指定出来ないので、日付の判定は where 節で判別。
  4. ジェネリックを通すためにインターフェイスを定義して使ったが、Entity Framework のモデル型の場合は NotSupportedException 例外が発生する。EFのプリミティブ型を直接指定しないと通らない模様。
    # 捕捉: あくまでもEFの仕様上の問題なので、Linq to SQL ではおそらく駄目だが、Linq to Objects なら上記方法で問題ないかと思われる。

バグをつぶした上で、ジェネリックではなく各モデル型ごとに定義する必要があるとは言え、一応は動いたので良しとします♪
本気で汎用化するなら、既定のコードを流用するのではなく、Queryを直接記述して組み込むくらいのことをする必要があると思われます。
こういった「複数クラスで同様の機能が必要だけど、完全な汎用ではない」場合は T4 とかを使ってテンプレート化する方が楽かな。

本日のリンク元
その他のリンク元
検索