Entity Framework with MySQL 学习笔记一(insert,update,delete)

先说说 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:00
Started transaction at 2014/9/28 15:33:53 +08:00
SET 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: EFMySqlDataReader

INSERT 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: 1

Committed transaction at 2014/9/28 15:33:53 +08:00
Closed connection at 2014/9/28 15:33:53 +08:00
Disposed 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:00
SELECT
`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: EFMySqlDataReader

Closed connection at 2014/9/28 15:43:01 +08:00
Opened connection at 2014/9/28 15:43:02 +08:00
Started transaction at 2014/9/28 15:43:02 +08:00
DELETE FROM `prod_detail` WHERE `prod_id` = 7
-- Executing at 2014/9/28 15:43:02 +08:00
-- Completed in 13 ms with result: 1

DELETE FROM `prod` WHERE `id` = 7
-- Executing at 2014/9/28 15:43:02 +08:00
-- Completed in 12 ms with result: 1

Committed transaction at 2014/9/28 15:43:02 +08:00
Closed connection at 2014/9/28 15:43:02 +08:00
Disposed transaction at 2014/9/28 15:43:02 +08:00

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:00
SELECT
`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: EFMySqlDataReader

Closed connection at 2014/9/28 15:46:09 +08:00
Opened connection at 2014/9/28 15:46:09 +08:00
Started transaction at 2014/9/28 15:46:09 +08:00
UPDATE `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: 1

Committed transaction at 2014/9/28 15:46:09 +08:00
Closed connection at 2014/9/28 15:46:09 +08:00
Disposed 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:00
Started transaction at 2014/9/28 16:14:19 +08:00
UPDATE `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: 1

Committed transaction at 2014/9/28 16:14:19 +08:00
Closed connection at 2014/9/28 16:14:19 +08:00
Disposed transaction at 2014/9/28 16:14:19 +08:00
Opened connection at 2014/9/28 16:14:19 +08:00
Started transaction at 2014/9/28 16:14:19 +08:00
UPDATE `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: 1

Committed transaction at 2014/9/28 16:14:19 +08:00
Closed connection at 2014/9/28 16:14:19 +08:00
Disposed 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)
        {
            trans.Rollback();
        }
    }
}

Opened connection at 2014/9/28 16:16:06 +08:00
Started transaction at 2014/9/28 16:16:06 +08:00
SELECT
`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: EFMySqlDataReader

UPDATE `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: 1

Rolled back transaction at 2014/9/28 16:16:07 +08:00
Disposed transaction at 2014/9/28 16:16:07 +08:00
Closed connection at 2014/9/28 16:16:07 +08:00