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 joinSELECT
[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, ....})
swiss replica watches sale, combining elegant style and cutting-edge technology, a variety of styles of swiss replica omega watches sale, the pointer walks between your exclusive taste style.
ReplyDelete