SQL Server のテーブルからレコードを抽出する際、あるフィールドに ORDER BY 句を適用して並べ替え、その順序で連番を振りたいという場合は ROW_NUMBER (Transact-SQL) を使うことができます。

上の画像がその例で、ProductName フィールドに ORDER BY 句を適用して昇順に並べ替えて、ROW_NUMBER を使ってその順で 1 から連番を振って、その連番を SeqNum という名前で取得しています。期待通り SeqNum に連番が取得されているのが分かるでしょうか？

それを同じことを Entity Framework ではどのようしたらできるかというのがこの記事の話です。

この記事を書いた時点で自分が調べた限りですが、ROW_NUMBER は Entity Framework ではサポートされてないようで（NuGet から Thinktecture.EntityFrameworkCore.SqlServer をインストールして使えるようにするという手はあるようですが）、Linq to Entities / Objects のクエリ式やメソッド式に ROW_NUMBER を含めることはできないようです。

Microsoft のドキュメント「生 SQL クエリ」に紹介されているように、FromSqlRaw 拡張メソッドを使用して上の画像の SELECT クエリをそのまま SQL Server に投げるという手も考えましたが、戻ってきた結果から SeqNum を取得する方法が見つかりません。（自分が見つけられないだけで手はあるのかもしれませんが）

ではどうするかですが、ググって調べていると Generating Sequence Numbers In LINQ Query という記事を見つけました。

IEnumerable<T> インターフェイスの Select 拡張メソッドのオーバーロードの一つに Select<TSource,TResult>(IEnumerable<TSource>, Func<TSource,Int32,TResult>) があって、それを使えば 0 番から始まる連番の index を取得することができるというものです。

その記事を参考に、上の画像の ROW_NUMBER を使った SELECT クエリと同じ結果を Linq to Entities / Objects を使って取得するコードを書いてみました。以下の通りです。

public List<ProductWithSeqNum> GetListWithSeqNum()
{
    var data = _context.Products
               .Select(a => new 
               {
                   ProductId = a.ProductId,
                   ProductName = a.ProductName,
                   UnitPrice = a.UnitPrice.Value
               });

    var list = data.AsEnumerable()
               .OrderBy(a => a.ProductName)
               .Select((a, index) => new ProductWithSeqNum
               {
                   SeqNum = index + 1,
                   ProductId = a.ProductId,
                   ProductName = a.ProductName,
                   UnitPrice = a.UnitPrice
               });

    return list.ToList();
}

まず、Linq to Entities を使って Products テーブルから ProductId, ProductName, UnitPrice 抽出して IQueryable<匿名クラス> のオブジェクトを取得し、変数 data に格納しています。

次に、data.AsEnumerable() で IEnumerable<匿名クラス> に変換して、それを OrderBy メソッドで ProductName 順に並べ替え、上に述べた Select メソッドの index を使って 1 から始まる連番を SeqNum に格納しています。

ProductWithSeqNum クラスを Data Transfer Object (DTO) として使っていて、結果を List<ProductWithSeqNum> オブジェクトとして戻しています。ProductWithSeqNum クラスの定義は以下の通りです。

public class ProductWithSeqNum
{
    public int SeqNum { get; set; }

    public int ProductId { get; set; }

    public string ProductName { get; set; }

    public decimal UnitPrice { get; set; }
}

上の GetListWithSeqNum メソッドで取得した結果をコンソールに書き出すと以下の通りとなります。一番上の画像と同様な結果が取得できているのが分かるでしょうか。

Linq to Entities でエンティティを追加したり取得したりする場合、デフォルトではエンティティは DbContext にキャッシュされるそうです。それでハマったので、再びそういうことがないよう備忘録を書いておくことにしました。

