neurotix neurotix - 2 months ago 8
C# Question

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

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

Answer

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>?

Comments