Most efficient way to map IDataReader to a C# object, also catering for deep-copy?

Question

we have a scenario where we use Enterprise Library Data package for the purposes of our data access needs. We have implemented a Generic Data Reader to hydrate our data into business objects. However we are looking for a more efficient way to do this, that will also cater for deep copy of child collections within objects without the use of reflection since we are dealing with large volumes of data. The Generic Data Reader also doesn't perform well with large amounts of data. I know if we used EF, it could solve this but we are not at a stage in which we can replace our whole data access layer. Is there an alternative or an industry standard to achieving this?

Answer 1

Why don't you hand code the mappers? It's the fastest way to do it, and you create a mapper only once during the project lifetime, right? In projects living five-ten years the amount of time it takes is negligible compared to what you get (easy to correct errors, no need to interpret OR/M errors or their "simplicity").

public interface IEntityMapper
{
   object Map(IDataRecord record);
}

public void UserMapper  : IEntityMapper
{
    public void Map(IDataRecord record)
    {
        var user = new User();
        user.FirstName = record["firstName"]
    }
}

If you got a very large number of records you need to use the number indexer instead to prevent name lookups:

public void UserMapper  : IEntityMapper
{
    public void Map(IDataRecord record)
    {
        var user = new User();

        //requires that you specify columns in your SELECT query
        //to not break the mapper in future versions.
        user.FirstName = record[0]
    }
}

In your repository you can do like this:

public class UserRepository
{
    private readonly IDbConnection _connection;
    private UserMapper _mapper = new UserMapper();

    public IReadOnlyList<User> GetAllUsers()
    {
        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, UserName FROM Users";
            using (var reader = cmd.ExecuteReader())
            {
                var users = new List<User>();
                while (reader.Read())
                {
                   var user = _mapper.Map(reader);
                   users.Add(user);
                }
                return users;
            }
        }
    }
}

You can even move everything to an extension method:

public static class CommandExtensions
{
    public static IReadOnlyCollection<T> ToList<T>(this IDbCommand cmd, IDataMapper mapper)
    {
        using (var reader = cmd.ExecuteReader())
        {
            var items = new List<T>();
            while (reader.Read())
            {
               var item= _mapper.Map(reader);
               items.Add(item);
            }
            return item;
        }
    }
}

which gives you even more lightweight repositories:

public class UserRepository
{
    private readonly IDbConnection _connection;
    private UserMapper _mapper = new UserMapper();

    public IReadOnlyList<User> GetAllUsers()
    {
        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, UserName FROM Users";
            return cmd.ToList<User>(_mapper);
        }
    }
}

Deep copy

Regarding Deep copies I tried to avoid working with complex entities (unless the child entities in fact are value objects). Better to refer to other entities using ID's as it's otherwise hard to keep the responsibility for an entity in one place in your code.

When it comes to mapping I would probably introduce a convention where sub objects are mapped using an alternative method in the mapping interface:

public interface IEntityMapper
{
   object Map(IDataRecord record);
   object Map(IDataRecord record, string prefix);
}

The prefix can be used as a convention to indicate that information is fetched from multiple tables using a join.

public void UserMapper  : IEntityMapper
{
    private AddressMapper _childMapper = new AddressMapper();

    public void Map(IDataRecord record)
    {
        var user = new User();
        user.FirstName = record["FirstName"]
        user.Address = _childMapper.Map(record, "Address_");
    }
}

.. which would make your repository method look like this:

public class UserRepository
{
    private readonly IDbConnection _connection;
    private UserMapper _mapper = new UserMapper();

    public IReadOnlyList<User> GetAllUsers()
    {
        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = @"SELECT Id, UserName, Address.City as Address_City 
                                FROM Users 
                                JOIN Address ON (Address.Id = Users.AddressId)";
            return cmd.ToList<User>(_mapper);
        }
    }
}

I've written about ADO.NET and (my) best practices: http://blog.gauffin.org/2013/01/04/ado-net-the-right-way/