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

Community · Accepted Answer · 2017-05-23 12:15:00Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Sep 14, 2016 at 15:42

haim770

49.2k7 gold badges113 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mike Over a year ago

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?

haim770 Over a year ago

Strictly speaking, the Func / Expression<Func> 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)

kiziu · Accepted Answer · 2016-09-14 15:41:26Z

2

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.

answered Sep 14, 2016 at 15:41

kiziu

1,1301 gold badge12 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related