■Entity Framework 6 Top


1.ログ

2.DB時間取得

3.SELECT

4.UPDATE

5.INSERT

6.DELETE

7.トランザクション

8.楽観的同時実行制御


■ログ

ログ
ログ
using System;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // ログを出力するAction設定
            db.Database.Log = (x) =>
            {
               // ログファイル出力
               using( StreamWriter sr = new StreamWriter(DateTime.Now.ToString("yyyyMMdd") + ".log", true, Encoding.GetEncoding("Shift_JIS")))
               {
                  sr.Write(x);
               };
            };
         
            var query = from tbl in db.Book
                        where tbl.Publisher == "発行元1"
                        select tbl;

            query.Any();
         }
      }
   }
}

■DB時間取得

データベース システムの現在のタイムスタンプを取得
データベース システムの現在のタイムスタンプを取得する
using System;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            Debug.Print(db.Database.SqlQuery<DateTime>("select getdate()").Single().ToString("yyyy/MM/dd HH:mm:ss"));
         }
      }
   }
}

■SELECT

SELECT
SELECT
-- テストデータ作成

DROP TABLE Book
CREATE TABLE Book(
   Id          int           IDENTITY( 1, 1 ) PRIMARY KEY,
   Name        nvarchar(30)  NOT NULL,
   Author      nvarchar(30)  NOT NULL,
   Publisher   nvarchar(30)  NOT NULL,
   CategoryId  int           NOT NULL,
   Memo        nvarchar(100) DEFAULT NULL
)

DROP TABLE BookCategory
CREATE TABLE BookCategory(
   CategoryId  int           IDENTITY( 1, 1 ) PRIMARY KEY,
   Name      nvarchar(30)    NOT NULL,
   Memo      nvarchar(100)   DEFAULT NULL
)

TRUNCATE TABLE Book

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId, Memo )
   VALUES
   ( 'C++参考書', '著者1', '発行元1', 1, 'メモ' )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId, Memo )
   VALUES
   ( 'C#参考書', '著者1', '発行元1', 1, 'メモ2' )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( 'Python参考書', '著者2', '発行元2', 1 )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( 'Ruby参考書', '著者3', '発行元2', 1 )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( 'JavaScript参考書', '著者4', '発行元3', 1 )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( '画像処理', '著者5', '発行元4', 1 )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( '小説1', '著者6', '発行元5', 2 )

INSERT INTO Book
   ( Name, Author, Publisher, CategoryId )
   VALUES
   ( 'コミック1', '著者7', '発行元6', 3 )

TRUNCATE TABLE BookCategory

INSERT INTO BookCategory
   ( Name )
   VALUES
   ( '技術書' )

INSERT INTO BookCategory
   ( Name )
   VALUES
   ( '小説' )

// Form1.cs using System; using System.Diagnostics; using System.Linq; using System.Windows.Forms; namespace Entity_Framework_Sample01 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { using (var db = new SQLExpressEntities()) { // クエリー式構文 var query = from a in db.Book where a.Publisher == "発行元1" select new Models.BookContent { Id = a.Id, Name = a.Name, Author = a.Author, Publisher = a.Publisher, CategoryId = a.CategoryId, Memo = a.Memo }; // メソッド構文 var query2 = db.Book .Where(a => a.Publisher == "発行元1") .Select(a => new Models.BookContent { Id = a.Id, Name = a.Name, Author = a.Author, Publisher = a.Publisher, CategoryId = a.CategoryId, Memo = a.Memo }); if( query.Any() ) { query.ToList().ForEach( a => { Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.Memo ); }); } else { Debug.Print("データなし"); } } } } }

