１．クエリー式
|
1 2 3 4 5 6 7 8 9 10 11 12 |
using (var db = new Model1()) { var query = from kokyaku in db.m_kokyaku where kokyaku.id >= 10 && kokyaku.id <= 20 orderby kokyaku.id descending select kokyaku; foreach (var k in query) { Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1); } } |
２．ラムダ式 パターン１
|
1 2 3 4 5 6 7 8 9 10 11 12 |
using (var db = new Model1()) { var query = db.m_kokyaku .Where(x => x.id >= 10 && x.id <= 20) .OrderByDescending(x => x.id) .ToList(); foreach (var k in query) { Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1); } } |
３．ラムダ式 パターン２
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
using (var db = new Model1()) { var query = db.m_kokyaku .Where(x => x.id >= 10 && x.id <= 20) .OrderByDescending(x => x.id) .Select(x => new { x.id, x.name, x.tel1 }); foreach (var k in query) { Console.WriteLine("id={0}, name={1}, tel1={2}", k.id, k.name, k.tel1); } } |
４．テーブル連結
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
using (var db = new Model1()) { var query = from t in db.taiou_rireki join k in db.m_kokyaku on t.id equals k.id where k.sex == "1" select new { t.id, t.taiou_bi, t.kenmei, k.name, k.corp_name }; foreach (var k in query) { Console.WriteLine("id={0}, taiou_bi={1}, kenmei={2}, name={3}, corp_name={4}" , k.id, k.taiou_bi, k.kenmei, k.name, k.corp_name); } |
５．INSERT 単一行
|
1 2 3 4 5 6 7 8 9 10 |
using (var db = new Model1()) { var sts = new m_status { id = 5, status = "打合せ" }; db.m_status.Add(sts); db.SaveChanges(); } |
６．INSERT 複数行
|
1 2 3 4 5 6 7 8 9 10 11 |
using (var db = new Model1()) { m_status[] sts = new m_status[] { new m_status {id = 6, status = "会議" }, new m_status {id = 7, status = "出張" }, }; db.m_status.AddRange(sts); db.SaveChanges(); } |
７．DELETE 単一行
|
1 2 3 4 5 6 7 8 9 |
using (var db = new Model1()) { var sts = (from s in db.m_status where s.id == 4 select s).Single(); db.m_status.Remove(sts); db.SaveChanges(); } |
８．DELETE 複数行
|
1 2 3 4 5 6 7 8 9 |
using (var db = new Model1()) { var sts = from s in db.m_status where s.id >= 5 && s.id <= 7 select s; db.m_status.RemoveRange(sts); db.SaveChanges(); } |
９．UPDATE 単一行１
|
1 2 3 4 5 6 7 8 |
using (var db = new Model1()) { var sts = db.m_status.Single(x => x.id == 3); sts.status = "保留"; db.SaveChanges(); } |
１０．UPDATE 単一行２
|
1 2 3 4 5 6 7 8 9 10 |
using (var db = new Model1()) { var sts = (from s in db.m_status where s.id == 3 select s).FirstOrDefault(); sts.status = "保留"; db.SaveChanges(); } |
１１．UPDATE 単一行３
|
1 2 3 4 5 6 7 8 9 10 11 12 |
using (var db = new Model1()) { var sts = new m_status() { id = 3, status = "保留" }; db.m_status.Attach(sts); db.Entry(sts).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } |
１２．トランザクション処理
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
using (var db = new Model1()) { ((IObjectContextAdapter)db).ObjectContext.Connection.Open(); using (var tx = new TransactionScope()) { var sts = new m_status { id = 5, status = "打合せ" }; db.m_status.Add(sts); db.SaveChanges(); m_status[] sts2 = new m_status[] { new m_status {id = 6, status = "会議" }, new m_status {id = 7, status = "出張" }, }; db.m_status.AddRange(sts2); db.SaveChanges(); tx.Complete(); } } |
１３．SQL文を直接発行
|
1 2 3 4 |
using (var db = new Model1()) { var q = db.Database.ExecuteSqlCommand("UPDATE m_status SET status = '出張' WHERE id = 7;"); } |
１４．パラメータークエリー
|
1 2 3 4 5 6 7 8 9 10 11 |
using (var db = new Model1()) { var sql = @"SELECT * FROM m_status WHERE id = @id"; var param = new SqlParameter("@id", 3); var sts = db.Database.SqlQuery<m_status>(sql, param); foreach (var st in sts) { Console.WriteLine("id={0}, status={1}", st.id, st.status); } } |
