参考 : http://msdn.microsoft.com/en-us/data/jj574232.aspx
EF 查询基本上有3中
默认是 Lazy Loading
特色是只有在需要数据的时候EF才会像数据库请求,它不会使用任何inner join
比如我们有一个产品,有很多颜色,(1对多)
那么我们想把每个产品和颜色统统选出来
using (EFDB db = newEFDB())
{var prods = db.prods.ToList(); //像数据库请求prods, 但是不会包括colors
foreach (var prod inprods)
{var color = prod.colors; //每一次都像数据库请求颜色
}
}
首先必须用 ToList(),不然之后的 prod.colors是会报错的。
如果prods有很多,它会发出很多次请求,这对性能是有影响的!
Opened connection at 2014/9/27 23:26:47 +08:00SELECT
`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 12ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:26:47 +08:00Opened connection at2014/9/27 23:26:48 +08:00SELECT
`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 12ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:26:48 +08:00Opened connection at2014/9/27 23:26:49 +08:00SELECT
`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 11ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:26:49 +08:00
View Code
所以通常我们不是很喜欢用lazy loading
第2种呢是 Eagerly Loading
它主要是使用了 Include 方法来调用 inner join ,使查询次数减少
using (EFDB db = newEFDB())
{
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 inprods)
{var color = prod.colors; //不需要再请求了
}
}
Opened connection at 2014/9/27 23:41:23 +08:00SELECT
`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 14ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:41:24 +08:00
View Code
语句很吓人,子查询都出来了。我想对数据库人员来说,这查询决定是不合格的...
这里有一件事要特别注意 :
当我们使用多层多个坎套 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 = newEFDB())
{
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 inprods)
{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:00SELECT
`Extent1`.`id`,
`Extent1`.`code`,
`Extent1`.`name`
FROM `prod` AS `Extent1`-- Executing at 2014/9/27 23:47:13 +08:00
-- Completed in 13ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:47:13 +08:00Opened connection at2014/9/27 23:47:15 +08:00SELECT
`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 13ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:47:15 +08:00Opened connection at2014/9/27 23:47:17 +08:00SELECT
`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 13ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:47:17 +08:00Opened connection at2014/9/27 23:47:17 +08:00SELECT
`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 13ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:47:17 +08:00Opened connection at2014/9/27 23:47:17 +08:00SELECT
`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 14ms with result: EFMySqlDataReader
Closed connection at2014/9/27 23:47:17 +08:00
View Code
原始方法调用 select
using (DB db = newDB())
{
List colors = db.Database.SqlQuery("select * from color where id in ({0})",10).ToList();
}
也是用了很多的查询...
目前我还没有找到比较可以接受的查询方式。至少我觉得对性能有点要求的人应该不会使用上面任何一种方法吧..
继续专研...待续