ORDER BY
ソート
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        orderby a.Publisher ascending, a.Name descending   // Publisherで昇順、Nameで降順
                        select new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        };

            // メソッド構文
            var query2 = db.Book
                .OrderBy(a => a.Publisher).ThenByDescending(a => a.Name)
                .Select(a => new Models.BookContent {
                    Id = a.Id,
                    Name = a.Name,
                    Author = a.Author,
                    Publisher = a.Publisher,
                    CategoryId = a.CategoryId,
                    Memo = a.Memo
                });

            if( query.Any() )
            {
               query.ToList().ForEach( a => {
                 Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

GROUP BY
GROUP BY
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        group a by a.Author into g
                        select new Models.GroupByContent{
                           Count = g.Count(),
                           Author = g.Key
                        };

            // メソッド構文
            var query2 = db.Book
                .GroupBy(a => a.Author)
                .Select(a => new Models.GroupByContent {
                    Count = a.Count(),
                    Author = a.Key
                });
                
            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print(a.Author + "(" + a.Count + ")" + "\t");
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

著者1(2)	著者2(1)	著者3(1)	著者4(1)	著者5(1)	著者6(1)	著者7(1)


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        // Authorをキーとしてグルーピング
                        group a by a.Author;

            // メソッド構文
            var query2 = db.Book
                .GroupBy(a => a.Author);
                
            if (query.Any())
            {
                string msg = "";

                var list = query.ToList();
                
                // キーで列挙
                foreach(var group in list) {
                    msg += group.Key + "\t";

                    // キーごとにグルーピングした内容の列挙
                    foreach(var book in group) {
                        msg += book.Name + "\t";
                    }
                    msg += "\t";
                }
                Debug.Print(msg);
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

著者1	C++参考書	C#参考書	著者2	Python参考書	著者3	Ruby参考書	著者4	JavaScript参考書	著者5	画像処理	著者6	小説1	著者7	コミック1


HAVING
HAVING
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        where a.CategoryId == 0       // WHERE句
                        group a by a.Author into g
                        where g.Count() == 2            // HAVING句
                        select new
                        {
                           Count = g.Count(),
                           Author = g.Key
                        };

            // メソッド構文
            var query2 = db.Book
                .Where(a => a.CategoryId == 0)
                .GroupBy(a => a.Author)
                .Where(a => a.Count() > 1)
                .Select(a => new Models.GroupByContent {
                    Count = a.Count(),
                    Author = a.Key
                });

            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print(a.Count + " " + a.Author);
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

DISTINCT
DISTINCT
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文に対応する文法はない

            // メソッド構文
            var query = db.Book
                .Select(a => new Models.AutorContent {
                    Autor = a.Author
                })
                .Distinct();
                
            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print(a.Autor);
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

LIKE
LIKE

前方一致で検索する場合は、StartsWith、後方一致で検索する場合は、EndsWithを使用する。大文字小文字区別しない。


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        where a.Name.Contains("c")
                        select new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        };

            // メソッド構文
            var query2 = db.Book
                .Where(a => a.Name.Contains("c"))
                .Select(a => new Models.BookContent {
                    Id = a.Id,
                    Name = a.Name,
                    Author = a.Author,
                    Publisher = a.Publisher,
                    CategoryId = a.CategoryId,
                    Memo = a.Memo
                });

            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

BETWEEN
BETWEEN
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        where 2 <= a.Id && a.Id <= 3
                        select new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        };

            // メソッド構文
            var query2 = db.Book
                .Where(a => 2 <= a.Id && a.Id <= 3)
                .Select(a => new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        });

            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

IN
IN
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        where a.Id == 2 || a.Id == 3
                        select new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        };

            // メソッド構文
            var query2 = db.Book
                .Where(a => a.Id == 2 || a.Id == 3)
                .Select(a => new Models.BookContent {
                            Id = a.Id,
                            Name = a.Name,
                            Author = a.Author,
                            Publisher = a.Publisher,
                            CategoryId = a.CategoryId,
                            Memo = a.Memo
                        });
                        
            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

内部結合
INNER JOIN

