Clean code trap for Entity framework

When writing a code, it is pretty normal to extract a part of your code to a new method in order to have a cleaner and human readable code.
Take this example:

PostTable (1) ===> (n) Votes


You have a post table that contains a lot of columns and you also like to retrieve total number of votes for that post.
A simple query would be:

db.posts.where(....).Select(Convert)

public static Dto Convert(Post p)
{
 return new Dto{
  Title= p.Title, 
  ...
  Other fields
  ....
  VoteCount= p.Votes.Count
 }
}

Simple. Right?
But then your query will be translated as a query to take Post object and 1 query for each of the posts to read its votes and it is not the worst part! it will fetch all the votes instead of counting them:

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[PostId] AS [PostId], 
    FROM [dbo].[Votes] AS [Extent1]
    WHERE [Extent1].[PostId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=6

Then you might say, you can Include your vote table in your query. Like this:

 db.posts
.Include(p=>p.Votes).where(....).Select(Convert)

But then it will be translated into a big join

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Title] AS [Title], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[Pid] AS [Pid], 
    [Project1].[PostId] AS [PostId], 
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Title] AS [Title], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[PostId] AS [PostId], 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Posts] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Votes] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PostId]
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

So what is wrong?!!


As you may already have guessed it. Do not put the select into a separated method. call your query simply like this:

db.posts.Where(....).Select(p=>
new PostlDto{Title= p.Title, VoteCount= p.Votes.Count, ....})