元の話は Teratail のスレッド「[C#,.NET5,EFCore5+Microsfot.Data.Sqlite] トランザクションのロールバックが意図通りに動かない」です。その話は表題とは異なり、ロールバックは期待通り動いていたがキャッシュのために動いてないと勘違いしたというものです。

本題に入る前に、まずトランザクション / ロールバックの話を書いておきます。

トランザクションは、保留中の状態 (BeginTransaction の呼び出し後、Commit の呼び出し前) だけからロールバックできるようになっています。逆に言えば、保留中の状態であれば RollBack を呼び出せばロールバックできます。

Microsoft のドキュメント「トランザクションの使用」の「トランザクションを制御する」のセクションのサンプルコードを見てください。

SaveChanges はすべて完全に成功していても、transaction は SaveChanges でコミットされるわけではない（保留中の状態にある）ので、transaction.Commit(); がないと transaction が Dispose される時にロールバックされます。

そのコードで transaction.Commit(); ⇒ transaction.RollBack(); としたのが Teratail のスレッドの話ですが、その場合はもちろん無条件でロールバックされます。

DB はロールバックはされたのですが、キャッシュされたエンティティまではロールバックされないので、キャッシュされたエンティティを使ってその後の操作を行った結果ロールバックが失敗しているように見えたという話です。

エンティティをキャッシュする理由は、Microsoft のドキュメント「追跡と追跡なしのクエリ」に書いてあるように、追跡を行うためということです。どういうことかと言うと、エンティティに加えられた変更を追跡していって、SaveChanges メソッドで変更結果を DB に反映���るということらしいです。

その記事に書いてある "If EF Core finds an existing entity, then the same instance is returned. EF Core won't overwrite current and original values of the entity's properties in the entry with the database values." というのは「エンティティがキャッシュにあればキャッシュから取得する。DB の値で上書きされることはない」と言っているように思えます。

"If the entity isn't found in the context, then EF Core will create a new entity instance and attach it to the context." というのは「context.Blogs.Add(...) というようにするとそのエンティティも DbContext にキャッシュされる」ということのように思えます。

Teratail のスレッドのように、自分で RollBack と書くようなことはしないはずなので、普通はキャッシュによる問題には遭遇しなそうな気がします。

そこを、若干無理やりですが、こんなことをすると問題になるかもしれないと作ったサンプルが下のコードです。Visual Studio 2019 のテンプレートで作った .NET 5.0 のコンソールアプリです。DB は SQLite を使っています。

Teratail のものとは違って、普通に Commit と書いて例外発生時のみロールバックするようにしています。ただし、例外を catch してなかったことにしているので、その後キャッシュされたエンティティを使っての作業が継続できるというものです。

これを実行した結果の DB の内容が上の画像です。Name が SEQ1, SEQ2, SEQ3 の既存のレコードがあって、それに赤枠で囲った SEQ5 のレコードを追加しています。

transaction で囲った 1 つ目の SaveChanges で SEQ5 の Value を 2 に UPDATE していますが、2 つ目の SaveChanges で PK 制約違反の例外が発生するので Commit できずロールバックされるようになっています。DB 上では上の画像の通りロールバックされて SEQ5 の Value は初期値 1 のままになっています。

コードのコメント「ここでエンティティがキャッシュされる」のところで SEQ5 のエンティティがキャッシュされています。ロールバックされた後、変数 seq1, seq2, seq3 に SEQ5 のエンティティを取得してその Value プロパティをコンソールに書き出すと以下のように順に 2, 2, 1 となります。

seq1, seq2 はキャッシュから取得されており、キャッシュはロールバックされないので、それらの Value はコードで書き換えた 2 のままになっています。

予想外だったのは seq2 です。これは要注意だと思いました。context.Sequences.ToListAsync() で DB に SELECT クエリを発行してロールバック後のすべてのレコードを取得してくるのですが、SEQ5 のエンティティのみはキャッシュで書き換えられてしまっています。

seq3 は Reload して DB からデータを取得してキャッシュを書き換えた結果です。上の画像のロールバック後の DB の値が Reload で取得されて Value は 1 になっています。

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace ConsoleAppSQLite
{
    class Program
    {
        static async Task Main(string[] args)
        {
            using (var context = new TestDBContext())
            {
                // ここでエンティティがキャッシュされる
                context.Sequences.Add(
                    new SequenceValue { Name = "SEQ5", Value = 1 });

                // 上で作った Name = "SEQ5", Value = 1 の新規データを INSERT
                context.SaveChanges();

                try
                {
                    using (var transaction = context.Database.BeginTransaction())
                    {
                        // 上で INSERT した Name = "SEQ5", Value = 1 のレコード
                        // の Value を 2 にして UPDATE
                        SequenceValue seq = context.Sequences.Find("SEQ5");
                        seq.Value = 2;                        
                        context.SaveChanges();

                        // Name = "SEQ2", Value = 1 のレコードを INSERT
                        // Name は主キーで "SEQ2" のレコードは　DB に存在する
                        // ので PK 制約違反
                        context.Sequences.Add(
                            new SequenceValue { Name = "SEQ2", Value = 100 });
                        context.SaveChanges();

                        // 上の SaveChanges で例外が発生するので Commit されない
                        // 結果 transaction が Dispose される際ロールバックされる
                        transaction.Commit();
                    }
                }
                catch (Exception)
                {
                    // 例外処理・・・何もしないと例外はなかったことになる
                }

                // キャッシュから取得する。ロールバックはキャッシュは書き換え
                // ないので、上のコードで seq.Value = 2 とした結果が取得される
                SequenceValue seq1 = context.Sequences.Find("SEQ5");

                // キャッシュから取得しないようにするには AsNoTracking() を
                // 追加して以下のようにする
                //SequenceValue seq1 = await context.Sequences
                //                           .AsNoTracking()
                //                           .SingleAsync(x => x.Name == "SEQ5");

                Console.WriteLine(seq1.Value);

                // DB に SELECT クエリを発行して全てレコードを取得してくるが
                // Name（主キー）が "SEQ5" のエンティティだけはキャッシュから
                // 取得して list を書き換える。                
                List<SequenceValue> list = await context.Sequences.ToListAsync();

                // 書き換えられないようにするには AsNoTracking() を追加して
                // 以下のようにする
                //List<SequenceValue> list = 
                //    await context.Sequences.AsNoTracking().ToListAsync();

                SequenceValue seq2 = list.Find(x => x.Name == "SEQ5");
                Console.WriteLine(seq2.Value);

                // Reload すると DB からデータを取得してキャッシュを書き換
                // えるので Value はロールバック後の値 1 になる
                SequenceValue seq3 = context.Sequences.Find("SEQ5");
                await context.Entry(seq3).ReloadAsync();
                Console.WriteLine(seq3.Value);
            }
        }
    }


    [Table("SEQUENCES")]
    public class SequenceValue
    {
        [Key, Column("NAME")] 
        public string Name { get; set; }
        
        [Column("VALUE")] 
        public int Value { get; set; }
    }

    public class TestDBContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            var path = @"C:\Users\...省略...\test.db";
            var connStr = "Data Source=" + path;

            builder.UseSqlite(connStr);

            // 出力ウィンドウに EF Core ログを表示
            builder.LogTo(msg => System.Diagnostics.Debug.WriteLine(msg));
        }
        public DbSet<SequenceValue> Sequences { get; set; }
    }
}

