Populate Model class from Data in Backend

Question

I have a database that has two tables as follows, Please ignore the data but the format looks as follows

Now I have a Model class that is constructed as follows

public class FamilyModel
    {

        public string Name { get; set; }
        public List<FamilyModel> FamilyList { get; set; }
        public FamilyModel()
        {
            FamilyList = new List<FamilyModel>();
        }
}

Now all I want is to get data from the two tables and populate the list.

So I have a stored procedure that returns data as follows

So I have written some code to populate the above class. But it dosent work. I get a count of 5 when I debug. I want the count to be 2 and when expanded I want something like FamilyA ->{Nick, Tom, Pam}.. FamilyB->{Harry} and so on. Please help fixing this code.

    public static FamilyModel familyData()
            {
                //FamilyModel fml = new FamilyModel();
                //fml.FamilyList = new List<FamilyModel>();
                using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0; AttachDbFilename=|DataDirectory|\Families.mdf; Integrated Security=True; Connect Timeout=30;"))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("sp_GetFamilies", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())                                                             {                                                          
                        FamilyModel fm = new FamilyModel();                    
                        fm.Name = dr["FamilyName"].ToString();
                        foreach (var item in dr["ChildName"].ToString())
                        {
                            if (Convert.ToInt32(dr["id"]) == Convert.ToInt32(dr["FID"]))
                            {
                                fm.FamilyList.Add(new FamilyModel() { Name = dr["ChildName"].ToString() });
                            }
                        }

                    }
 return fm;
                }

    }

Answer 1

You need to distinguish between a new row in the data set and a new FamilyModel. One way to do this is to declare a list of models, then look up the "right" one before you add the current child row:

var rootModel = new FamilyModel();
rootModel.Name = "root";

// ... Set up data reader ...

while (dr.Read()) 
{
    //This requires support for the Id in your FamilyModel:
    var id = (int)dr["Id"];
    //You could also use ".Single(...)" here
    var fm = rootModel.FamilyList.Where(x => x.Id == id).First();
    if (fm == null) 
    {
        fm = new FamilyModel();
        fm.Name = dr["FamilyName"].ToString();
        rootModel.FamilyList.Add(fm);
    }
    fm.FamilyList.Add(new FamilyModel() { Name = dr["ChildName"].ToString() });
}

For each row in your database query, you'll:

1. Try to look up that family in your list
2. If you don't find one, create a new one. Add it to your top-level list.
3. Add the child name as a sub-element of the "current" family.

Answer 2

Here is some source code that should get the right idea across. Below it, I've included some explanation for what's going on.

using Dapper;
public class FamilyModel
{
    public int Id { get; set;}
    public string FamilyName { get; set; }
    public List<Person> Members { get; set; } = new List<Person>();//Initializer for Auto-Property, C#6<= only
}
public class Person
{
    public int Id { get; set;}
    public string Name { get; set; }
}
public class DatabasePOCO
{
    public string FamilyName { get; set; }
    public string ChildName { get; set; }
    public int Fid { get; set; }
    public int Id { get; set;}
}
void Main()
{
    using (IDbConnection conn = new SqlConnection("..."))
    {
        conn.Open();
        var raw = conn.Query<DatabasePOCO>("sp_GetFamilies",
        commandType: CommandType.StoredProcedure);//Could be dynamic, not typed
        var familyList = raw
        .GroupBy(x => x.Fid)
        .Select(x =>
        {
            var rawMembers = x.ToList();
            var fId = x.First().Fid;
            var fName = x.First().FamilyName;
            var members = rawMembers.Select(y => new Person 
            {
                Id = y.Id,
                Name = y.ChildName
            });
            return new FamilyModel
            {
                Id = fId,
                FamilyName = fName,
                Members = members.ToList()
            };
        });
        //Whatever else you want to do here
    }
}

1. Consider using Dappper. It is a great ORM that makes accessing data from database really easy. It's designed to work with SQL Server, but I've had success using Oracle too, and most other RMDBS systems will probably work.
2. Consider using Slapper. If you have control over your stored procedure, this can reduce a lot of the boilerplate code below.
3. If you use Dapper (I hope you do), you can play around with C# dynamic objects, or you can create a POCO to help get some type enforcement on your code.
4. Understand if you care about reference equality. The code I provided below does not enforce reference equality of objects. Reference equality, in my experience, doesn't buy you much and is a pain to enforce.