Entity Framework is executing too many queries

Question

I need to find distinct Campaigns of a particular user. A User has CodeRights, CodeRights contain Codes and Codes contain Campaign. Here is the CodeRight class

public class SmartCodeRight
{
        [Key, Column(Order = 1)]
        public long UserId { get; set; }
        public virtual User User { get; set; }

        [Key, Column(Order = 2)]
        public long CodeId { get; set; }
        public virtual SmartCode Code { get; set; }

        public CodeRight CodeRight { get; set; }
}

I would write the following SQL for this:

SELECT * 
FROM campaigns 
WHERE campaignid IN (SELECT DISTINCT campaignid  
                     FROM smartcodes t1 
                     INNER JOIN smartcoderights t2 ON t1.codeId = t2.codeId
                     WHERE t2.userid = @userId)

Using EF I am writing this code:

var v = user.CodeRights.Select(r => r.Code.Campaign).Distinct().ToList();

Now on profiling I am seeing that EF is executing 2 SQL queries for every CodeRight present.

I have also calculated the time of entire execution and EF takes ~400 ms while using ADO.Net it's only ~8.

Now my question is that if EF is really this slow or I'm doing something wrong?

Edits

Following two blocks are being executed for every CodeRight

exec sp_executesql N'SELECT 
[Extent1].[CodeId] AS [CodeId], 
[Extent1].[CodeTitle] AS [CodeTitle], 
[Extent1].[CodeContent] AS [CodeContent], 
[Extent1].[CreatedOn] AS [CreatedOn], 
[Extent1].[IsActive] AS [IsActive], 
[Extent1].[Deleted] AS [Deleted], 
[Extent1].[OwnerId] AS [OwnerId], 
[Extent1].[Tags] AS [Tags], 
[Extent1].[CampaignId] AS [CampaignId]
FROM [dbo].[SmartCodes] AS [Extent1]
WHERE [Extent1].[CodeId] = @EntityKeyValue1',N'@EntityKeyValue1 bigint',@EntityKeyValue1=24
go

and

exec sp_executesql N'SELECT 
[Extent1].[CampaignId] AS [CampaignId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Description] AS [Description], 
[Extent1].[AdminId] AS [AdminId]
FROM [dbo].[Campaigns] AS [Extent1]
WHERE [Extent1].[CampaignId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=12
go

Can you show the code executing the query? This question (stackoverflow.com/questions/8473705/…) indicates that EF should be generating a proper query using DISTINCT — w.brian
– w.brian, Commented Apr 23, 2013 at 15:23
The trade-off for ORM's is that they are "chatty". Most of the time the performance is "good enough". But they are chatty. But you spend less time writing custom ORM code. Pick a poison. One think you can do is see how EF issues the query and see if there is an index that is worth adding. — granadaCoder
– granadaCoder, Commented Apr 23, 2013 at 15:24
You haven't set those items up as foreign keys, not sure if that makes a difference though. — Mathew Thompson
– Mathew Thompson, Commented Apr 23, 2013 at 15:40
@mattytommo All foreign keys are properly configured. I have crosschecked the database. — Apurv Gupta
– Apurv Gupta, Commented Apr 23, 2013 at 15:42

Mathew Thompson · Accepted Answer · 2013-04-23 16:07:15Z

10

You should spend time looking at Fetch Plans for Entity Framework. In order for EF to perform the join, you'll have to use the Include keyword.

It'll have to be part of your initial query when you get your user object:

var user = context.Users
    .Include("CodeRights.Code")
    .Include("CodeRights.Campaign")
    .FirstOrD‌efault(u => u.Id == id);

edited Apr 23, 2013 at 16:07

answered Apr 23, 2013 at 15:42

Mathew Thompson

56.5k15 gold badges131 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Apurv Gupta Over a year ago

CodeRights is a collection of type CodeRight so can't have "Include" there.

Dmitry S. Over a year ago

The Include needs to be a part of the query that returns the user, something like var user = context.Users.Include("CodeRights.Code").Include("CodeRights.Campaign").FirstOrDefault(u => u.Id == id);

Mathew Thompson Over a year ago

@DmitryStarosta Good shout, didn't spot that. Updated :)

Apurv Gupta Over a year ago

Did like this

var v = db.SmartCodeRights.Where(r => r.UserId == user.UserId).Include(r => r.Code).Include(r => r.Code.Campaign).Select(r => r.Code.Campaign).Distinct().ToList();

and just a single query now. Didn't want to include in the first query as it'll unnecessarily bring data.

Collectives™ on Stack Overflow

Entity Framework is executing too many queries

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related