先说说 insert 吧.
当EF执行insert时,如果我们传入的对象是有关联(1对多等)的话,它会执行多个语句 insert到多个表,
并且再select出来填充我们的属性(因为有些column默认值是sql设定的,比如id等,我们insert后要有最新的数据丫).
using (EFDB db = new EFDB()){ db.prods.Add(new Prod { code = "mk100", name = "name", detail = new ProdDetail //1对1关系当 prod_detail table { fullDescription = "des" } }); db.SaveChanges();}
Opened connection at 2014/9/28 15:33:53 +08:00Started transaction at 2014/9/28 15:33:53 +08:00SET SESSION sql_mode='ANSI';INSERT INTO `prod`(`code`, `name`) VALUES (@gp1, @gp2);SELECT`id`FROM `prod` WHERE row_count() > 0 AND `id`=last_insert_id()-- @gp1: 'mk100' (Type = String, IsNullable = false, Size = 5)-- @gp2: 'name' (Type = String, IsNullable = false, Size = 4)-- Executing at 2014/9/28 15:33:53 +08:00-- Completed in 13 ms with result: EFMySqlDataReaderINSERT INTO `prod_detail`(`prod_id`, `id`, `fullDescription`) VALUES (7, 0, @gp1)-- @gp1: 'des' (Type = String, IsNullable = false, Size = 3)-- Executing at 2014/9/28 15:33:53 +08:00-- Completed in 15 ms with result: 1Committed transaction at 2014/9/28 15:33:53 +08:00Closed connection at 2014/9/28 15:33:53 +08:00Disposed transaction at 2014/9/28 15:33:53 +08:00
delete 的话也有类似的情况,如果是关联的话,它也会执行多个语句去删除多个表,delete必须先把要洗掉的行select出来(这个似乎不太好...)
如果关联的数据是null,就不会去delete了,比如下面没有使用Include的话(虽然pk是有的,但是detail对象是Null)
WillCascadeOnDelete - Enabling Cascade Delete (级联删除) <-- 目前不清楚这样设置
using (EFDB db = new EFDB()){ //db.Configuration.LazyLoadingEnabled = false; db.prods.RemoveRange(db.prods.Where(p => p.id == 7).Include(p => p.detail)); //用include把关联select出来 db.SaveChanges();}
Opened connection at 2014/9/28 15:43:01 +08:00SELECT`Extent1`.`id`, `Extent1`.`code`, `Extent1`.`name`, `Extent2`.`prod_id`, `Extent2`.`id` AS `id1`, `Extent2`.`fullDescription`FROM `prod` AS `Extent1` LEFT OUTER JOIN `prod_detail` AS `Extent2` ON `Extent1`.`id` = `Extent2`.`prod_id` WHERE 7 = `Extent1`.`id`-- Executing at 2014/9/28 15:43:01 +08:00-- Completed in 14 ms with result: EFMySqlDataReaderClosed connection at 2014/9/28 15:43:01 +08:00Opened connection at 2014/9/28 15:43:02 +08:00Started transaction at 2014/9/28 15:43:02 +08:00DELETE FROM `prod_detail` WHERE `prod_id` = 7-- Executing at 2014/9/28 15:43:02 +08:00-- Completed in 13 ms with result: 1DELETE FROM `prod` WHERE `id` = 7-- Executing at 2014/9/28 15:43:02 +08:00-- Completed in 12 ms with result: 1Committed transaction at 2014/9/28 15:43:02 +08:00Closed connection at 2014/9/28 15:43:02 +08:00Disposed transaction at 2014/9/28 15:43:02 +08:00
如果想删除 n-n 关系的rows
Room room = db.rooms.Where(r => r.id == 6).Include(r => r.imgs).Include(r => r.facilities).FirstOrDefault(); var facility = room.facilities.First(); //db.Entry(facility).State = EntityState.Deleted; 这样不行哦! room.facilities.Remove(facility); //关键 : 要这样! db.SaveChanges();
update 也是先select出来,然后EF会再saveChange时对比之前的值然后生成语句update
using (EFDB db = new EFDB()){ //db.Configuration.LazyLoadingEnabled = false; var prod = db.prods.Where(p => p.id == 3).FirstOrDefault(); prod.name = "change"; db.SaveChanges();}
Opened connection at 2014/9/28 15:46:09 +08:00SELECT`Extent1`.`id`, `Extent1`.`code`, `Extent1`.`name`FROM `prod` AS `Extent1` WHERE 3 = `Extent1`.`id` LIMIT 1-- Executing at 2014/9/28 15:46:09 +08:00-- Completed in 11 ms with result: EFMySqlDataReaderClosed connection at 2014/9/28 15:46:09 +08:00Opened connection at 2014/9/28 15:46:09 +08:00Started transaction at 2014/9/28 15:46:09 +08:00UPDATE `prod` SET `name`=@gp1 WHERE `id` = 3-- @gp1: 'change' (Type = String, IsNullable = false, Size = 6)-- Executing at 2014/9/28 15:46:09 +08:00-- Completed in 11 ms with result: 1Committed transaction at 2014/9/28 15:46:09 +08:00Closed connection at 2014/9/28 15:46:09 +08:00Disposed transaction at 2014/9/28 15:46:09 +08:00
EF 6 默认情况会使用 Transaction 的.
比如当我们对实体做了很多修改,最后调用saveChange().EF会开启Transaction然后执行多个语句,如果其中一个语句有问题,就会rollBack
需要注意的是,每一次saveChange都会开启和关闭一次 tansaction ,所以如果你调用多次的话,自己要搞清楚
using (EFDB db = new EFDB()){ //db.Configuration.LazyLoadingEnabled = false; var prod = db.prods.Where(p => p.id == 3).Include(p => p.detail).FirstOrDefault(); prod.name = "change22"; db.SaveChanges(); prod.detail.fullDescription = "zzz"; db.SaveChanges();}
Opened connection at 2014/9/28 16:14:19 +08:00Started transaction at 2014/9/28 16:14:19 +08:00UPDATE `prod` SET `name`=@gp1 WHERE `id` = 3-- @gp1: 'change22' (Type = String, IsNullable = false, Size = 8)-- Executing at 2014/9/28 16:14:19 +08:00-- Completed in 13 ms with result: 1Committed transaction at 2014/9/28 16:14:19 +08:00Closed connection at 2014/9/28 16:14:19 +08:00Disposed transaction at 2014/9/28 16:14:19 +08:00Opened connection at 2014/9/28 16:14:19 +08:00Started transaction at 2014/9/28 16:14:19 +08:00UPDATE `prod_detail` SET `fullDescription`=@gp1 WHERE `prod_id` = 3-- @gp1: 'zzz' (Type = String, IsNullable = false, Size = 3)-- Executing at 2014/9/28 16:14:19 +08:00-- Completed in 12 ms with result: 1Committed transaction at 2014/9/28 16:14:19 +08:00Closed connection at 2014/9/28 16:14:19 +08:00Disposed transaction at 2014/9/28 16:14:19 +08:00
还有一种方式是,让我们自己来控制
using (EFDB db = new EFDB()){ using (var trans = db.Database.BeginTransaction()) { try { //db.Configuration.LazyLoadingEnabled = false; var prod = db.prods.Where(p => p.id == 3).Include(p => p.detail).FirstOrDefault(); prod.name = "xx1234"; db.SaveChanges(); prod.detail.fullDescription = "123"; throw new Exception("x"); //即使这里有问题,上一个saveChange也能rollBack db.SaveChanges(); trans.Commit(); } catch (Exception) { if(trans.UnderlyingTransaction.Connection != null) trans.Rollback(); //检查确保还没有被 rocllback 或者 commit rollback 2 次是会error的 } }}
Opened connection at 2014/9/28 16:16:06 +08:00Started transaction at 2014/9/28 16:16:06 +08:00SELECT`Extent1`.`id`, `Extent1`.`code`, `Extent1`.`name`, `Extent2`.`prod_id`, `Extent2`.`id` AS `id1`, `Extent2`.`fullDescription`FROM `prod` AS `Extent1` LEFT OUTER JOIN `prod_detail` AS `Extent2` ON `Extent1`.`id` = `Extent2`.`prod_id` WHERE 3 = `Extent1`.`id` LIMIT 1-- Executing at 2014/9/28 16:16:06 +08:00-- Completed in 12 ms with result: EFMySqlDataReaderUPDATE `prod` SET `name`=@gp1 WHERE `id` = 3-- @gp1: 'xx12234' (Type = String, IsNullable = false, Size = 7)-- Executing at 2014/9/28 16:16:07 +08:00-- Completed in 11 ms with result: 1Rolled back transaction at 2014/9/28 16:16:07 +08:00Disposed transaction at 2014/9/28 16:16:07 +08:00Closed connection at 2014/9/28 16:16:07 +08:00
这里补个优化删除,如果只是要删除一条row,而且知道这个row的id,那么就可以这样.
要注意如果sql被改过,比如这id已经被删除,会报错哦。
var address = new Address { addressId = 9 }; //放id db.addresss.Attach(address); //用Attach db.addresss.Remove(address); //remove db.SaveChanges();
删除时我们也可以直接发SQL COMMAND, 因为delete语句比较通俗规范副作用比较少,不然EF不建议我们自己写Command的.
using (DB db = new DB()){ int x = db.Database.ExecuteSqlCommand("delete from daily_room where id ={0}", 21);}
继承类删除 : db.parents.remove(child | parent); 这样parent 和 child 都会一起被删除的.
db.SaveChanges()是通过肮数据监测实现的,一个实体有3个值 : database value, original value, current value 有几种state : detached = 1 //如果obj并非是under entity的,那么将是detached状态 unchanged = 2 //default added = 4 //if don havedatabase value deleted = 8 //if don have current value modified = 16 //if current value != original base on 不同状态 (下面是我自己的经验,没有参考过源码) 当一个实体是detached时代表它是普通对象(可能是从json转换来的), 这里普通对象的意思是它没有进入 entity 的作用域内 (不要以为一个class有[key],[foreign key])标签 new 出来就是entity对象哦) 我们可以通过 attach 把一个普通对象添加进entity 作用域内 这个attach其实就调用了 db.Entry(p).State = EntityState.Unchanged; 对一个detached设置state时,不同的state是不同的. set to add , 子层的state也是add (但是如果你先set to unchanged ,在set to add , 子层将会是 unchanged哦) set to unchanged , 子层也是unchanged set to modified , 子层是 unchanged set to deleted , 子层的值会被删除 如果entity set to modified , 它的所有属性的都会set to isModified 如果其中一个attr set to isModified , entity state 会变成 modified 要set attr 的 ismodified ,实体必须在作用域先。 所以通常做法是 : 一个obj set to modified. 子层不用管(除非你要更新等), 哪一些不要update的,set attr ismodified to false 在 attach 时,如果 foreign id 和 子层obj id 不 match ,会报错! 在 saveChanges 时,foreign id 和 子层 obj id 不match 是不会报错的哦. p.s attach时也是要注意, EF作用域内不可以有相同 Key 的实体哦 如果attach之后,我们必须自己来控制好外键对象和外键值,EF会用跟踪改变来判断最后使用外键值还是外键对象的id来更新数据库,最后在match它们。 obj id 优先于 foreign id 哦 一个错误的例子是 attach之后,我们先修改外键对象去一个(id =5 )的对象,然后我们在修改外键值去 (id = 6), EF会用6作为更新,然后在把外键对象变成 null. 所以不要同时修改2个,很危险. 在做项目的时候,想要替换外键,就不要把外键对象传上来,不然就error了.除非你能一起传新的外键对象。
var obj = new SingleTable { data = "12345", id = 1 }; db.SingleTables.Attach(obj); //attach 进entity上下文 db.Entry(obj).State = System.Data.Entity.EntityState.Modified; db.Entry(obj).Property("data").IsModified = true; //如果只是想修改一些属性罢了的话 db.Entry(obj).Property(o => o.data).IsModified = true; db.saveChanges();
//顺便提一下 : Entry 还可以控制 database value, original value, current value哦 //db.Entry(obj).CurrentValues.SetValues("x"); //db.Entry(obj).OriginalValues.SetValues("x");
db.Entry(obj).State = System.Data.Entity.EntityState.Modified;
(一次过把所有属性的 isModified set to true (不包括层级对象) , 之后我们也可以单独 set 某些属性回到 false 如果不希望修改)
更新 by detached object
大多时候我们的对象是来自前台的json
一个不再entity context 内的对象,我们称为 detached object (即你用entry会得到state是detached)
using (EFDB db = new EFDB()) { SingleTable sqlObject = db.SingleTables.Find(detachObject.id); //先从sql获取原本的数据 if (sqlObject == null) //如果没有的话 maybe insert or 不处理 { db.SingleTables.Add(detachObject); } else { //透过 entry 设置一个新的value对象进去,那么它在save的时候会dirty check, 如果有modified的它会更新. var entry = db.Entry(sqlObject); //修改sqlObject的current values entry.CurrentValues.SetValues(detachObject); //它会dirty. 如果对象中有 array 忽略(skip,当作没看到,完全不处理), object会=null,but complex is ok } db.SaveChanges(); }
补充insert:
var employer = db.employers.FirstOrDefault(ee => ee.id == 22);var company = new Company{ name = "22", addressComplex = new addressComplex() //不需要写 foreign id or obj 没关系};employer.companys = new List//也不需要include出来{ company}; await db.mySaveChangesAsync(); //不需要使用db.companys.add, 最后依然可以insert进去sql
可以看得出来,EF的检查还是挺好用的。
补充 update :
//get and update var FAQSQL = await db.FAQs.FindAsync(id);db.Entry(FAQSQL).CurrentValues.SetValues(FAQ);db.Entry(FAQSQL).OriginalValues["rowVersion"] = FAQ.rowVersion;//direct update FAQ.id = id;db.Entry(FAQ).State = EntityState.Modified;
get and update 会导致请求多一次,一般情况下是不推荐使用的,但是很多时候由于要兼顾权限管理,我们依然必须使用这个方式,而在使用SetValues的时候rowVersion是不会被更新的,所以我们必须要自己写入
rowVersion 来防止并发。
direct update 就不需要担心并发问题了,但是对权限管理就不行了。
补充 foreign key
如果 foreign obj.id 和 foreign id 不同,saveChange 会用foreign obj.id 然后foreign id 会被刷新掉
更新 2015-11-07:
常用 :
DbContextTransaction trans = null;trans = DB.Sharing.beginTransaction();DB.Sharing.commitTransaction(trans);DB.Sharing.rollBackTansaction();