Why does Entity Framework create a different sql queries for very similar code

Question

I've been recently playing around with SQL server profiler and noticed weird behaviour of generating two different queries for a code which in my opinion should works the same. Obviously I'm wrong hence the question.

Let's start from the top. I've a very simple repository class which consists of these methods:

 public virtual TEntity GetSingle(Func<TEntity, bool> where, bool asNoTracking = true, params Expression<Func<TEntity, object>>[] includedNavigationProperties)
    {
        IQueryable<TEntity> dbQuery = this.ResolveIQueryableForType<TEntity>(asNoTracking, includedNavigationProperties);
        return dbQuery.Where(where).FirstOrDefault();
    }

    public virtual IQueryable<TEntity> AsQueryable(bool asNoTracking = true, params Expression<Func<TEntity, object>>[] includedNavigationProperties)
    {
        IQueryable<TEntity> dbQuery = this.ResolveIQueryableForType<TEntity>(asNoTracking, includedNavigationProperties);

        return dbQuery;
    }

    private IQueryable<TEntityType> ResolveIQueryableForType<TEntityType>(bool asNoTracking, params Expression<Func<TEntityType, object>>[] includedNavigationProperties)
        where TEntityType : class
    {
        IQueryable<TEntityType> dbQuery = _context.Set<TEntityType>();

        // Apply eager loading
        if (includedNavigationProperties != null)
        {
            foreach (Expression<Func<TEntityType, object>> navigationProperty in includedNavigationProperties)
            {
                dbQuery = dbQuery.Include<TEntityType, object>(navigationProperty);
            }
        }

        if (asNoTracking)
        {
            return dbQuery.AsNoTracking();
        }
        else
        {
            return dbQuery;
        }
    }

Later in the application I do this call (where AccessTokenRepository is an object of my repository type):

accessToken = _repository.AccessTokenRepository.AsQueryable().Where(x => x.AccessTokenID == accessTokenId).FirstOrDefault();

which results in this query:

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[AccessTokenID] AS [AccessTokenID], 
    [Extent1].[IssuedUtc] AS [IssuedUtc], 
    [Extent1].[ExpiresUtc] AS [ExpiresUtc], 
    [Extent1].[ValidForTimeSpan] AS [ValidForTimeSpan], 
    [Extent1].[CreatedDateTime] AS [CreatedDateTime]
    FROM [dbo].[AccessToken] AS [Extent1]
    WHERE [Extent1].[AccessTokenID] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='62A1BE60-3569-4E80-BC8E-FC01B0FFC266'

But similar call (which I would say should result in same SQL):

 accessToken = _repository.AccessTokenRepository.GetSingle(x => x.AccessTokenID == accessTokenId);

results in:

SELECT 
    [Extent1].[AccessTokenID] AS [AccessTokenID], 
    [Extent1].[IssuedUtc] AS [IssuedUtc], 
    [Extent1].[ExpiresUtc] AS [ExpiresUtc], 
    [Extent1].[ValidForTimeSpan] AS [ValidForTimeSpan], 
    [Extent1].[CreatedDateTime] AS [CreatedDateTime]
    FROM [dbo].[AccessToken] AS [Extent1]

and that looks like entire table load. Could someone please explain this subtle differences in the load behaviour? Thank you

score 4 · Accepted Answer · edited May 23 '17 at 12:15

4

That's because the first parameter of your GetSingle method is defined as

Func<TEntity, bool> where

Instead of

Expression<Func<TEntity, bool>> where

And when you're passing that Func<TEntity, bool> (which is simply a generic delegate) to the Where() method, you're calling Enumerable.Where() (instead of Queryable.Where()) hence loading the entire DbSet to memory - and the SQL would not include the WHERE clause.

See What is the difference between IQueryable<T> and IEnumerable<T>?

edited May 23 '17 at 12:15

Community

1
1

answered Sep 14 '16 at 15:42

haim770

48,394
7
105
133

Thank you! Could you perhaps recommend some literature / articles which treats EF a bit deeper than just 'crud' so I could avoid similar problems in future? – mike Sep 14 '16 at 16:11
1

Strictly speaking, the `Func / Expression` distinction is more of a LINQ issue than an EF issue (anytime you don't pass an Expression, the LINQ provider - EF in your case - wouldn't be able to interpret it and the only overloads that will match are those in the `Enumerable` class). One more thing that is worth mentioning (and *is* specific to EF) is that EF doesn't support `Queryable` Navigation Properties, that means each time you append `Where()` (or any other LINQ method) to it, it will fetch the entire data from the DB, then would apply the method using Linq to Objects (`Enumerable`) – haim770 Sep 14 '16 at 16:51

score 2 · Answer 2 · answered Sep 14 '16 at 15:41

Your GetSingle method takes Func<> as an argument, which forces the IQueryable<> to be cast to IEnumerable<>, causing the query to be "finished" (query executed will have a form dictated by an expression created until the collection was down-cast) and every subsequent operation to be performed in-memory. You have to use Expression<Func<>> so the engine can properly analyze the expression tree and generate correct query.

Why does Entity Framework create a different sql queries for very similar code

2 Answers2