Entity Framework performs too many requests

Question

I need to find individual Campaigns for a specific user. User has CodeRights, CodeRights contains codes and codes containing the 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 write this code:

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

Now, when profiling, I see that EF makes 2 SQL queries for each CodeRight present.

I also calculated the full execution time and EF takes ~ 400 ms using ADO.Net only ~ 8 .

Now my question is, if EF is really slow, or am I doing something wrong?

edits

The following two blocks are executed for each 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 

Answer 1

You have to spend time on the Fetch Plans for Entity Framework . In order for EF to complete the connection, you need to use the Include keyword.

It should be part of your initial request when you receive your user object:

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