WebSurfer's Home

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

EF でレコードの削除

by WebSurfer 2015年12月21日 16:34
2016/9/13 書換
間違いや新事実を見つけて部分的な書き換えや追記を行っているうちに意味がよく分からない記事になってしまったので、内容を整理して全面的に書き換えました。

Entity Framework Code First の機能を利用して SQL Server データーベースに作った親子関係のあるテーブルで、レコードの削除を行った際にハマって悩んだ話を書きます。

以下のコードは、Microsoft のチュートリアル「新しいデータベースの Code First」に記載されていたクラス定義ですが、これをそのままサンプルとして使用して説明します。

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
} 

上記のコードをベースに、チュートリアルの手順通りに SQL Server 2008 Express にデータベースを作ると以下の画像のテーブルとフィールドが生成されます。上に親子関係のあるテーブルと書きましたが、Blogs が親、Posts が子です。「Code First の規約 (Code First Conventions)」に従い、Posts テーブルに BlogId という名前の外部キーフィールドが生成され、NULL 不可になっているところに注目してください。

生成された DB

これに上の画像に示すデータを追加した後、例えば以下のようなコードで、コンテキストから一つの親(Blog オブジェクト)を取得し、その中の子のコレクション(List<Post>)から最初の要素を削除した後、SaveChanges メソッドでデータベースに結果を反映しようとしたとします。(Posts テーブルの中の PostId が 4 のレコードを削除しようと試みたということです)

class Program
{
  static void Main(string[] args)
  {
    using (var db = new BloggingContext())
    {
      var b = db.Blogs.Single(i => i.BlogId == 3);
      b.Posts.Remove(b.Posts[0]);
      db.SaveChanges();
    }
  }
}

そうすると、db.SaveChanges(); で InvalidOperationException がスローされます。エラーメッセージは以下のようになります。

"The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."

上のエラーメッセージが何を言っているかを簡単に言うと「Posts テーブルの BlogId フィールドは NULL 不可だが、それを NULL にしようとして失敗した」ということです。

つまり、上記のコードは Posts テーブルの当該レコードを DELETE するのではなく、当該レコードの外部キーフィールド BlogId を NULL にしようとします。結果、BlogId フィールドは NULL 不可なので失敗します。

コードを少し変更(b.Posts.Remove... の b を db 変更)して以下のようにすると削除に成功します。上の画像の Posts テーブルのレコード一覧で赤枠で囲った部分が下のコードの実行結果ですが、元あった PostId が 7 のレコードが削除されています。

class Program
{
  static void Main(string[] args)
  {
    using (var db = new BloggingContext())
    {
      var b = db.Blogs.Single(i => i.BlogId == 4);
      db.Posts.Remove(b.Posts[0]);
      db.SaveChanges();
    }
  }
}

ハマったのはこの違いです。b と db で何が違うのでしょうか? 以下にその説明を書きます。

前者のコード b.Posts.Remove(b.Posts[0]);

b は Blog クラスのオブジェクトです。詳しく言うと、BloggingContext オブジェクトから Blogs プロパティを使って DbSet<Blog> オブジェクト(親のコレクション)を取得し、その中から BlogId == 3 の条件で取得した Blog オブジェクトです。

なので、前者のコードの意味は、Blog オブジェクトから Posts プロパティを使って List<Post>(外部キーで関連付けられた Post オブジェクトのコレクション)を取得し、それから b.Posts[0] に該当する子を削除するということになります。

つまり、親子の関係を絶つという指示を出しただけで、データベースの Posts テーブルから該当するレコードを削除していいとは誰も言ってないです。

親子の関係を絶つというのは、データベース上では Posts テーブルの当該レコードの外部キーフィールド BlodId を NULL に設定することに相当し、db.SaveChanges() でその操作が行われたということのようです。

なお、外部キーフィールド BlodId を NULL に設定する動きになると言っても、b.Posts[0] の BlogId プロパティが null に書き換えられるわけではないので注意してください(元の値のまま変わりません)。

コード上では、(1) 親が保持する子のコレクション List<Post> から b.Posts[0] に該当する子が外され、(2) b.Posts[0] に該当するエンティティの EntityState が Unchanged から Modified に変わるのみです。

結果からの想像ですが、フレームワークは上の (1), (2) を見て、db.SaveChanges() でデータベースの Posts テーブルの当該レコードの外部キーフィールド BlodId を NULL に設定しようとするようです。

後者のコード db.Posts.Remove(b.Posts[0]);

前者のコードからは、b(Blog オブジェクト)が db(BloggingContext オブジェクト)に変わっている点に注意してください。

なので、後者のコードの意味は、BloggingContext オブジェクト の Posts プロパティを使って DbSet<Post> オブジェクト(子エンティティのコレクション)を取得し、それから b.Posts[0] に該当する子エンティティを削除するということになります。

データベース上では Posts テーブルの当該レコードを削除することに相当するので、当該子エンティティの EntityState は Deleted に設定され、db.SaveChanges() で当該レコードは削除されます。