join句で結合したテーブルに存在するレコードのみ取得する


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文
            var query = from a in db.Book
                        join b in db.BookCategory on a.CategoryId equals b.CategoryId
                        select new Models.BookContent2 {
                            bookContent = new Models.BookContent {
                                Id = a.Id,
                                Name = a.Name,
                                Author = a.Author,
                                Publisher = a.Publisher,
                                CategoryId = a.CategoryId,
                                Memo = a.Memo
                            },
                            bookCategoryContent = new Models.BookCategoryContent {
                                CategoryId = b.CategoryId,
                                Name = b.Name,
                                Memo = b.Memo
                            }
                        };

            // メソッド構文
            var query2 = db.Book
                .Join(db.BookCategory, a => a.CategoryId, b => b.CategoryId,
                (a, b) => new Models.BookContent2 {
                    bookContent = new Models.BookContent {
                        Id = a.Id,
                        Name = a.Name,
                        Author = a.Author,
                        Publisher = a.Publisher,
                        CategoryId = a.CategoryId,
                        Memo = a.Memo
                    },
                    bookCategoryContent = new Models.BookCategoryContent {
                        CategoryId = b.CategoryId,
                        Name = b.Name,
                        Memo = b.Memo
                    }
                });
                
            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  // サンプルの場合BookCategoryにコミックレコードが存在しないのでコミックに該当するレコードは取得されない
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo + " " + b.CategoryId + " " + b.Name + " " + b.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

外部結合
OUTER JOIN

join句で結合したテーブルに存在しないレコードもすべて取得する。


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文         
            var query = from a in db.Book
                        join b in db.BookCategory on a.CategoryId equals b.CategoryId into c
                        from d in c.DefaultIfEmpty()
                        select new Models.BookContent2 {
                            bookContent = new Models.BookContent {
                                Id = a.Id,
                                Name = a.Name,
                                Author = a.Author,
                                Publisher = a.Publisher,
                                CategoryId = a.CategoryId,
                                Memo = a.Memo
                            },
                            bookCategoryContent = new Models.BookCategoryContent {
                                CategoryId = d.CategoryId,   // BookCategoryContentモデルのCategoryIdはnull許容型である必要がある
                                Name = d.Name,
                                Memo = d.Memo
                            }
                        };

            // メソッド構文
            var query2 = db.Book
                .GroupJoin(db.BookCategory, a => a.CategoryId, b => b.CategoryId,
                (a, b) => new Models.BookContent2 {
                    bookContent = new Models.BookContent {
                        Id = a.Id,
                        Name = a.Name,
                        Author = a.Author,
                        Publisher = a.Publisher,
                        CategoryId = a.CategoryId,
                        Memo = a.Memo
                    },
                    bookCategoryContent = new Models.BookCategoryContent {
                        CategoryId = b.FirstOrDefault().CategoryId,
                        Name = b.FirstOrDefault().Name,
                        Memo = b.FirstOrDefault().Memo
                    }
                });

            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo + " " + b.CategoryId + " " + b.Name + " " + b.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

部分検索
Skip/Take

取得したレコードの一部を取得する。Skipは取得するレコードの開始インデックス、Takeで取得件数を指定する。


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文に対応する文法はない

            // メソッド構文
            var query2 = db.Book
                .OrderBy(a => a.Name)
                .Skip(1).Take(2)
                .Select(a => new Models.BookContent {
                    Id = a.Id,
                    Name = a.Name,
                    Author = a.Author,
                    Publisher = a.Publisher,
                    CategoryId = a.CategoryId,
                    Memo = a.Memo
                });

            if (query2.Any())
            {
               query2.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

// C++参考書
// JavaScript参考書


先頭データのみ取得
First

取得したレコードのうち先頭1件のみ取得する。


// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文に対応する文法はない

            // メソッド構文
            var query2 = db.Book
                .Where(a => a.Id == 100)
                .OrderBy(a => a.Name)
                .Select(a => new Models.BookContent {
                    Id = a.Id,
                    Name = a.Name,
                    Author = a.Author,
                    Publisher = a.Publisher,
                    CategoryId = a.CategoryId,
                    Memo = a.Memo
                }).FirstOrDefault();   // データが1件も見つからない場合は初期値を返す

            Debug.Print( query2.Id + " " + query2.Name + " " + query2.Publisher + " " + query2.CategoryId + " " + query2.Memo );
         }
      }
   }
}

