//代码功能:根据ItemModelID查询PD_ItemParameter表的数据,该表大概9万条数据
List<string> idList = itemModelList.Select(c =>c.ID).ToList();
var paramterEntityList = new BaseService<pd_itemparameterentity>().GetList(c =>idList.Contains(c.ItemModelID));
BaseService<pd_itemparameterentity>().GetList方法的底层核心代码
///
/// 查询列表根据表达式
///
/// <typeparam< span=""> name="T">类型
/// <param< span=""> name="condition">表达式
///
publicIEnumerable FindList<T>(Expression<func<t, <="" span="">bool>> condition) where T : class,new()
{
var enumerableData = dbcontext.Set().Where(condition.Compile());
if(enumerableData == null)
{
return new List();
}
return enumerableData.ToList();
}
执行完成GetList方法大概需要2.5秒,使用正常的sql实现相同的功能,执行时间大概0.1秒。究竟哪里导致了速度这么慢?
通过SQL Server Profilter 监测 EF 生成的 SQL
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ItemModelID] AS [ItemModelID],
[Extent1].[FieldCode] AS [FieldCode],
[Extent1].[FieldName] AS [FieldName],
[Extent1].[FieldValuesDecimal] AS [FieldValuesDecimal],
[Extent1].[FieldValuesString] AS [FieldValuesString],
[Extent1].[FieldValuesType] AS [FieldValuesType],
[Extent1].[LanguageType] AS [LanguageType],
[Extent1].[ISDELETE] AS [ISDELETE]
FROM [dbo].[PD_ItemParameter] AS [Extent1]
该 SQL 并没有携带查询条件。后面查看 Where 方法,发现该方法居然是 IEnumerable 的扩展方法,而不是 IQueryable 的扩展方法,这就不难理解生成的 SQL 没有携带查询条件。使用 IEnumerable 的 Where 就是 EF 查询所有的数据再过滤处理。

解决办法:dbcontext.Set().Where(condition),不要将 condition 表达式编译,从而使用 IQueryable 的扩展方法

通过SQL Server Profilter 监测 EF 生成的 SQL ,该SQL携带了查询条件,查询速度也在正常范围了。
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ItemModelID] AS [ItemModelID],
[Extent1].[FieldCode] AS [FieldCode],
[Extent1].[FieldName] AS [FieldName],
[Extent1].[FieldValuesDecimal] AS [FieldValuesDecimal],
[Extent1].[FieldValuesString] AS [FieldValuesString],
[Extent1].[FieldValuesType] AS [FieldValuesType],
[Extent1].[LanguageType] AS [LanguageType],
[Extent1].[ISDELETE] AS [ISDELETE]
FROM [dbo].[PD_ItemParameter] AS [Extent1]
WHERE ([Extent1].[ItemModelID] IN (N'M00000002738', N'M00000002739')) AND ([Extent1].[ItemModelID] IS NOT NULL) AND (0 = [Extent1].[ISDELETE])
后记:
使用EF/EFCore中where的正确姿势:应是Expression<func<t, bool="">> predicate作为表达式,而不是Func<t, bool=""> predicate。