Entity Framework 第四篇 优化SQL查询

  public IList<TEntity> Search<TEntity>(Expression<Func<TEntity, bool>> predicate = null) where TEntity : class
        {
            if (predicate == null)
            {
                return dbContext.Set<TEntity>().ToList();
            }
            else
            {
                return dbContext.Set<TEntity>().Where(predicate).ToList();
            }

        }

本人通过跟踪sql语句得知

predicate如果为委托类型的时候，dbContext.Set<TEntity>().Where(predicate).ToList();对应的sql语句是

SELECT 
    [Extent1].[UserID] AS [UserID], 
    [Extent1].[LoginName] AS [LoginName], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[UserName] AS [UserName], 
    [Extent1].[UserType] AS [UserType], 
    [Extent1].[Tel] AS [Tel], 
    [Extent1].[CreateDate] AS [CreateDate], 
    [Extent1].[LoginNum] AS [LoginNum], 
    [Extent1].[LastDate] AS [LastDate], 
    [Extent1].[ShopID] AS [ShopID]
    FROM [dbo].[S_Users] AS [Extent1]
   

但是当predicate如果为表达式树的时候，语句如下，

SELECT 
    [Extent1].[UserID] AS [UserID], 
    [Extent1].[LoginName] AS [LoginName], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[UserName] AS [UserName], 
    [Extent1].[UserType] AS [UserType], 
    [Extent1].[Tel] AS [Tel], 
    [Extent1].[CreateDate] AS [CreateDate], 
    [Extent1].[LoginNum] AS [LoginNum], 
    [Extent1].[LastDate] AS [LastDate], 
    [Extent1].[ShopID] AS [ShopID]
    FROM [dbo].[S_Users] AS [Extent1]
    WHERE N‘liuyangh‘ = [Extent1].[LoginName]

所以我们尽量用表达式树传参，而不是单纯地lambda表达式或者委托类型。

为什么会这样呢？

因为dbContext.Set<TEntity>().Where(predicate)，当是表达式树的时候返回的是IQueryable<TSource>

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

  如果是委托类型返回的是IEnumerable<TSource>
public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

Entity Framework 第四篇 优化SQL查询

标签：