動的クエリ
動的にクエリを生成する。
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         int id = 0;
         
         using (var db = new SQLExpressEntities())
         {
            // クエリー式構文に対応する文法はない

            // メソッド構文
            IQueryable query2 = db.Book;

            // Where文を動的に変更する
            switch (id) {
            case 0:
                query2 = query2.Where(a => a.Id == 1);
                break;
            case 1:
                query2 = query2.Where(a => a.Name == "Python参考書");
                break;
            }

            var query3 = query2.Select(a => new Models.BookContent {
                    Id = a.Id,
                    Name = a.Name,
                    Author = a.Author,
                    Publisher = a.Publisher,
                    CategoryId = a.CategoryId,
                    Memo = a.Memo
                });
                
            if (query3.Any())
            {
               query3.ToList().ForEach(a =>
               {
                  Debug.Print( a.Id + " " + a.Name + " " + a.Publisher + " " + a.CategoryId + " " + a.Memo );
               });
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

■UPDATE

UPDATE
UPDATE
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // 更新対象レコードを検索
            var book = db.Book.Find(1);

            if (book != null)
            {
               book.Memo = "メモ更新";

               try
               {
                  // Commit
                  int cnt = db.SaveChanges();
                  Debug.Print(cnt.ToString() + "件データ更新終了");
               }
               catch (System.Data.Entity.Validation.DbEntityValidationException ex)
               {
                  ex.EntityValidationErrors.ToList().ForEach(a =>
                  {
                     a.ValidationErrors.ToList().ForEach(b =>
                     {
                        Debug.Print(b.ErrorMessage);
                     });
                  });
               }
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

■INSERT

INSERT
INSERT
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            const string BookCategoryName = "攻略本";

            // 追加するレコードが存在するかを検証
            var query = from tbl in db.BookCategory
                        where tbl.Name == BookCategoryName
                        select tbl;

            // 存在しない場合
            if (!query.Any())
            {
               // データ追加
               db.BookCategory.Add(new BookCategory()
                  {
                     Name = BookCategoryName,
                  });

               try
               {
                  // Commit
                  int cnt = db.SaveChanges();
                  Debug.Print(cnt.ToString() + "件データ追加終了");
               }
               catch (System.Data.Entity.Validation.DbEntityValidationException ex)
               {
                  ex.EntityValidationErrors.ToList().ForEach(a =>
                  {
                     a.ValidationErrors.ToList().ForEach(b =>
                     {
                        Debug.Print(b.ErrorMessage);
                     });
                  });
               }
            }
            else
            {
               Debug.Print("データあり");
            }
         }
      }
   }
}

■DELETE

DELETE
DELETE
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // プライマリキーを1で検索
            var book = db.Book.Find(1);
            
            // 存在する場合
            if (book != null)
            {
               db.Book.Remove(book);
               
               try
               {
                  // Commit
                  int cnt = db.SaveChanges();
                  Debug.Print(cnt.ToString() + "件データ削除終了");
               }
               catch (System.Data.Entity.Validation.DbEntityValidationException ex)
               {
                  ex.EntityValidationErrors.ToList().ForEach(a =>
                  {
                     a.ValidationErrors.ToList().ForEach(b =>
                     {
                        Debug.Print(b.ErrorMessage);
                     });
                  });
               }
            }
            else
            {
               Debug.Print("データなし");
            }
         }
      }
   }
}

自動発番した値を取得
自動発番した項目値は、Addしたオブジェクトが更新されるのでそれで確認できる。
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            const string BookCategoryName = "攻略本";

            // 追加するレコードが存在するかを検証
            var query = from tbl in db.BookCategory
                        where tbl.Name == BookCategoryName
                        select tbl;

            // 存在しない場合
            if (!query.Any())
            {
               var bookCategory = new BookCategory()
               {
                  Name = BookCategoryName,
               };
            
               // データ追加
               db.BookCategory.Add(bookCategory);

               try
               {
                  // Commit
                  db.SaveChanges();
                  Debug.Print("CategoryId:" + bookCategory.CategoryId.ToString() + "のレコード追加");
               }
               catch (System.Data.Entity.Validation.DbEntityValidationException ex)
               {
                  ex.EntityValidationErrors.ToList().ForEach(a =>
                  {
                     a.ValidationErrors.ToList().ForEach(b =>
                     {
                        Debug.Print(b.ErrorMessage);
                     });
                  });
               }
            }
            else
            {
               Debug.Print("データあり");
            }
         }
      }
   }
}


