CODEWITHSUNDEEP
c#sql.netentity-frameworkentity-framework-6
Toben
Toben

Reputation: 111

Big execution time for a simple request with Entity Framework

I have a weird behaviour from Entity Framework 6. I have a simple (a simple where and a simple select) query which takes 30s.

I used Sql Profiler to watch what sql code is executed. I am using a Where then the FirstOrDefault method to get an item. Then I tried another query, I did a ToList (to fetch data) then FirstOrDefault and it takes less than 1 second.

Original code (takes 30s to be executed):
-----------------------------------------

id = Container.SocialNetworks.Where(a => a.SocialNetwork == EnumSocialNetwork.LinkedIn && a.Link == linkedinurl && a.User.TenantID == Container.TenantId).Select(i => i.UserID).FirstOrDefault();

From SQL Profiler :
-------------------

exec sp_executesql N'SELECT 
    [Limit1].[UserID] AS [UserID]
    FROM ( SELECT TOP (1) 
        [Extent1].[UserID] AS [UserID]
        FROM  [dbo].[SocialNetworks] AS [Extent1]
        INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[ID]
        WHERE (0 = [Extent1].[SocialNetwork]) AND (([Extent1].[Link] = @p__linq__0) OR (([Extent1].[Link] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent2].[TenantID] = @p__linq__1)
    )  AS [Limit1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 int',@p__linq__0=N'linkedin.com/in/a-profile',@p__linq__1=5

After testing another solutions (takes less than 1s):
-----------------------------------------------------

id = Container.SocialNetworks.Where(a => a.SocialNetwork == EnumSocialNetwork.LinkedIn && a.Link == linkedinurl && a.User.TenantID == Container.TenantId).Select(i => i.UserID).ToList().FirstOrDefault();

From SQL Profiler:
------------------

exec sp_executesql N'SELECT 
    [Extent1].[UserID] AS [UserID]
    FROM  [dbo].[SocialNetworks] AS [Extent1]
    INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[ID]
    WHERE (0 = [Extent1].[SocialNetwork]) AND (([Extent1].[Link] = @p__linq__0) OR (([Extent1].[Link] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent2].[TenantID] = @p__linq__1)',N'@p__linq__0 nvarchar(4000),@p__linq__1 int',@p__linq__0=N'linkedin.com/in/a-profile-as',@p__linq__1=5

As you can see, I use ToList to fetch data before filtering with FirstOrDefault. And, normally, it is not advisable to do a ToList, a eager load. Why Entity Framework put a select into a select when I use FirstOrDefault ?

I am sorry for my english and I hope I explained properly my issue.

EDIT :

I have something interesting to add, when the "linkedinurl" value does not exist, and only when it does not exist, in the database, both queries take less than 1 second.

EDIT 2:

After writing a comment, I would like to add that our database is on Azure. And the problem does not appear on a simple SQLEXPRESS database. Moreover, this issue appeared like 4 or 5 days ago.

Upvotes: 3

Views: 339

Answers (1)

Antoine Pelletier
Antoine Pelletier

Reputation: 3326

That is because you use FirstOrDefault AFTER the where().Select() combination.

The first query would work better like this :

id = Container.SocialNetworks.FirstOrDefault(a => a.SocialNetwork == EnumSocialNetwork.LinkedIn && a.Link == linkedinurl && a.User.TenantID == Container.TenantId)?.UserID;

As you can see, I use FirstOrDefault just like you used your Where, but this will load the entire object, as discussed in the comments.

Why is your seconde query faster ? Because you ended the query with a ToList() so the FirstOrDefault part apply only in your c# code, AFTER the lines have been loaded, not on the DB with a double select.

Edit :

Trying these 2 lines might highlight the root cause better :

1. Try to order your set :

id = Container.SocialNetworks
   .Where(a => a.SocialNetwork == EnumSocialNetwork.LinkedIn && a.Link == linkedinurl && a.User.TenantID == Container.TenantId)
   .OrderBy(t => t.UserID).Select(i => i.UserID).FirstOrDefault();

2. Use an aggregate function :

id = Container.SocialNetworks
    .Where(a => a.SocialNetwork == EnumSocialNetwork.LinkedIn && a.Link == linkedinurl && a.User.TenantID == Container.TenantId)
    .Min(i => i.UserID);

Upvotes: 2

Related Questions