LINQ Optimization Entity Framework's Any() method

entity-framework linq performance

Question

After evaluating my Object Framework 4.0-based database layer, I discovered that a straightforward LINQ Any() I use to determine if an entity is already present in the database is the main performance bottleneck. It takes orders of magnitude longer to do the Any() check than to save the object. The database has a small number of records, and the indexed columns are being examined.

I use the following LINQ to see whether a setting group is present:

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

The following SQL is produced as a result (in addition, my SQL profiler indicates that the query was ran 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'

To remedy this issue, the only thing that comes to mind right now is writing stored procedures, although I would obviously want to retain the code in LINQ.

Is there a way to use EF to speed up such a "Exist" check?

I should probably note that in my n-tier design, I also utilize self-tracking entities. Even if certain entities already exist in the database, in some cases the status of the ChangeTracker for such things is set to "Added." For this reason, if changing the database resulted in an insert failure exception, I utilize a check to alter the ChangeTracker state appropriately.

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

Accepted Answer

Entity Framework (at least EF4) produces idiotic SQL, which is the issue. The following code seems to create respectable SQL with little difficulty.

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

}

Afterward, you can:

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

and even

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

Popular Answer

Consider adding an index by Group & Category to the database table "SettingGroups."

Does this generate equivalent SQL, by the way?

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