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

时间：2022-09-23 15:54:50

参考 : 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 // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

所以通常我们不是很喜欢用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 // :: +:

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 () 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 // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

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

这里有一件事要特别注意 :

当我们使用多层多个坎套 include 的时候

var zz = db.dailyRooms.Include("offers").Include("room.imgs").Include("room.facilities").ToList();

var zzz = db.dailyRooms.Include("room.imgs").Include("room.facilities").Include("offers").ToList();

这2行代码只是include的顺序调换了，但是第一句会error，第2句可以执行，这里遇到的error是 TINYINT 无法被转换成 bool

我没有研究它生产的语句，但是我大概猜是因为在多个坎套的情况语句会影响类型，导致无法正常转换 TINYINT 可能变成string了。

推出解决方案是尽可能先include自己属性，才去坎套

有机会我才深入理解一下(清楚的人请在这指点，感激！)

第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 // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`code`,

`Extent1`.`name`

FROM `prod` AS `Extent1`

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

Opened connection at // :: +:

SELECT

`Extent1`.`id`,

`Extent1`.`color`,

`Extent1`.`prod_id`

FROM `prod_color` AS `Extent1`

 WHERE `Extent1`.`prod_id` = @EntityKeyValue1

-- EntityKeyValue1: '' (Type = Int32, IsNullable = false)

-- Executing at // :: +:

-- Completed in  ms with result: EFMySqlDataReader

Closed connection at // :: +:

原始方法调用 select

using (DB db = new DB())

{

    List<Color> colors = db.Database.SqlQuery<Color>("select * from color where id in ({0})",).ToList();

}

也是用了很多的查询...

目前我还没有找到比较可以接受的查询方式。至少我觉得对性能有点要求的人应该不会使用上面任何一种方法吧..

继续专研...待续

相关文章