AutoMapper with Entity Framework the right way

Let’s say we have the following entity that mapped to a database table using Entity Framework:

 public class Person
    {
        public int Id { get; set; }
        [StringLength(250)]
        public string FirstName { get; set; }

        [StringLength(250)]
        public string LastName { get; set; }


        public DateTime Dob { get; set; }

        [StringLength(100)]
        public string Email { get; set; }

        [StringLength(15)]
        public string Mobile { get; set; }
    }

and we have the following DTO (Data Transfer Object) to be used in the presentation layer:

public class PersonDto
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

and we have the following AutoMapper profile to map the Person entity to PersonDto :

public class Mapper:Profile
    {
        public Mapper()
        {
            CreateMap<Person, PersonDto>();
        }
    }

to map the data from Entity Framework, we might use the following:

 var person = _db.Persons
.Select(x => _mapper.Map<Person, PersonDto>(x))
.FirstOrDefault();

The issue with the above code, that it will send a full query to the database, selecting all the columns in Person table while we only need two columns (First Name & Last Name), to check the query sent to the database we can use ToQueryString() at the end of the Linq query like the following:

var query1 =  _db.Persons
.Select(x => _mapper.Map<Person, PersonDto>(x))
.ToQueryString();

the value of query1 will be the following :

SELECT 
 [p].[Id],
 [p].[Dob],
 [p].[Email],
 [p].[FirstName],
 [p].[LastName],
 [p].[Mobile] 
FROM [Persons] AS [p]

This query asks the database to retrieve all the columns, to enforce the Entity Framework to retrieve only needed data required by PersonDto, we can use ProjectTo like this:

 var person  = _mapper.ProjectTo<PersonDto>(_db.Persons)
.FirstOrDefault();

If we examine the generated SQL command sent by Entity Framework :

  var query2 = _mapper.ProjectTo<PersonDto>(_db.Persons)
  .ToQueryString();

the value of the query2 will be:

SELECT 
[p].[FirstName],
[p].[LastName] 
FROM [Persons] AS [p]

This query includes exactly the columns we need, this can be very helpful if we are dealing with a larger table that could contain teens of columns, selecting only the needed columns will improve the performance.

for more details about ProjectTo check the documentation here

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s