Posts テーブルの BlogId フィールドが NULL 不可になるのは Post クラスの外部キープロパティ BlogId を int 型にしたからです(クラスに定義されるプロパティが null にできない型の場合は、Code First の規約に従って、データベースのフィールドも NULL 不可になります)。

Microsoft の文書「Code First の規約」によると、外部キーフィールドの NULL 可 / 不可によって DELETE 操作の結果に以下の違いがあるそうです。

"依存エンティティの外部キーで null 値が許容されない場合、Code First はリレーションシップに連鎖削除を設定します。依存エンティティの外部キーで null 値が許容される場合は、Code First はリレーションシップに連鎖削除を設定しないため、プリンシパルが削除されると外部キーが null に設定されます"

上の例では Posts テーブルの BlogId フィールドは NULL 不可なので cascade delete が設定され、例えば以下のようなコードで親を DELETE すると関連する子も DELETE されます。

Blog b = db.Blogs.Single(i => i.BlogId == 6);
db.Blogs.Remove(b);
db.SaveChanges();

Post クラスの外部キープロパティ BlogId を int? 型(null 可)に変更して、Code First の機能を使って Posts テーブルを作ると、Code First の規約に従って、外部キーフィールド BlogId は NULL 可になります。

そのようにして作成したデータベースに対しては、外部キーフィールドBlogId は NULL 可なので当然ですが、上の「前者のコード」でエラーにならず、Posts テーブルの当該レコードの BlogId は NULL に設定されます。

「後者のコード」では Posts テーブルの当該レコードは削除されます。

一つ分からないのが、Microsoft の文書に "依存エンティティの外部キーで null 値が許容される場合は、Code First はリレーションシップに連鎖削除を設定しないため、プリンシパルが削除されると外部キーが null に設定されます" と書いてあったのに、実際に試したらそうなならなかった点です。

子を持つ親を削除しようとしたら FK 制約に引っかかって SqlException がスローされました。子があるのに先に親を削除しようとしたようです。なぜ Microsoft の文書と違うのか理由は不明です。

Tags:

ADO.NET

接続文字列でのデータベース名の指定

by WebSurfer 2014年5月30日 16:37

SQL Server の接続文字列で Initial Catalog(データベース名)の指定は必要かという話を書きます。

MSDN ライブラリなどでは、SQL Server の既定のインスタンスまたは名前つきインスタンスに接続する場合の接続文字列(正確には SqlClient 接続文字列)の例として、Initail Catalog(もしくは Database)キーワードでデータベース名を指定するように書かれています。

具体例は下記のページを見てください。

Initial Catalog(もしくは Database)のない接続文字列の例は、自分が知る限りですが、見たことがないです。

なので、接続文字列にデータベース名の指定は必須かと思い込んでいましたが、ある機会に試してみたところ、そんなことはなかったです。

以下の条件が満足されれば、任意のデータベースからデータを取得できます。

  1. SQL Server の既定のインスタンスまたは名前つきインスタンスが対象。(ユーザーインスタンスとか LacalDB など、.mdf ファイルを動的にアタッチするようなケースはダメです)
  2. 同一インスタンス内にある、静的にアタッチ済みのデータベースを対象とする。
  3. 接続文字列で指定したログイン権限で SQL Server による認証が通ってログインできること。
  4. 接続文字列にデータベース名の指定がない場合、もしくは指定されていてもそれとは異なるデータベースを対象とする場合は、データベース名を特定できるクエリを記述する。例えば、 [データベース名].[スキーマ名].[テーブル名] というように。
  5. ログインしたユーザーが、目的のデータベースに対して必要な権限を持っている(すなわち、SQL Server によるユーザー承認が通る)。具体的には、先の記事 ユーザー権限の設定 に書いたような設定がしてあることが条件です。

ADO.NET のコードを使って説明した方が分かりやすいと思いますので、以下にサンプルをアップしておきます。説明はコメントに書きましたので見てください。

using System;
using System.Data.SqlClient;

class Program
{
  static void Main(string[] args)
  {
    // Initial Catalog=PUBS でも NORTHWIND から取得できる。
    // Initial Catalog の指定なしでも OK。下はその例。
    // ただしクエリに DB 名の指定が必要。下の例を参照。
    // Integrated Security=True まで省くとログインできない。
    string connString1 =
      "Data Source=tcp:papiko-pc;Integrated Security=True";
    string query1 =
      "SELECT CategoryID, CategoryName " + 
          "FROM [NORTHWIND].[dbo].[Categories];" +
      "SELECT EmployeeID, LastName " + 
          "FROM [NORTHWIND].[dbo].[Employees]";
    RetrieveMultipleResults(new SqlConnection(connString1), query1);

    // Initial Catalog を指定すればクエリで DB 名の指定は不要(下
    // の例では NORTHWIND に含まれる Categories テーブル)。
    // ただし、titleauthor テーブルは PUBS に含まれるものなので、
    // 下の例のようにクエリに DB 名の指定が必要。
    string connString2 =
      "Data Source=tcp:papiko-pc;Initial Catalog=NORTHWIND;" + 
      "Integrated Security=True";
    string query2 =
      "SELECT CategoryID, CategoryName FROM Categories;" +
      "SELECT royaltyper, au_id FROM [PUBS].[dbo].[titleauthor]";
    RetrieveMultipleResults(new SqlConnection(connString2), query2);
  }

