Using Automapper Part 2 – Object Relational Mapping (ORM)

Last time I described that basic usage of Automapper to map the properties of one object to the properties of another. A more interesting use of the tool is as an object-relational mapper (ORM). Object-relational mapping is the process of taking data retrieved from the database and representing it as objects in your application. An alternative method would be to manipulate ADO.Net objects that are created by the Sql libraries as part of the ADO framework. The ADO.Net objects can either be data readers or data tables which can be tedious to work with. Rather than access the data through those objects, ORM allows you to create data models that represent the data from the database for more intuitive manipulation. Let’s look at an example.

public class User
    public int UserId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

public List<User> GetUsers()
    Automapper.Mapper.CreateMap<IDataReader, User>();

    SqlCommand command = new SqlCommand("select * from Users");
    command.Connection = new SqlConnection("connection string goes here");

    IDataReader dataReader = command.ExecuteReader();
    List<User> users = Automapper.Mapper.Map<List<User>>(dataReader);
    return users;

Establishing the mapping relationship and actually mapping the objects gets a little tricky. Notice the line Automapper.Mapper.CreateMap() maps the data reader to a single object. The problem is that likely multiple records will be returned from the query resulting in multiple objects. As such when the IDataReader is actually mapped to a list of objects, the destination is specified as an IEnumberable. I prefer to directly use the List class because it is not uncommon to use the FirstOrDefault() Linq extenstion method when I know there is only 1 record being returned by the query if I’m retrieving a record by PK for example.

It would probably be a good idea to throw a try-catch around the Map method call in order to catch exceptions thrown when the results of an IDataReader cannot be mapped to an object. Also a good idea to close the IDataReader but the example shown is to demonstrate the object mapping capabilities.

The example above expects that the database column names identically match the data model properties (including case, ie ‘UserId’ does not map to ‘UserID’). In these cases when you create the mapping relationship you can specify which properties should be mapped where. Mapping the data reader is a bit annoying but looks like the below code.

public List<User> GetUsers()
    Automapper.Mapper.CreateMap<IDataReader, User>()
        .ForMember(m => m.UserId, opt => opt.MapFrom(r => r.GetInt32(r.GetOrdinal("UserID"))))
        .ForMember(m => m.FirstName, opt => opt.MapFrom(r => r.GetString(r.GetOrdinal("FirstNameColumnName"))));

    // Rest of code...


Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s