Entity Framework Core: Simplify your queries with AutoMapper
Tags: dotnet, entityframework, automapper
Published: 2022-04-30 23:37:22

---
title: 'Entity Framework Core: Simplify your queries with AutoMapper'
published: true
description: We can create smaller and performant queries using Entity Framework Core and AutoMapper.
tags: 'dotnet, entityFramework, automapper'
cover_image: 'https://raw.githubusercontent.com/cloudx-labs/posts/main/posts/ilaborde/assets/01-automapper-cover.png'
---

For the examples of code, we are going to use the following entities:

```c#
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:

```c#
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.

```c#
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:

```SQL
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:

```c#
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:

```SQL
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:

```c#
var posts = await mapper.ProjectTo<PostDto>(dbContext.Posts, null).ToListAsync();
```

Generating exactly the same query as the projection:

```SQL
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:

```c#
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#:

```c#
var postSummary = await mapper.ProjectTo<PostSummaryDto>(dbContext.Posts, null).ToListAsync();
```

The SQL code looks really nice:

```SQL
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](https://docs.automapper.org/en/stable/Queryable-Extensions.html).