首页 > 代码库 > Entity Framework with MySQL 学习笔记一(查询)
Entity Framework with MySQL 学习笔记一(查询)
参考 : 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: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 12 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:26:47 +08:00Opened connection at 2014/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 12 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:26:48 +08:00Opened connection at 2014/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 11 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:26:49 +08:00
所以通常我们不是很喜欢用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: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 14 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:41:24 +08:00
语句很吓人，子查询都出来了。我想对数据库人员来说，这查询决定是不合格的...
第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:00SELECT`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: EFMySqlDataReaderClosed connection at 2014/9/27 23:47:13 +08:00Opened connection at 2014/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 13 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:47:15 +08:00Opened connection at 2014/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 13 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:47:17 +08:00Opened connection at 2014/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 13 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:47:17 +08:00Opened connection at 2014/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 14 ms with result: EFMySqlDataReaderClosed connection at 2014/9/27 23:47:17 +08:00
也是用了很多的查询...
目前我还没有找到比较可以接受的查询方式。至少我觉得对性能有点要求的人应该不会使用上面任何一种方法吧..
继续专研...待续
Entity Framework with MySQL 学习笔记一(查询)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。