LINQ Optimization Entity Framework's Any() method

entity-framework linq performance

Question

After profiling my Entity Framework 4.0 based database layer I have found the major performance sinner to be a simple LINQ Any() I use to check if an entity is already existing in the database. The Any() check performs orders of magnitude slower than saving the entity. There are relatively few rows in the database and the columns being checked are indexed.

I use the following LINQ to check for the existence of a setting group:

from sg in context.SettingGroups
where sg.Group.Equals(settingGroup) && sg.Category.Equals(settingCategory)
select sg).Any()

This generates the following SQL (additionally my SQL profiler claims the query is executed twice):

exec sp_executesql N'SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[SettingGroups] AS [Extent1]
    WHERE ([Extent1].[Group] = @p__linq__0) AND ([Extent1].[Category] = @p__linq__1)
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[SettingGroups] AS [Extent2]
    WHERE ([Extent2].[Group] = @p__linq__0) AND ([Extent2].[Category] = @p__linq__1)
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'Cleanup',@p__linq__1=N'Mediator'

Right now I can only think of creating stored procedures to solve this problem, but I would of course prefer to keep the code in LINQ.

Is there a way to make such an "Exist" check run faster with EF?

I should probably mention that I also use self-tracking-entities in an n-tier architecture. In some scenarios the ChangeTracker state for some entities is set to "Added" even though they already exist in the database. This is why I use a check to change the ChangeTracker state accordingly if updating the database caused an insert failure exception.

1
8
1/18/2011 10:16:23 AM

Accepted Answer

The problem is Entity Framework (at least EF4) is generating stupid SQL. The following code seems to generate decent SQL with minimal pain.

public static class LinqExt 
{
    public static bool BetterAny<T>( this IQueryable<T> queryable, Expression<Func<T, bool>> predicate)
    {
        return queryable.Where(predicate).Select(x => (int?)1).FirstOrDefault().HasValue;
    }

    public static bool BetterAny<T>( this IQueryable<T> queryable)
    {
        return queryable.Select(x => (int?)1).FirstOrDefault().HasValue;
    }

}

Then you can do:

(from sg in context.SettingGroups
where sg.Group.Equals(settingGroup) && sg.Category.Equals(settingCategory)
select sg).BetterAny()

or even:

context.SettingGroups.BetterAny(sg => sg.Group.Equals(settingGroup) && sg.Category.Equals(settingCategory));
0
7/26/2012 10:06:01 PM

Popular Answer

Try adding index to the database table "SettingGroups", by Group & Category.

BTW, does this produce similar sql?

var ok = context.SettingGroups.Any(sg => sg.Group==settingGroup && sg.Category==settingCategory);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow