How to use automapper to map a dataset to multiple tables

Question

DISCLAIMER: This is to copy a copy from the old stackoverflow entry, which is no longer available, but I have the same problem, so it would be advisable to recompile it since it never responded.

I have a stored procedure that will return 4 sets of results (contacts, addresses, email, phones) that are populated into the data set. 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 receive data and return a list of contacts. After populating the DataSet, I define the relationships between the tables.

I found many examples where you convert a DataSet (or table) to a reader using the CreateDataReader method, and this is what I am doing here. The method will actually analyze the first table in the object, but will not list through 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); } 

It seems to me that I'm missing something in the mapping for the Contact object, but I just can't find a good example.

If I manually populate the contact object and then pass it to my controller, it will load the ContactModel object correctly using direct matching

 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); } 

I want to make it even possible?

Answer 1

The IDataReader collation tool is very simple, it can populate an object from a data reader, where it matches the properties of an object by column names. It was not intended to create complex data structures with relationships, etc.

In addition, DataSet.CreateDataReader will produce a data reader with several resulting data, that is, the reader will have several result sets for each table, but it will not save the relationship.

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

Here I suggest a simplified approach, but you can go into the wild and create custom transformers , 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(); } } } } 

Answer 2

I'm incredibly late to the party, but in case this helps someone else.

I serialized my dataset to a JSON string using Json.NET .

 var datasetSerialized = JsonConvert.SerializeObject(dataset, Formatting.Indented); 

See json as a string when debugging in Visual Studio and copy it to the clipboard.

Then in Visual Studio go to the menu "Edit" → "Paste" → "Paste JSON As Classes

You will have a POCO for each table with relationships.

Finally, deserialize the JSON into a “RootObject” created by inserting the As JSON classes.

 var rootObj = JsonConvert.DeserializeObject<RootObject>(datasetSerialized);