Free Tool: Automapping LINQ-to-Entities Queries

Moving property values from one object to another can be tricky; DevTrends has a set of extensions that can help.

• By Peter Vogel
• 05/23/2012

In a recent C# Corner column Patrick Steele discussed AutoMapper, which is a fabulous tool. However, AutoMapper has one deficiency when used with LINQ queries.

Let's say you want to return some customer data from your application to some client. It's a bad idea to return Entity Framework objects and, besides, it's an unusual case where the data required by the client is a one-to-one match with your business entities. In fact, in most cases you need a Data Transfer Object that returns some data from several business entities. As an example, assume that you need this DTO to return three pieces of Customer data and the number of Orders for that customer:

Public Class CustomerDTO
  Public Property CustomerID As String
  Public Property ContactName As String
  Public Property City As String
  Public Property OrdersCount As Integer
End Class

You could write code like this to extract the data and initialize a set of CustomerDTOs:

Dim oc As New NorthwindEntities()
Dim custDTOs = From cust In oc.Customers
               Where cust.CustomerID = "ALFKI"
               Select New CustomerDTO With 
                    {.CustomerID = cust.CustomerID,
                     .ContactName = cust.ContactName,
                     .City = cust.City,
                     .OrdersCount = cust.Orders.Count}

The code that moves the data from the Customer object to the DTO is boring to write and, quite frankly, error prone (you know that you're going to use Copy-and-Paste to generate all but the first line and there's a very good chance you'll get end up getting at least one of the lines wrong).

This is where AutoMapper comes in. AutoMapper moves values in properties from one object (typically, a business entity) to another object (typically, a Data Transfer Object or DTO) or vice-versa, often in one line of code. By default, AutoMapper does this by property name, as you'll notice that my code does: City on the Customer object goes to City on the DTO; Orders.Count on the Customer object goes to OrdersCount on the DTO. So AutoMapper will work great, except for that deficiency I mentioned.

When used with a LINQ-to-Entities statement, AutoMapper works its magic after the SQL statement is generated and your data is retrieved. This means that my LINQ statement will cause all the columns in the Customer table to be retrieved in order to move just the four required properties to the DTO. This is a problem Jimmy Bogard, who developer AutoMapper, recognizes, has a workaround for, and is considering as an enhancement to AutoMapper.

In the meantime, DevTrends (a UK consultancy) has provided a set of extensions that does some of what AutoMapper does and avoids this problem. Their Project and To extension methods work together to determine which fields are required by the DTO, and ensure that the SQL statement generated by LINQ retrieves only those columns. The syntax is simple -- this example retrieves only the columns specified by the CustomerDTO class passed to the To method:

Dim oc As New NorthwindEntities()
Dim custDTOs = (From cust In oc.Customers
                Where cust.CustomerID = "ALFKI"
                Select cust).Project.To(Of CustomerDTO)()