Infinite Loop When Loading Object

Question

I've been racking my brains for a few days, trying not to generate infinite loops when I load my classes with data coming from a database through a method inside the class.

In a playful example this is what is happening. Consider the class below:

    public class Person
{
    public int id { get; set; }
    public string name{ get; set; }
    public Person carrier{ get; set; }
}

In the method inside the class I defined it like this:

public void Load()
{
    Datatable tableResult;
    using (MySqlConnection con = new MySqlConnection(ConnectionString))
    {
        using (MySqlCommand com = new MySqlCommand(My.Resources.PersonSelect))
        {
            com.Parameters.AddWithValue("@ID", this.ID);
            using (MySqlDataAdapter adp = new MySqlDataAdapter(CmdPerson))
            {
                TableResult = new DataTable();
                adp.Fill(TableResult);
                if (tableResult.Rows.Count == 1)
                {
                    ID = tableResult.Rows(0).Item("id");
                    Name = tableResult.Rows(0).Item("name");
                    Carrier = new Person().Load(tableResult.Rows(0).Item("carrierid"));
                }
            }
        }
    }
}

Also consider that I have a person, and that person's carrier is himself. this would generate an infinite loop.

I've already tried implementing LazzyLoading in the properties, but I still don't know which classes might have this same problem. Is there any other way to solve this problem?

Answer 1

You can try this:

public void Load()
{
    var tableResult = new DataTable(); 
    using (var con = new MySqlConnection(ConnectionString))
    using (var com = new MySqlCommand(My.Resources.PersonSelect))
    using (var adp = new MySqlDataAdapter(com))
    {
        com.Parameters.AddWithValue("@ID", this.ID);
        adp.Fill(tableResult);
    }

    if (tableResult.Rows.Count == 1)
    {
        ID = tableResult.Rows(0).Item("id");
        Name = tableResult.Rows(0).Item("name");
        if (ID == tableResult.Rows(0).Item("carrierid"))
        {
            Carrier = this;
        }
        else 
        {
            Carrier = new Person().Load(tableResult.Rows(0).Item("carrierid"));
        }
    }
}

It still has potential to have a loop in a chain (Person A's carrier is Person B, who's carrier is Person A again), but if that's not a situation in your data you might be okay.

Otherwise you can opt to lazy-load the carrier, like this:

public class Person
{
    public int ID { get; set; }
    public string Name{ get; set; }

    private int _carrierID;
    private Person _carrier = null;
    public Person Carrier
    { 
       get 
       {
           if (_carrier == null) _carrier = Person.Load(_carrierID);
           return _carrier;                  
       }
       private set
       {
            _carrier = value;  
            if (_carrier != null) _carrierID = _carrier.ID;
       }
    }

    public static Person Load(int ID)
    {
        var tableResult = new DataTable();
        using (var con = new MySqlConnection(ConnectionString))
        using (var com = new MySqlCommand(My.Resources.PersonSelect))
        using (var adp = new MySqlDataAdapter(com))
        {
            com.Parameters.AddWithValue("@ID", ID);
            adp.Fill(tableResult);
        }

        if (tableResult.Rows.Count == 1)
        {
            return new Person() {
                ID = tableResult.Rows(0).Item("id"),
                Name = tableResult.Rows(0).Item("name"),
                _carrierid = tableResult.Rows(0).Item("carrierid")
            };
        }
        return null;
    }
}

This can also still possibly loop forever if you try to write code like this:

var p = Person.Load(someID);
while (p.Carrier!=null)
{
   p = p.Carrier;
}

But at least now you have to put the loop out in the open where you can find it.

Also note how I converted the Load() method to static.