あと、コメントにも書きましたが、DbExtensions.AsNoTracking メソッドを適用すると "Returns a new query where the entities returned will not be cached in the DbContext or ObjectContext." ということで、キャッシュは使われなくなるようです。こういうやり方が正解なのかどうかは分かりませんが。

ASP.NET MVC アプリケーションで Entity Framework を利用してデータベースの編集・更新を行う方法について調べる機会があったのですが、その過程でいろいろ新発見があったので備忘録として残しておきます。

(1) EntityState.Modified の設定

データベースを更新 (UPDATE) するには、SaveChanges メソッドをコンテキストに適用する前に、当該エンティティの State を EntityState.Modified（以下、Modified と書きます）に設定する必要があります。

その方法には、自分が知る限りですが、以下の 2 つがあります：

(1-1) コードで明示的に設定

編集したオブジェクトをコンテキストにアタッチし、そのエンティティの State を Modified に設定します。

具体的な方法は、Microsoft の記事「Working with entity states」の Attaching an existing but modified entity to the context のセクションの説明とサンプルコードが参考になると思いますので、そちらを見てください。

なお、上に紹介した記事のコードは DbContext クラスがベースのコンテキストの場合ですので注意してください。（EF6 Code First の場合は DbContext クラスが使われます）

VS2010 の EF4 を使って DB First で作った EDM などには、DbContext クラスではなくて ObjectContext クラスが使われますが、それには Entry メソッドは定義されておらず、上に紹介した記事のコードのようにはできないので注意してください。具体的には以下のようにします。

[HttpPost]
public ActionResult Edit(Address address)
{
  if (ModelState.IsValid)
  {
    db.Address.Attach(address);
    db.ObjectStateManager.
      ChangeObjectState(address, EntityState.Modified);
    db.SaveChanges();
    return RedirectToAction("Index");
  }
  return View(address);
}

上のコードは、Microsoft が提供するサンプルデータベース AdventureWorksLT の Address テーブルから、VS2010 + EF4 を使って DB First で EDM を作成し、それをベースにスキャフォールディング機能を使って自動生成させたものです。

(1-2) エンティティのプロパティを書換

コンテキストから更新するエンティティを取得し、そのプロパティを使って内容を書き換えると、前の値から変更された場合は自動的にそのエンティティに Modified マークが付けられます。前の値と同じ値を設定した場合は Unchanged のままとなります。

