Entity Framework with MySQL 学习笔记一(查询)

浏览数：62 / 时间：2015年06月12日

参考 : http://msdn.microsoft.com/en-us/data/jj574232.aspx

EF 查询基本上有3中

默认是 Lazy Loading

特色是只有在需要数据的时候EF才会像数据库请求，它不会使用任何inner join

比如我们有一个产品，有很多颜色，(1对多)

那么我们想把每个产品和颜色统统选出来

using (EFDB db = new EFDB())
{
    var prods = db.prods.ToList(); //像数据库请求prods, 但是不会包括colors
    foreach (var prod in prods)
    {
        var color = prod.colors; //每一次都像数据库请求颜色
    }              
}

首先必须用 ToList()，不然之后的 prod.colors是会报错的。

如果prods有很多，它会发出很多次请求,这对性能是有影响的！

Opened connection at 2014/9/27 23:26:47 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘2‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:26:47 +08:00
-- Completed in 12 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:26:47 +08:00
Opened connection at 2014/9/27 23:26:48 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘3‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:26:48 +08:00
-- Completed in 12 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:26:48 +08:00
Opened connection at 2014/9/27 23:26:49 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘4‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:26:49 +08:00
-- Completed in 11 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:26:49 +08:00

View Code

所以通常我们不是很喜欢用lazy loading

第2种呢是 Eagerly Loading

它主要是使用了 Include 方法来调用 inner join ,使查询次数减少

using (EFDB db = new EFDB())
{
    db.Configuration.LazyLoadingEnabled = false;
    var prods = db.prods.Include(p => p.colors).ToList(); //像数据库请求prods和colors
    //var prods = db.prods.Include(p => p.colors.Select(c => c.sizes)).ToList();  如果还有sizes
    foreach (var prod in prods)
    {
        var color = prod.colors; //不需要再请求了 
    }              
}

Opened connection at 2014/9/27 23:41:23 +08:00
SELECT
`Project1`.`id`, 
`Project1`.`code`, 
`Project1`.`name`, 
`Project1`.`C1`, 
`Project1`.`id1`, 
`Project1`.`color`, 
`Project1`.`prod_id`
FROM (SELECT
`Extent1`.`id`, 
`Extent1`.`code`, 
`Extent1`.`name`, 
`Extent2`.`id` AS `id1`, 
`Extent2`.`color`, 
`Extent2`.`prod_id`, 
CASE WHEN (`Extent2`.`id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`
FROM `prod` AS `Extent1` LEFT OUTER JOIN `prod_color` AS `Extent2` ON `Extent1`.`id` = `Extent2`.`prod_id`) AS `Project1`
 ORDER BY 
`Project1`.`id` ASC, 
`Project1`.`C1` ASC
-- Executing at 2014/9/27 23:41:24 +08:00
-- Completed in 14 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:41:24 +08:00

View Code

语句很吓人，子查询都出来了。我想对数据库人员来说，这查询决定是不合格的...

第3种是 Explicitly Loading

这个和 lazy loading 差不多，只是可以手动去控制

using (EFDB db = new EFDB())
{
    db.Configuration.LazyLoadingEnabled = false;
    //var prods = db.prods.Include(p => p.colors).ToList(); //像数据库请求prods和colors
    //var prods = db.prods.Include(p => p.colors.Select(c => c.sizes)).ToList();  如果还有sizes
    var prods = db.prods.ToList();
    foreach (var prod in prods)
    {                   
        var color = prod.colors; //null
        db.Entry(prod).Collection(p => p.colors).Load(); //像数据库发送请求
        //db.Entry(prod).Collection(p => p.colors).Query().Where(c => c.color == "red").Load(); //加过滤的话 
        color = prod.colors; //有了 
    }              
}

Opened connection at 2014/9/27 23:47:13 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`code`, 
`Extent1`.`name`
FROM `prod` AS `Extent1`
-- Executing at 2014/9/27 23:47:13 +08:00
-- Completed in 13 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:47:13 +08:00
Opened connection at 2014/9/27 23:47:15 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘1‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:47:15 +08:00
-- Completed in 13 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:47:15 +08:00
Opened connection at 2014/9/27 23:47:17 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘2‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:47:17 +08:00
-- Completed in 13 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:47:17 +08:00
Opened connection at 2014/9/27 23:47:17 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘3‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:47:17 +08:00
-- Completed in 13 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:47:17 +08:00
Opened connection at 2014/9/27 23:47:17 +08:00
SELECT
`Extent1`.`id`, 
`Extent1`.`color`, 
`Extent1`.`prod_id`
FROM `prod_color` AS `Extent1`
 WHERE `Extent1`.`prod_id` = @EntityKeyValue1
-- EntityKeyValue1: ‘4‘ (Type = Int32, IsNullable = false)
-- Executing at 2014/9/27 23:47:17 +08:00
-- Completed in 14 ms with result: EFMySqlDataReader

Closed connection at 2014/9/27 23:47:17 +08:00