How to implement one to many relationship

Question

I have a one to many relationship coming from a stored procedure. I have several one to many relationships in the query and i am trying to map these fields to a C# object. The problem i am having is i get duplicate data because of the one to many relationships. Here is a simplified version of my code:

Here is the objects classes:

public class Person {     public int Id { get; set; }     public string Name { get; set; }     public List<Color> FavoriteColors { get; set; }     public List<Hobby> Hobbies { get; set; }      public Person()     {         FavoriteColors = new List<Color>();         Hobbies = new List<Hobby>();     } }  public class Color {     public int Id { get; set; }     public string Name { get; set; } }  public class Hobby {     public int Id { get; set; }     public string Name { get; set; } } 

Here is how I am retrieving the data:

using (SqlConnection conn = new SqlConnection("connstring..")) {     string sql = @"                     SELECT                          Person.Id AS PersonId,                          Person.Name AS PersonName,                          Hobby.Id AS HobbyId,                         Hobby.Name AS HobbyName,                         Color.Id AS ColorId,                         Color.Name AS ColorName                     FROM Person                     INNER JOIN Color on Person.Id = Color.PersonId                     INNER JOIN Hobby on Person.Id = Hobby.PersonId";     using (SqlCommand comm = new SqlCommand(sql, conn))     {         using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))         {             List<Person> persons = new List<Person>();             while (reader.Read())             {                 Person person = new Person();                 //What to do             }         }     } } 

As you can see there can be multiple colors and hobbies for a given Person. Usually, I would use Entity Framework to solve this mapping but we are not allowed to use any orms. Is there a technique to properly unflatten this data?

Answer 1

The idea is while iterating on the reader check if the existing row person id exists in the person list. If not create a new person object and declare two separate lists to hold the hobby and color info. For subsequent iterations go on populating these two lists because these will always be the same persons data. One you get to a new record for a new person, add these lists to the person object and start over with a new person object

Below is the sample code:

                string sql = @"                 SELECT                      Person.Id AS PersonId,                      Person.Name AS PersonName,                      Hobby.Id AS HobbyId,                     Hobby.Name AS HobbyName,                     Color.Id AS ColorId,                     Color.Name AS ColorName                 FROM Person                 INNER JOIN Color on Person.Id = Color.PersonId                 INNER JOIN Hobby on Person.Id = Hobby.PersonId                 Order By PersonId"; // Order By is required to get the person data sorted as per the person id             using (SqlCommand comm = new SqlCommand(sql, conn))             {                 using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))                 {                     List<Person> persons = new List<Person>();                     while (reader.Read())                     {                         var personId = reader.GetInt32(0);                         var personName = reader.GetString(1);                         var hobbyId = reader.GetInt32(3);                         var hobbyName = reader.GetString(4);                         var colorId = reader.GetInt32(5);                         var colorName = reader.GetString(6);                          var person = persons.Where(p => p.Id == personId).FirstOrDefault();                         if (person == null)                         {                             person = new Person();                             person.Id = personId;                             person.Name = personName;                              hobby = new Hobby() { Id = hobbyId, Name = hobbyName };                             color = new Color() { Id = colorId, Name = colorName };                              person.FavoriteColors = new List<Color>();                             person.Hobbies = new List<Hobby>();                              person.FavoriteColors.Add(color);                             person.Hobbies.Add(hobby);                              persons.Add(person);                         }                         else                         {                             hobby = new Hobby() { Id = hobbyId, Name = hobbyName };                             color = new Color() { Id = colorId, Name = colorName };                              //JT Edit: if the colour/hobby doesn't already exists then add it                             if (!person.FavoriteColors.Contains(color))                                person.FavoriteColors.Add(color);                              if (!person.Hobbies.Contains(hobby))                                person.Hobbies.Add(hobby);                         }                     }                 }             }         } 

Answer 2

I think ultimately, all approaches mentioned here work. We could one up the solutions by focusing on performance.

@Mark Menchavez commented on the performance impact of repeatedly going back to the database when we start out with a simple list of persons. For a huge list, this impact is significant and should be avoided as much as possible.

Ultimately, the best thing is to fetch the data in as few chunks as possible; in this instance as one chunk will be ideal (if the joins are not too expensive). Databases are optimized for working on sets of data and we will use this to avoid the overhead of setting up multiple, repeated connections (especially if we are going over the wire to an instance of Sql running on another machine).

I will use @Luke101's approach but just change to the List to a Dictionary of values. The hash look up of keys will be faster than the use of Where in @Koder's response. Also note that I have changed SQL to read as a LEFT JOIN to accommodate those Persons that don't have a Hobby or Color on record and allow them to be returned as NULL (DBNull in .NET).

Also note that because of the shape of the tables and data, it is possible to have Colors and/or Hobbies repeated multiple times, so we do need to check for those too and not just assume there will be one Color and one Hobby.

I didn't bother repeating the classes here.

        public static IEnumerable<Person> DataFetcher(string connString)     {         Dictionary<int, Person> personDict = new Dictionary<int,Person>(1024);  //1024 was arbitrarily chosen to reduce the number of resizing operations on the underlying arrays;                                                                                  //we can rather issue a count first to get the number of rows that will be returned (probably divided by 2).          using (SqlConnection conn = new SqlConnection(connString))         {             string sql = @"                 SELECT                      Person.Id AS PersonId,                      Person.Name AS PersonName,                      Hobby.Id AS HobbyId,                     Hobby.Name AS HobbyName,                     Color.Id AS ColorId,                     Color.Name AS ColorName                 FROM Person                 LEFT JOIN Color on Person.Id = Color.PersonId                 LEFT JOIN Hobby on Person.Id = Hobby.PersonId";              using (SqlCommand comm = new SqlCommand(sql, conn))             {                 using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))                 {                     while (reader.Read())                     {                         int personId = reader.GetInt32(0);                         string personName = reader.GetString(1);                          object hobbyIdObject = reader.GetValue(2);                         object hobbyNameObject = reader.GetValue(3);                         object colorIdObject = reader.GetValue(4);                         object colorNameObject = reader.GetValue(5);                          Person person;                          personDict.TryGetValue(personId, out person);                          if (person == null)                         {                             person = new Person                             {                                 Id = personId,                                 Name = personName,                                  FavoriteColors = new List<Color>(),                                 Hobbies = new List<Hobby>()                             };                              personDict[personId] = person;                         }                          if (!Convert.IsDBNull(hobbyIdObject))                         {                             int hobbyId = Convert.ToInt32(hobbyIdObject);                             Hobby hobby = person.Hobbies.FirstOrDefault(ent => ent.Id == hobbyId);                              if (hobby == null)                             {                                 hobby = new Hobby                                 {                                     Id = hobbyId,                                     Name = hobbyNameObject.ToString()                                 };                                  person.Hobbies.Add(hobby);                             }                         }                          if (!Convert.IsDBNull(colorIdObject))                         {                             int colorId = Convert.ToInt32(colorIdObject);                             Color color = person.FavoriteColors.FirstOrDefault(ent => ent.Id == colorId);                              if (color == null)                             {                                 color = new Color                                 {                                     Id = colorId,                                     Name = colorNameObject.ToString()                                 };                                  person.FavoriteColors.Add(color);                             }                         }                     }                 }             }         }          return personDict.Values;     }