言葉だけでは分かりにくいと思いますので、以下にコードを書いて説明します。

先の記事「EF でレコードの削除」で紹介した通りに Code First でデータベースを生成したとします。

Code First で生成した Blogs テーブルと Posts テーブルに、以下のコードでデータを INSERT します。

// DB に以下のデータを INSERT
Blog b = new Blog { Name = "プログラミング" };
b.Posts = new List<Post>();
b.Posts.Add(new Post { Title = "ASP", Content = "作り方" });
b.Posts.Add(new Post { Title = "WCF", Content = "書き方" });
b.Posts.Add(new Post { Title = "WPF", Content = "内容" });
db.Blogs.Add(b);
db.SaveChanges();

それを以下のようなコードで各エンティティのプロパティを使用して書き換えるとします。

// DB からエンティティを取得し、一部のプロパティを書換
BloggingContext db = new BloggingContext();
Blog b = db.Blogs.Single(i => i.BlogId == 1);
b.Name = "プログラミング";     // 変更なし
b.Posts[0].Title = "MVC";      // ASP ⇒ MVC
b.Posts[0].Content = "訂正";   // 作り方 ⇒ 訂正
b.Posts[1].Title = "NET";      // WCF ⇒ NET
b.Posts[1].Content = "書き方"; // 変更なし
b.Posts[2].Title = "WPF";      // 変更なし
b.Posts[2].Content = "内容";   // 変更なし

// 各エンティティの State を調べると：
EntityState state = db.Entry(b).State;  // Unchanged
foreach (Post p in b.Posts)
{
    state = db.Entry(p).State;
    // 順に、Modified, Modified, Unchanged
}

各エンティティの State を調べると、全てのプロパティに現在の値と同じ値を設定した場合は（即ち変更しない場合は）そのエンティティの State は Unchanged のままになり、一つでもプロパティが前の値から変更された場合はエンティティの State が自動的に Modified に変更されます。

注：Visual Studio 2010 に標準で備わっている EF4 を使って DB First で EDM を生成した場合はプロパティに現在の値と同じ値を設定しても Modified に変わるので注意してください。上の話は NuGet で EF6 を適用し、Code First でデータベースを生成した場合の話です。（EF4 / EF6 のバージョンの差によるものか、ObjectContext / DbContext のコンテキストのベースが違うためかは分かりません）

(2) MVC アプリで Modified マークをつける方法

エンティティに Modified マークをつけてから、そのエンティティをトラックしているコンテキストに SaveChanges メソッドを適用すればデータベースは更新 (UPDATE) されます。

ASP.NET MVC アプリでエンティティに Modified マークをつけるには、編集画面から送信されてきたユーザー入力情報を利用することになりますが、基本的には以下のような方法があると思います。

(2-1) UpdateModel メソッドを使う

Controller.UpdateModel メソッドは、フォーム、クエリ文字列、ルート、クッキーなどに含まれるクライアントから送信されてきたデータから、このメソッドの引数に設定されたエンティティのプロパティ名と一致するものを探して、それでエンティティの内容を書き換えます。

書き換えられると、上の (1-2) で書いたように、State が自動的に Unchanged から Modified に変わりますので、SaveChanges メソッドでデーターベースが更新 (UPDATE) されます。

UpdateModel メソッドを使う場合の問題は、フォーム、クエリ文字列、ルート、クッキーなどを使って送信されてきたデータのどれを使ってエンティティを書き換えているのかよく見えないところにあると思います。UpdateModel には多数のオーバーロードがあり、送信されたパラメータを指定 / 除外するためのオプションがあって、それらを使えばある程度コントロールできるとは思いますが・・・

そのあたりを配慮するとしても、先の記事「親子関係のあるデータの編集・削除」で書いたようなケースが問題です。

その記事に書いてありますが、UpdateModel メソッドを使うと、SaveChanges で子テーブルの関連するレコードの外部キーフィールドを NULL に書き換え、ポストされたデータでレコードを新たに作り INSERT するという動きになります。外部キーが NULL 不可に設定されている場合は特に問題で、当然制約違反でエラーになってしまいます。

これには、自分が考えた限りですが、対応不可でした。（何故そういう結果になるのか、メカニズムが解明できていません）

そもそも、普通はモデルバインディング＋データアノテーション検証の機能を利用している場合がほとんどでしょうから、そういうケースでは UpdateModel メソッドは忘れてもよさそうです。

モデルバインディング＋データアノテーション検証の機能を利用すると、アクションメソッドのパラメータ（引数）が指すオブジェクトにクライアントから送信されてきたデータがバインドされると同時に、サーバー側で検証が行われます。