■トランザクション

トランザクション
自前でトランザクション管理を行う。
// Form1.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         // トランザクション開始
         using( var tran = db.Database.BeginTransaction() )
         {
            try
            {
               // Bookテーブルの更新対象レコードを検索
               var query = from tbl in db.Book
                           where tbl.Name == "C++参考書"
                           select tbl;

               if (query.Any())
               {
                  query.ToList().ForEach(a =>
                  {
                     // データ更新
                     a.Memo = "メモ書き込み";
                  });

                  // 自前でトランザクション管理する場合、ここでCommitされない
                  db.SaveChanges();
               }
               else
               {
                  Debug.Print("データなし");
               }

               // BookCategoryテーブルの更新対象レコードを検索
               var query2 = from tbl in db.BookCategory
                            where tbl.Name == "技術書"
                            select tbl;

               if (query2.Any())
               {
                  query2.ToList().ForEach(a =>
                  {
                     // データ更新
                     // a.Memo = "メモ書き込み";
                     a.Memo = "0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789a";
                  });

                  // 自前でトランザクション管理する場合、ここでCommitされない
                  db.SaveChanges();
               }
               else
               {
                  Debug.Print("データなし");
               }

               tran.Commit();
               Debug.Print("Commit");            
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
               tran.Rollback();
               Debug.Print("RollBack");

               ex.EntityValidationErrors.ToList().ForEach(a =>
               {
                  a.ValidationErrors.ToList().ForEach(b =>
                  {
                     Debug.Print(b.ErrorMessage);
                  });
               });
            }
         }
      }
   }
}

■楽観的同時実行制御

楽観的同時実行制御
データ参照時に排他ロックをかけず、データの更新時に他のユーザーによってデータが更新されていた場合にエラーする。
Visual Studio 上でテーブルの項目のプロパティを変更するだけで実装できる。

サンプルではBookテーブルのMemo項目に対し同時実行モードを有効にしている。この場合、検索処理とCommitの間にMemo項目に対して他端末で更新されている場合
Memo項目を更新しようとすると、DbUpdateConcurrencyExceptionが発生する。


using System;
using System.Diagnostics;
using System.Linq;
using System.Windows.Forms;

namespace Entity_Framework_Sample01
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         using (var db = new SQLExpressEntities())
         {
            // 更新対象レコードを検索
            var query = from tbl in db.Book
                        where tbl.Name == "C++参考書"
                        select tbl;

            if (query.Any())
            {
               query.ToList().ForEach(a =>
               {
                  // データ更新
                  a.Memo = DateTime.Now.ToString();
               });

               try
               {
                  // Commit
                  int cnt = db.SaveChanges();
                  Debug.Print(cnt.ToString() + "件データ更新終了");
                  label1.Text = cnt.ToString() + "件データ更新終了";
               }
               catch (System.Data.Entity.Infrastructure.DbUpdateConcurrencyException ex1)
               {
                  Debug.Print(ex1.Message);
                  label1.Text = ex1.Message;
               }
               catch (System.Data.Entity.Validation.DbEntityValidationException ex2)
               {
                  ex2.EntityValidationErrors.ToList().ForEach(a =>
                  {
                     a.ValidationErrors.ToList().ForEach(b =>
                     {
                        Debug.Print(b.ErrorMessage);
                        label1.Text = b.ErrorMessage;
                     });
                  });
               }
            }
            else
            {
               Debug.Print("データなし");
               label1.Text = "データなし";
            }
         }
      }
   }
}

Top
inserted by FC2 system