none
Difference in using DbContext.<entity property> Vs using via DbContext.Set() method

    Question

  • Hi,

    I have come across a performance issue and it nailed down to the way the code is querying using EF.                   

    Let me use the example of users table.

    Assume i want to do a Any based on User.FirstName and User.LastName (User table has more fields)

    Method1: Context.Users.Any(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName);

    Method2: (Context.Set(typeof(User)) as IQueryable<User>).Any(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName);

    I checked in sql profiler the query fired in Method1 is:

            exec sp_executesql N'SELECT 
        CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[User] AS [Extent1]
    WHERE (((LOWER([Extent1].[FirstName])) = (LOWER(@p__linq__0))) AND ((LOWER([Extent1].[LastName])) = @p__linq__1)
        )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[User] AS [Extent2]
    WHERE (((LOWER([Extent2].[FirstName])) = (LOWER(@p__linq__0))) AND ([Extent2].[LastName] = @p__linq__1)
        )) THEN cast(0 as bit) END AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'jack',@p__linq__1=N'saw'

    From Method2:

            SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[Email] AS [Email], 
        .......other fields......
        FROM [dbo].[Users] AS [Extent1]


    The table has 40000 records and Method1 takes around 20 ms while Method2 takes around 3500 ms.

    Major issue is if the context is used in further workflow like querying other table or doing an update  (in case of Method2), it takes lot of time. (what should take 10 ms takes 1000+ ms !!)

    We use the 2 ways case by case thinking it does not make any difference. 

    If you can please confirm the behavior, as all the places its mentioned that it should have no difference either ways.

    Or am missing something ?

    Thursday, February 06, 2014 2:19 PM

All replies

  • Hi. That seems correct as in the second method the first part of the query in the parenthesis will get executed first retrieving all the records and then the Any query will be performed on the against the results locally. You could write it as below (I've skipped casting to IQueryable as DbSet implements it anyway).

    Context.Set<User>().Any(u => u.FirstName.ToLower() == userObj.FirstName && u.LastName.ToLower() == userObj.LastName);

    Thursday, February 06, 2014 2:42 PM
  • Hi JC, 

    It fires the same query and has the same behavior if i use Context.Set(typeinfo).Any() or Context.Set<Typeinfor>().Any()

    Friday, February 07, 2014 3:50 AM
  • It will fire the equivalent of the method1 query in your question. The Any method will cause the query to be executed, and the where clause will be used in the SQL statement.

    Method 2 has the first part of the query wrapped in parenthesis: (Context.Set(typeof(User)) as IQueryable<User>). This will cause the query to be executed immediately. All records in Users table are being returned and then the 'Any' query will be run against the local results. 

    You could change method 2 to this as well:

    var query = Context.Set(typeof(User)) as IQueryable<User>;
    var result = query.Any(u => u.FirstName.ToLower() == userObj.FirstName && u.LastName.ToLower() == userObj.LastName);
    

    Saturday, February 08, 2014 3:13 AM
  • Hi,

    I did some more findings. The way am making a call is i guess causing the problem to occur.

    Correction. If i execute below statememts, the query i capture in profiler are exact same:

    Method1: Context.Users.Any(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName);

    Method2: (Context.Set(typeof(User)) as IQueryable<User>).Any(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName);

    QUERY as-in profiler for both Method1 and Method2

    exec sp_executesql N'SELECT 
        CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[User] AS [Extent1]
    WHERE (((LOWER([Extent1].[FirstName])) = (LOWER(@p__linq__0))) AND ((LOWER([Extent1].[LastName])) = @p__linq__1)
        )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[User] AS [Extent2]
    WHERE (((LOWER([Extent2].[FirstName])) = (LOWER(@p__linq__0))) AND ([Extent2].[LastName] = @p__linq__1)
        )) THEN cast(0 as bit) END AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'jack',@p__linq__1=N'saw'

    But in my case when m calling Method2 i.e. Set<User>().Any( ... ), i am passing the expression to the Any() like `ValidationMethod<User>(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName)` this intern would do Set<User>().Any( .. expression passed as param.. ) 

    TestValidateUsers()
    {
      bool valid = ValidationMethod<User>(u => u.FirstName.ToLower() == userObj.FirstName &&  u.LastName.ToLower() == userObj.LastName)
    }
    
    ValidationMethod<T>(Func<T, bool> expr) where T : class
    {
               if (DbSet<T>().Any(expr))
                {
                    return false;
                }
                return true;
    }
    
    IQueryable<T> DbSet<T>() where T : class
    {
                return Context.Set(typeof(T)) as IQueryable<T>;
    }


    This fires a query to select all, like below

    QUERY as-in profiler

    SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[Email] AS [Email], 
        .......other fields......
        FROM [dbo].[Users] AS [Extent1]


    Is this a bug or is as expected ? Is there any way to make dbcontext fire a query same as in Method1 ?

    Saturday, February 22, 2014 10:51 AM
  • The second query will firstly query out all data which would take a long time.

    Wednesday, February 26, 2014 9:05 AM