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.

Entity Framework Core: Simplify your queries with AutoMapper

ProjectTO to the rescue