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

Answer 1

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