DEV Community

Cover image for Entity Framework Core: Simplify your queries with AutoMapper
Ignacio laborde for Cloud(x);

Posted on

Entity Framework Core: Simplify your queries with AutoMapper

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; }
}
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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     
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

And writing again only 1 line on C#:

var postSummary = await mapper.ProjectTo<PostSummaryDto>(dbContext.Posts, null).ToListAsync();
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
akshatsparrow profile image
Akshat Bisht

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.