For the examples of code, we are going to use the following entities:
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public Blog Blog { get; set; }
public List<Comment> Comments { get; set; }
}
public class Comment
{
public int Id { get; set; }
public string Content { get; set; }
public Post Post { get; set; }
}
And DTOs:
public class PostDto
{
public int Id { get; set; }
public string Title { get; set; }
public int BlogId { get; set; }
public string BlogName { get; set; }
public List<CommentDto> CommentsDto { get; set; }
}
public class CommentDto
{
public int Id { get; set; }
public string Content { get; set; }
}
public class PostSummaryDto
{
public int Id { get; set; }
public string Title { get; set; }
public int CommentsCount { get; set; }
}
When querying data with Entity Framework is very common to use the Include method. But that can have two drawbacks: you will query fields that probably you don't need, and the second one maybe you will need to map your entities to another model or DTO.
var posts = await dbContext.Posts
.Include(x => x.Blog)
.Include(x => x.Comments)
.ToListAsync();
// map to DTO
For this query Entity Framework will generate the following SQL code:
SELECT [p].[Id], [p].[BlogId], [p].[Title], [b].[Id], [b].[Name],
[c].[Id], [c].[Content], [c].[PostId]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
LEFT JOIN [Comments] AS [c] ON [p].[Id] = [c].[PostId]
ORDER BY [p].[Id], [b].[Id]
An existing way to solve this is using a projection, just picking the fields that you need and using directly the DTO instead of entities:
var post = await dbContext.Posts
.Select(x => new PostDto()
{
Id = x.Id,
Title = x.Title,
BlogId = x.Blog.Id,
BlogName = x.Blog.Name,
CommentsDto = x.Comments.Select(c => new CommentDto()
{
Id = c.Id,
}).ToList()
})
.ToListAsync();
With the following query as result:
SELECT [p].[Id], [p].[Title], [b].[Id], [b].[Name], [c].[Id]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
LEFT JOIN [Comments] AS [c] ON [p].[Id] = [c].[PostId]
ORDER BY [p].[Id], [b].[Id]
But manually writing the query can be a bit tedious.
ProjectTO to the rescue
AutoMapper has a simple way to solve this using the .ProjectTo method:
var posts = await mapper.ProjectTo<PostDto>(dbContext.Posts, null).ToListAsync();
Generating exactly the same query as the projection:
SELECT [p].[Id], [p].[Title], [b].[Id], [b].[Name], [c].[Id]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
LEFT JOIN [Comments] AS [c] ON [p].[Id] = [c].[PostId]
ORDER BY [p].[Id], [b].[Id]
The .ProjectTo<PostDto>() will tell AutoMapper's mapping engine to emit a select clause to the IQueryable that will inform Entity Framework that it only needs to query the fields presents in the DTO.
A common case for the .ProjectTo is using small DTOs/models. For example, we can have a smaller DTO to just return a summary of the posts to list in the frontend, with just the id, name, and comments counts:
public class PostSummaryDto
{
public int Id { get; set; }
public string Title { get; set; }
public int CommentsCount { get; set; }
}
And writing again only 1 line on C#:
var postSummary = await mapper.ProjectTo<PostSummaryDto>(dbContext.Posts, null).ToListAsync();
The SQL code looks really nice:
SELECT [p].[Id], [p].[Title], (
SELECT COUNT(*)
FROM [Comments] AS [c]
WHERE [p].[Id] = [c].[PostId]
) AS [CommentsCount]
FROM [Posts] AS [p]
As we can see we can have a very performant query just using 1 line of code. If you want to read more about .ProjectTo please read here.
Top comments (1)
How is the autoMapper able to create this part of the SQL query
SELECT COUNT(*)
FROM [Comments] AS [c]
WHERE [p].[Id] = [c].[PostId]
in the last SQL generated.