HowTo: Save the WebService result with One-To-Many-Relationships in a clean way

Version 1

    I had the task to query a source database, match all the source records with HEAT using Dapper and do some actions (create/update Incidents) as soon as it has something to match.
    I won't describe the whole process here as this would be a very long post.

    But I will show you how to easily implement a One-To-Many relationship based upon the WebService results:

     

    As described in the HEAT help, the results of a "Search" Web method are returned in a WebServiceBusinessObject[][] class:

    ----

    The results are returned for each parent-child combination.

    Because the four Journal.Email records are associated with the same incident record, this same incident record shows up four times, once for each Journal.Email child record associated with it.

    ----

    You will have several matches for the parent business object.

    I would like to return

    - all open Incidents with the "Service Desk" Service

    - all related Task assignments (IncidentContainsTask)

    - all related Configuration Items (IncidentAssociatesCI)

    - some fields from the Employee# object (IncidentAssociatedCustomer)

     

    You will now have the Parent business object being returned n times, depending on how many tasks or CIs are attached to this Incident:

    foreach (WebServiceBusinessObject[] objOuterList in objList)
    {
        foreach (WebServiceBusinessObject obj in objOuterList)
        {
            WebServiceFieldValue[] objFieldList = obj.FieldValues;
            Console.WriteLine("BO Name:{0}, Field Name: {1}, Field Value: {2}", obj.BusinessObjectName, objFieldList[0].Name, objFieldList[0].Value);
            }
    }
    
    

    Incident 1 has one related CI and two tasks.

    Incident 2 has two related CIs and one task.

    As a result, each Incident will show up 4 times:

    BO Name:Incident, Field Name: Subject, Field Value: Incident 1

    BO Name:Task.Assignment, Field Name: t.TaskType, Field Value: Assignment

    BO Name:CI.Workstation, Field Name: CI.CIType, Field Value: Workstation

    BO Name:Frs_CompositeContract_Contact, Field Name: LoginID, Field Value: user1

    BO Name:Incident, Field Name: Subject, Field Value: Incident 1

    BO Name:Task.Assignment, Field Name: t.TaskType, Field Value: Assignment

    BO Name:CI.Workstation, Field Name: CI.CIType, Field Value: Workstation

    BO Name:Frs_CompositeContract_Contact, Field Name: LoginID, Field Value: user1

    BO Name:Incident, Field Name: Subject, Field Value: Incident 2

    BO Name:Task.Assignment, Field Name: t.TaskType, Field Value: Assignment

    BO Name:CI.Workstation, Field Name: CI.CIType, Field Value: Workstation

    BO Name:Frs_CompositeContract_Contact, Field Name: LoginID, Field Value: user3

    BO Name:Incident, Field Name: Subject, Field Value: Incident 2

    BO Name:Task.Assignment, Field Name: t.TaskType, Field Value: Assignment

    BO Name:CI.Workstation, Field Name: CI.CIType, Field Value: Workstation

    BO Name:Frs_CompositeContract_Contact, Field Name: LoginID, Field Value: user3

    But to really work with the result I would like to have it stored like this in a normalized form:

    class Incident
    {
    public string IncidentNumber { get; set; }
    public string Subject { get; set; }
    public string Symptom { get; set; }
    public List<CI> CIs { get; set; }
    public List<TaskAssignment> Tasks { get; set; }
    public string LoginID { get; set; }
    public string RecID { get; set; }
    
    public Incident()
    {
    CIs = new List<CI>();
    Tasks = new List<TaskAssignment>();
    }
    }
    
    

    One Incident has a list of n Tasks and a list of n CIs.

    The Incident is unique in its class.

     

    This can be done in two simple steps:

    1) store the result in a "raw" format (List<Incident>).

    2) Convert this list into a useful list.

     

    Step 1:

    - create a generic (poco) class. Make sure to implement the IEquatable property:

    class Incident : IEquatable<Incident>
        {
            public string IncidentNumber { get; set; }
            public string Subject { get; set; }
            public string Symptom { get; set; }
            public string CIName { get; set; }
            public string CIType { get; set; }
            public string CIRecID { get; set; }
            public string TaskRecID { get; set; }
            public string TaskStatus { get; set; }
            public string TaskSubject { get; set; }
            public string TaskDetails { get; set; }
            public List<CI> CIs { get; set; }
            public List<TaskAssignment> Tasks { get; set; }
            public string LoginID { get; set; }
            public string RecID { get; set; }
    
            public Incident()
            {
                CIs = new List<CI>();
                Tasks = new List<TaskAssignment>();
            }
    
            public bool Equals(Incident other)
            {
                if (RecID == other.RecID)
                    return true;
    
                return false;
            }
    
            public override int GetHashCode()
            {
                 int hashRecID = RecID == null ? 0 : RecID.GetHashCode();
                 return hashRecID;
            }
        }
    
    
    class TaskAssignment : IEquatable<TaskAssignment>
    {
        public string Subject { get; set; }
        public string Status { get; set; }
        public string Details { get; set; }
        public string RecID { get; set; }
        public string ParentLink_RecID { get; set; }
    
        public bool Equals(TaskAssignment other)
        {
            if (RecID == other.RecID)
                return true;
    
            return false;
        }
    
        public override int GetHashCode()
        {
            int hashRecID = RecID == null ? 0 : RecID.GetHashCode();
            return hashRecID;
        }
    }
    
    class CI : IEquatable<CI>
    {
        public string Name { get; set; }
        public string CIType { get; set; }
        public string RecID { get; set; }
    
        public bool Equals(CI other)
        {
            if (RecID == other.RecID)
                return true;
    
            return false;
        }
    
        public override int GetHashCode()
        {
            int hashRecID = RecID == null ? 0 : RecID.GetHashCode();
            return hashRecID;
        }
    }
        
    
    

    - create a list of objects using this class

    List<Incident> openIncidentList = new List<Incident>();
    List<Incident> normalizedIncidents = new List<Incident>();
    foreach (WebServiceBusinessObject[] incObjOuterList in incObjList)
    {
        try
        {
            var tempIncident = new Incident()
            {
                // Array order is static based on rule & field classes!
                RecID = incObjOuterList[0].RecID.ToString(),
                Subject = incObjOuterList[0].FieldValues[0].Value.ToString(),
                Symptom = ReplaceIfNull(incObjOuterList[0].FieldValues[1], ""),
                IncidentNumber = incObjOuterList[0].FieldValues[2].Value.ToString(),
                TaskRecID = incObjOuterList[1].RecID,
                TaskSubject = incObjOuterList[1].FieldValues[1].Value.ToString(),
                TaskDetails = ReplaceIfNull(incObjOuterList[1].FieldValues[2], ""),
                TaskStatus = incObjOuterList[1].FieldValues[3].Value.ToString(),
                CIRecID = incObjOuterList[2].RecID.ToString(),
                CIName = incObjOuterList[2].FieldValues[1].Value.ToString(),
                CIType = incObjOuterList[2].BusinessObjectName.ToString(),
                LoginID = incObjOuterList[3].FieldValues[0].Value.ToString()
            };
            openIncidentList.Add(tempIncident);
        }
        catch (Exception ex)
        {
            // your exceptions here
        }
    }
    
    

     

    Step 2: Normalize this result with Linq and store it in a new List<T>.

    You can do it with lambda expressions as well if needed.

    // normalize Incidents to use List<T> for 1:n relations
    normalizedIncidents = (from i in openIncidentList
       group i by new { i.RecID, i.IncidentNumber, i.Subject, i.Symptom, i.LoginID}
       into rowGroups
       select new Incident
       {
           RecID = rowGroups.Key.RecID,
           IncidentNumber = rowGroups.Key.IncidentNumber,
           Subject = rowGroups.Key.Subject,
           Symptom = rowGroups.Key.Symptom,
           LoginID = rowGroups.Key.LoginID,
           Tasks = (from i in rowGroups
                    select new TaskAssignment
                    {
                    RecID = i.TaskRecID,
                    Details = i.TaskDetails,
                    Status = i.TaskStatus,
                    Subject = i.TaskSubject,
                    ParentLink_RecID = i.RecID
                    }).Distinct().ToList(),
                    CIs = (from i in rowGroups
                    select new CI
                    {
                    RecID = i.CIRecID,
                    Name = i.CIName,
                    CIType = i.CIType,
                    }).Distinct().ToList(),
        }).ToList();
    
    
    

     

    You will now have a List<Incident> according to your generic class.

    The fields CIRecID, TakRecID etc. are only for temporary purpose. You can also create two different classes to have a clean normal form.