  // MSDN ライブラリ「DataReader によるデータの取得」
  // http://msdn.microsoft.com/ja-jp/library/haa3afyz.aspx
  // NextResult による複数の結果セットの取得
  static void RetrieveMultipleResults(
    SqlConnection connection, string query)
  {
    using (connection)
    {
      SqlCommand command = new SqlCommand(query, connection);
      connection.Open();

      SqlDataReader reader = command.ExecuteReader();

      while (reader.HasRows)
      {
        Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
            reader.GetName(1));

        while (reader.Read())
        {
          Console.WriteLine("\t{0}\t{1}", 
              reader.GetInt32(0), reader.GetString(1));
        }
        // バッチ Transact-SQL ステートメントの結果を読み込む
        // ときに、データリーダーを次の結果に進めます。
        reader.NextResult();
      }
    }
  }
}

よく分からないのは、上記のようなことができるのに、何のためにデータベース名を接続文字列に指定するかということです。

クエリを書くときにデータベース名を省略できるぐらいのメリットしか思い浮かびません。他に、コードファーストを利用する際 Initial Catalog に指定された名前でデータベースを作ってくれるということもあるそうですが。でも、そんな理由ではなさそうです。

接続プールが複数作られることになるのも無駄なような気がします。(Initial Catalog が違えばもちろん、キーワードを異なる順序で指定しただけでも接続プールは異なるとのこと。詳しくは MSDN ライブラリ SQL Server の接続プール (ADO.NET) を参照)

上のサンプルの結果からわかるように、接続プールは接続文字列で指定されるインスタンスに接続するためにあるだけで、インスタンス中にあるどのデータベースに接続するかは関知しないです。

それなのに、わざわざ接続文字列にデータベースを指定して、複数の接続プールを作る理由が分かりません。

そもそも、同じ接続プールで複数の異なるデータベースに接続するなどということはやってはいけないことで、好ましからざる副作用があるのでしょうか?

Tags: ,

ADO.NET

反復子メソッドと using 句

by WebSurfer 2013年6月24日 14:55

このようなトラブルに悩む人はいないかもしれませんが、yield return ステートメント を使用した反復子のメソッドで using 句を使ったデータベースアクセスを行う場合の注意点を備忘録として書いておきます。

エラーメッセージ

下のコードを見てください(SqlDataSource コントロールを利用すれば自力でこのようなコードを書く必要はありませんが、あくまで例として書きました)。

GetData が反復子のメソッドです。ここで注意しなければならないことは、このメソッドの戻り値(この例では IEnumerable<Employee> 型として定義されている employees)が foreach ループなどで反復処置されるまで GetData メソッドは呼び出されないことです。

従って、using のスコープの中で、まだ SqlDataReader が閉じられてないうちに、反復処置を完了しなければなりません。この例では、反復処置は GridView1.DataBind メソッドで行われますので、using のスコープの中で GridView1.DataBind メソッドを実行しなければなりません。

using のスコープを抜けた後で GridView1.DataBind メソッドを実行すると(下のコードでコメントアウトしたように)、その時点では SqlDataReader は閉じられているので、上の画像のように reader.Read() のところで "リーダーが閉じている場合は、Read の呼び出しは無効です。" というエラーが出ます。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  public class Employee
  {
    public string ID { get; set; }
    public string Name { get; set; }
  }

  // yield return を使用した反復子のメソッド。戻り値が
  // foreach ループなどで反復処置されるまで呼び出されない。
  public IEnumerable<Employee> GetData(SqlDataReader reader)
  {
    while (reader.Read())
    {
      Employee employee = new Employee();
      employee.ID = reader["EmployeeID"].ToString();
      employee.Name = reader["LastName"].ToString();
      yield return employee;
    }
  }

  IEnumerable<Employee> employees = null;
    
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      string connString = 
        System.Web.Configuration.WebConfigurationManager.
        ConnectionStrings["Northwind"].ConnectionString;
      string query = 
        "SELECT EmployeeID, LastName FROM Employees";

      using (SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader reader = cmd.ExecuteReader())
          {
            if (reader != null)
            {
              employees = GetData(reader);

              // GetData メソッドが実行されるのは、employees が
              // foreach などによって反復処置される時。ここで 
              // 以下のようにデータバインドすれば reader が開い
              // ているうちに反復処置が完了するので問題なし。
              GridView1.DataSource = employees;
              GridView1.DataBind();
            }
          }
        }
      }

      // ここでバインドしたのではダメ。GetData メソッドは
      // using 句を抜けてから実行され、reader.Read() で
      // "リーダーが閉じている場合は、Read の呼び出しは無
      // 効です。" というエラーが出る。
      //GridView1.DataSource = employees;
      //GridView1.DataBind();
    }        
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
  </div>
  </form>
</body>
</html>

Tags:

ADO.NET

About this blog

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

Calendar

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

View posts in large calendar