Optimizing LINQ Any() call in Entity Framework

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.

Answer 1

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);