How do I use automapper to map a dataset with multiple tables

Question

DISCLAIMER: this is a copy paste from an older stackoverflow post that isn't available anymore, but I have exaclty the same problem, so it seemed appropriate to repost it as it was never answered.

I have a stored procedure that will return 4 result sets (contacts, addresses, email, phones) which is populated into a dataset. I would like to use AutoMapper to populate a complex object.

public class Contact 
{
    public Guid ContactId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Address> Addresses { get; set; }
    public List<Phone> Phones { get; set; }
    public List<Email> Emails { get; set; }
}

public partial class Address:BaseClass
{
    public Guid ContactId { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
    public string City { get; set; }
    public string StateProvince { get; set; }
    public string PostalCode { get; set; }
    public string CountryCode { get; set; }   
}

public class Email
{
    public Guid EmailId { get; set; } 
    public Guid ContactId { get; set; } 
    public string EmailAddress { get; set; }
}

public class Phone
{
    public Guid PhoneId { get; set; } 
    public Guid ContactId { get; set; }         
    public string Number { get; set; } 
    public string Extension { get; set; }
}

I have a method that will get data and return a list of contact. After the DataSet is populate, I define the relationships between the tables.

I found many examples where you convert the DataSet (or table) to a reader using the CreateDataReader method and that is what I'm doing here. The method will in fact parse the first table into the object, but will not enumerate through the related tables.

public List<Contact> GetContacts()
{
    List<Contact> theList = null;

    // Get the data
    Database _db = DatabaseFactory.CreateDatabase();
    DataSet ds = db.ExecuteDataSet(CommandType.StoredProcedure, "GetContacts");

    //The dataset should contain 4 tables
    if (ds.Tables.Count == 4) 
    {    
        //Create the maps
        Mapper.CreateMap<IDataReader, Contact>(); // I think I'm missing something here
        Mapper.CreateMap<IDataReader, Address>();
        Mapper.CreateMap<IDataReader, Email>();
        Mapper.CreateMap<IDataReader, Phone>();

        //Define the relationships        
        ds.Relations.Add("ContactAddresses", ds.Tables[0].Columns["ContactId"], ds.Tables[1].Columns["ContactId"]);
        ds.Relations.Add("ContactEmails", ds.Tables[0].Columns["ContactId"], ds.Tables[2].Columns["ContactId"]);
        ds.Relations.Add("ContactPhones", ds.Tables[0].Columns["ContactId"], ds.Tables[3].Columns["ContactId"]);

        IDataReader dr = ds.CreateDataReader();
        theList = Mapper.Map<List<Contact>>(dr);    
    }

    return (theList);    
}

I feel as though I'm missing something in the mapping for the Contact object, but I just can't find a good example to follow.

If I manually populate the contact object and then pass is to my controller, it will properly load the ContactModel object using a direct mapping

public ActionResult Index()
{
    //From the ContactController
    Mapper.CreateMap<Contact, Models.ContactModel>();
    Mapper.CreateMap<Address, Models.AddressModel>();

    List<Models.ContactModel> theList = Mapper.Map<List<Contact>, List<Models.ContactModel>>(contacts);

    return View(theList);
}

Is what I want to do even possible?

Answer 1

IDataReader mapper is very simple one, it can populate an object out of a data reader, where it maps the object properties by column names. It was not designed to create a complex data structures with relations, etc.

Also, the DataSet.CreateDataReader will produce a multiple resultset data reader - i.e. the reader will have few result sets for each table, but it will not preserve the relations.

So, in order to get what you want, you need to create reader for each table, map each reader to different collection, and then use these results to create the final complex object(s).

Here I'm providing the simplistic approach, but you can go wild, and create custom resolvers, etc., to encapsulate everything.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using AutoMapper;
using NUnit.Framework;

namespace StackOverflowExample.Automapper
{
    public class Contact
    {
        public Guid ContactId { get; set; }
        public string Name { get; set; }
        public List<Address> Addresses { get; set; }
    }

    public partial class Address
    {
        public Guid AddressId { get; set; }
        public Guid ContactId { get; set; }
        public string StreetAddress { get; set; }
    }

    [TestFixture]
    public class DatasetRelations
    {
        [Test]
        public void RelationMappingTest()
        {
            //arrange
            var firstContactGuid = Guid.NewGuid();
            var secondContactGuid = Guid.NewGuid();

            var addressTable = new DataTable("Addresses");
            addressTable.Columns.Add("AddressId");
            addressTable.Columns.Add("ContactId");
            addressTable.Columns.Add("StreetAddress");
            addressTable.Rows.Add(Guid.NewGuid(), firstContactGuid, "c1 a1");
            addressTable.Rows.Add(Guid.NewGuid(), firstContactGuid, "c1 a2");
            addressTable.Rows.Add(Guid.NewGuid(), secondContactGuid, "c2 a1");

            var contactTable = new DataTable("Contacts");
            contactTable.Columns.Add("ContactId");
            contactTable.Columns.Add("Name");
            contactTable.Rows.Add(firstContactGuid, "contact1");
            contactTable.Rows.Add(secondContactGuid, "contact2");

            var dataSet = new DataSet();
            dataSet.Tables.Add(contactTable);
            dataSet.Tables.Add(addressTable);

            Mapper.CreateMap<IDataReader, Address>();
            Mapper.CreateMap<IDataReader, Contact>().ForMember(c=>c.Addresses, opt=>opt.Ignore());

            //act
            var addresses = GetDataFromDataTable<Address>(dataSet, "Addresses");
            var contacts = GetDataFromDataTable<Contact>(dataSet, "Contacts");
            foreach (var contact in contacts)
            {
                contact.Addresses = addresses.Where(a => a.ContactId == contact.ContactId).ToList();
            }
        }

        private IList<T> GetDataFromDataTable<T>(DataSet dataSet, string tableName)
        {
            var table = dataSet.Tables[tableName];
            using (var reader = dataSet.CreateDataReader(table))
            {
                return Mapper.Map<IList<T>>(reader).ToList();
            }
        }
    }
}