Using AutoMapper in Entity Framework Projections

12 September 2015

AutoMapper is a wonderful tool that lets us convert one class to another using fluently-defined configurations. It is useful when there is a need for multiple mappings, such mappings from persistence model classes to domain model classes or mapping from domain model classes to viewmodels. In this post we are going to look at how to use AutoMapper with Entity Framework for projections.

If you haven’t used AutoMapper yet, I strongly recommend giving it a try. The home page is here and there is also a NuGet package available for easy installation in Visual Studio projects. You can also check out my AutoMapper Quickstart post to get the basics.

What is an Entity Framework Projection?

When querying in Entity Framework the default behavior is for all columns to be included in the query and all properties to be populated in the entity class. In some cases, however, the desired behavior is to query only a partial set of columns. Let’s see this in action.

Demo: Entity Framework Projection

Say we have a User entity with the following declaration:

public class User
{
    public string About { get; set; }
    public string FirstName { get; set; }
    public int Id { get; set; }
    public string LastName { get; set; }
    public string ProfileImageUrl { get; set; }
}

And we have the following context class:

public class ApplicationContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

When the database is queried like so:

using System.Linq;

// ...

using (var context = new ApplicationContext())
{
    var users = context.Users.ToList();
}

The resulting SQL query is:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[About] AS [About], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[ProfileImageUrl] AS [ProfileImageUrl]
    FROM [dbo].[Users] AS [Extent1]
go

There are some strange constructs because this query was generated by Entity Framework, but it can be clearly seen that all columns are being queried.

Now let’s use projections and look at the resulting query. Let’s change the code to:

using System.Linq;

// ...

using (var context = new ApplicationContext())
{
    var users = context.Users
                        .Select(u => new
                        {
                            FirstName = u.FirstName,
                            LastName = u.LastName,
                            ProfileImageUrl = u.ProfileImageUrl
                        })
                        .ToList();
}

We are using the Select method to achieve the projection. Running this query produces the following SQL:

SELECT 
    1 AS [C1], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[ProfileImageUrl] AS [ProfileImageUrl]
    FROM [dbo].[Users] AS [Extent1]
go

Now the query is only limited to a subset of the columns.

Strongly-Typed Projections

In the projection example the type of users was a list of anonymous types. If we wanted to use the results of our projection as the type of a method return call, we should create a class and project into that class. As an example, let’s create a UserSummary class:

public class UserSummary
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string ProfileImageUrl { get; set; }
}

Now we can change the projection query to:

using System.Linq;

// ...

using (var context = new ApplicationContext())
{
    var users = context.Users
                        .Select(u => new UserSummary
                        {
                            FirstName = u.FirstName,
                            LastName = u.LastName,
                            ProfileImageUrl = u.ProfileImageUrl
                        })
                        .ToList();
}

The only change is the addition of the UserSummary type. The generated SQL query will still be the same, but this time the type of users would be a list of UserSummary instead of a list of anonymous types. We can then use this list as a return value of a method.

Using AutoMapper

In the previous projection queries we had to specify each property one by one. It would be nice if we can use an AutoMapper map to take care of the projection for us. Fortunately this is supported by AutoMapper. To take advantage of this, we just need to add the map from User to UserSummary:

using AutoMapper;

// ...

Mapper.CreateMap<User, UserSummary>();

Since the property names of the two classes match, there is no need for further configurations.

Now we can change our query code to:

using AutoMapper.QueryableExtensions;
using System.Linq;

// ...

using (var context = new ApplicationContext())
{
    var users = context.Users
                        .Project()
                        .To<UserSummary>()
                        .ToList();
}

We are using the Project and To methods to enable the projection. Don’t forget to add the AutoMapper.QueryableExtensions using statement to enable these methods.

Running the query will generate the same SQL that the previous projection queries used, where only a subset of the columns was queried.

Conclusion

Projections in Entity Framework lets us select only a subset of the columns from a database. This can be desirable in performance optimization and other scenarios. We saw how to store the projected results in a strongly typed class, which can then be used as return types in methods. Finally, we saw how we can take advantage of AutoMapper when doing our projections.