コントローラーで ModelState.IsValid が ture であればアクションメソッドの引数が指すオブジェクトにバインドされたデータは検証結果 OK ということになります。

従って、モデルバインディング＋データアノテーション検証の機能を利用しているのであれば、アクションメソッドの引数を使った方が簡単＆確実だと思います。その方法は下の (2-2), (2-3) を見てください。

(2-2) バインディング結果をアタッチして Modified マーク

モデルバインディング＋データアノテーション検証の機能を利用している場合、アクションメソッドの引数が指すオブジェクトに更新後のデータはバインドされており、コントローラーで ModelState.IsValid が ture であればデータは検証結果 OK ということになります。

従って、アクションメソッドの引数が指すオブジェクトを、上の (1-1) で述べた方法でコンテキストにアタッチし、そのエンティティの State を Modified に設定してやることで目的が果たせます。

UpdateModel メソッドでは (2-1) に書いた問題があった親子関係のあるデータ（子はコレクション）の場合も、親子を別々にコンテキストにアタッチし、State を Modified に設定してやれば問題は解決できます。（具体例は、先の記事「親子関係のあるデータの編集・削除」の「Edit アクションメソッド」のコードを見てください）

(2-3) バインドされたデータでエンティティを書き換え

上の (2-2) の方法では無条件に State が Midified に設定されますので、ユーザーがデータを変更していなくても SaveChanges でデータベースに UPDATE がかかってしまいます。

先の記事「親子関係のあるデータの編集・削除」ではそのようにコーディングしましたが、子のデータのほんの一部のみ更新するケースも多々あるでしょうから考え直した方がよさそうです。

また、本来更新は不要でユーザーが送信する必要のないデータも送信する必要があるという面倒なこともあります。特に外部キーのデータは送信しないと State を Midified に設定する際参照整合性制約違反でエラーとなります。

上の (1-2) で書きましたが、コンテキストから更新するエンティティを取得し、そのプロパティを使って内容を書き換えると、前の値から変更された場合のみ Modified マークが付けられ、前の値と同じ値を設定した場合は Unchanged のままとなります。

アクションメソッドの引数が指すオブジェクトにモデルバインドされたデータがあるのですから、自力でコードを書いて更新する項目のみそれらのデータをエンティティに反映させた方がよさそうです。すべてを自分のコントロール下で設定できるので安心・安全ということもあるかもしれません。

上記のことを考慮して、先の記事「親子関係のあるデータの編集・削除」を書き直すと、以下のようになるでしょうか。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Entity;
using Mvc4App3.Models;

namespace Mvc4App3.Controllers
{
  public class ParentChildController : Controller
  {
    private ParentChildContext db = new ParentChildContext();
        
    // ・・・中略・・・

    // GET: /ParentChild/Edit/5
    public ActionResult Edit(int id)
    {
      Parent parent = db.Parents.Find(id);
      if (parent == null)
      {
        return HttpNotFound();
      }
      return View(parent);
    }

    // POST: /ParentChild/Edit/5
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit(Parent postedParent)
    {
      if (ModelState.IsValid)
      {
        // 編集画面から送信された親の Id で、コンテキストから
        // 該当する親エンティティを取得
        Parent parent = db.Parents.Find(postedParent.Id);

        if (parent == null)
        {
          // 見つからなかったときの処理
        }

        // 編集画面から送信されたデータで、上で取得した親エン
        // ティティの中身を書き換え
        parent.Name = postedParent.Name;

        foreach (Child postedChild in postedParent.Children)
        {
          // 編集画面から送信された子の Id で、コンテキスト
          // から該当する子エンティティを取得
          Child child = db.Children.Find(postedChild.Id);
                    
          if (child == null)
          {
            // 見つからなかったときの処理
          }
                    
          // 編集画面から送信されたデータで、上で取得した
          // 子エンティティの中身を書き換え
          child.Name = postedChild.Name;
        }

        db.SaveChanges();

        return RedirectToAction("Index");
      }
      return View(postedParent);
    }

    // ・・・中略・・・

  }
}

上のコードのコメントの「見つからなかったときの処理」ですが、編集画面を表示する際に取得した Id を隠しフィールドで保持しており、更新するときそれをそのまま POST しているので、普通なら見つからないということはあり得ません。見つからないとすれば、編集中に誰かが DB の当該レコードを削除してしまったとか、不正なデータが送信されたなどの異常事態と考えた方がよいかもしれません。