EntityFrameWork使用linq to entity 提高查询效率要注意的问题

1.关于IEnumerable IQueryable IList 三者的继承关系

IQueryable 继承 IEnumerable
IList 继承 IEnumerable

 

2.ef中DbSet<T>对象提供两种扩展方法

public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);
public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

两种方法的参数分别为 表达式树 Expression 和委托 Func
当使用dbset<>对象的where方法时，传入Func，得到的是IEnumerable对象，传入Expression，得到的是IQueryable对象，这两种情况的实现却过程完全不同

使用Func作为参数，ef将表中所有数据取出存入内存中，再进行过滤
使用Expression作为参数，ef会生成带查询语句的sql进行查询

看下面的例子
我要查NorthWind数据库里的Products表里ProductId大于15的所有产品,为了分别传入Func和Expression，我们做如下封装：
//接受Func参数，返回IEnumerableprivate IEnumerable<T>
FetchData2<T>(Func<T, bool> f) where T : class
{
var context = new NorthwindEntities();
return context.Set<T>().Where(f);
}
//接受Expression<Func>参数，返回IQueryableprivate IQueryable<T>
FetchData<T>(Expression<Func<T, bool>> f) where T : class
{
var context = new NorthwindEntities();
return context.Set<T>().Where(f);
}
分别进行调用:
FetchData2<Products>(m => m.ProductID > 15).ToList();
FetchData<Products>(m => m.ProductID > 15).ToList();

利用prolifer查看执行的sql

--以Func为参数进行的查询，可见没有生成where语句SELECT
[Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS
[UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued]FROM [dbo].[Products] AS [Extent1]

--以Expression为参数进行的查询，如愿生成了where语句SELECT
[Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS
[UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued]FROM [dbo].[Products] AS [Extent1]WHERE [Extent1].[ProductID] > 15

一次加载大量数据必然会影响程序效率，我们可以将Func转Expression来进行查询

Func<Products, bool> t = m => m.ProductID > 15;
Expression<Func<Products, bool>> g = m => m.ProductID > 15;

将func直接赋值给Expression就能将Func转Expression

 

3. ef延迟执行及查询技巧

ef执行sql时候并不是在where方法后立刻执行
而是在执行first（）、count（）或者tolist（）方法时才会进行查询

ef在执行where方法过程中如果返回IEnumerable 最后在tolist（）时会执行返回IEnumerable的sql再过滤
而返回IQueryable时ef将会根据最后返回IEnumerable或者tolist（）之前的所有条件执行sql
当返回IEnumerable 时你可以使用AsQueryable方法将IEnumerable转为IQueryable接口,以便在执行sql过程中插入后面where条件,看下面例子

定义了一个方法
public override IEnumerable<T> QueryTest(Expression<Func<T, bool>> where)
{
var query = db.Set<T>().Where(where);
return query;
}
IEnumerable<User> li = _userRepository.QueryTest(a => a.Sex == 1);
该方法执行后并未执行sql，而在ToList（）后执行sql
List<User> li = _userRepository.QueryTest(a => a.Sex == 1).Where(a => a.UserName == "cb").ToList();
生成sql如下
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[UserName] AS [UserName],
[Extent1].[Password] AS [Password],
[Extent1].[Sex] AS [Sex],
[Extent1].[Email] AS [Email],
[Extent1].[Contact] AS [Contact]
FROM [dbo].[User] AS [Extent1]
WHERE 1 = [Extent1].[Sex]
List<User> li = _userRepository.QueryTest(a => a.Sex == 1).AsQueryable().Where(a => a.UserName == "cb").ToList();
生成sql如下
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[UserName] AS [UserName],
[Extent1].[Password] AS [Password],
[Extent1].[Sex] AS [Sex],
[Extent1].[Email] AS [Email],
[Extent1].[Contact] AS [Contact]
FROM [dbo].[User] AS [Extent1]
WHERE (1 = [Extent1].[Sex]) AND (N'cb' = [Extent1].[UserName])

 

转载于:https://www.cnblogs.com/bangnet/archive/2013/04/26/3043994.html