Loading related objects in memory (without an ORM)

Question

I am using ADO.NET to read a bunch of data from the database into in-memory objects.

This is my domain model:

// Question.cs
public class Question
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public IEnumerable<Tag> Tags { get; set; }
}

// Tag.cs
public class Tag 
{
    public int ID { get; set; }
    public string Name { get; set; }
}

On retrieving the list of Questions, I would like to fetch the related tags for each question. I am able to do this as follows:

// QuestionRepository.cs

public IList<Question> FindAll()
{
    var questions = new List<Question>();

    using (SqlConnection conn = DB.GetSqlConnection())
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "select * from questions";

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Question question = new Question();
                // Populate the question object using reader
                question.Load(reader);

                questions.Add(question);
            }
            reader.Close();
        }
     }
    return questions;
}


// Question.cs
public void Load(SqlDataReader reader)
{
    ID = int.Parse(reader["ID"].ToString());
    Title = reader["Title"].ToString();
    Description = reader["Description"].ToString();

    // Use Tag Repository to find all the tags for a particular question
    Tags = tagRepository.GetAllTagsForQuestionById(ID); 
}

    return questions;
}

// TagRepository.cs
public List<Tag> GetAllTagsForQuestionById(int id)
{
    List<Tag> tags = new List<Tag> ();
    // Build sql query to retrive the tags
    // Build the in-memory list of tags 
    return tags;
}

My question is, are there any best practices/patterns for fetching related objects from the database?

Most of the SO questions I came across for loading related data provide the solution for entity framework. There is no answer for this duplicate question.

Even though my code works, I would like to know other ways to do the same. The closest explanation I came across that's targeting my particular problem was Martin Fowler's Lazy Load pattern, which I believe, will result in following implementation:

public class Question
{
    private TagRepository tagRepo = new TagRepository();
    private IList<Tag> tags;

    public int ID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public IEnumerable<Tag> Tags {
        get
        {
            if (tags == null)
            {
                tags = tagRepo.GetAllTagsForQuestionById(ID);
            }
            return tags;
        }
    }  
}

Are there any other alternatives?

Transudate

There is no reason to do this manually. I would never recommend that over Dapper (since it elegantly handles things like DBNull). – Rustic

You can use any ORM (EF, Dapper, NHibernate, etc)... they simplify the DB access for you... Lazy loading can simply be turned On/Off in EF, that should not be a concern for you... if you don't want to use any of these ORMs (and I am not sure why you would want to do that?) you can use raw sql using ADO.NET Entity Framework. Also please note that you are asking for recommendation here... which is an Off Topic subject – Stopcock

Answer 1

If you are insisting on doing this in ADO.Net, then I would suggest using a little trick with anonymous types, LINQ, and Enumerable.Range(0,0).

First, you need to create a list of an anonymous type (or just create an actual class that maps back to your SQL statement)

var data = Enumerable.Range(0, 0).Select(x => new
{
    QestionId = 0,
    Title = "Question.Title",
    Description = "Question.Description",
    TagId = 0,
    Name = "Tag.Name"
}).ToList();

Next is where you do your ADO.Net stuff query the database and get the results.

The key thing here is to write a Query that returns all of the data you're looking for in one query.

using (var conn = GetConnection())
{
    using (var cmd = conn.CreateCommand())
    {
        //Construct a valid SQL statement that joins questions to tags
        cmd.CommandText = "SELECT q.*, t.* FROM questions q JOIN tags t ON 1 = 1";

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                data.Add(new
                {
                    QestionId = reader.IsDBNull(0) ? 0 : int.TryParse(reader.GetValue(0).ToString(), out var qId) ? qId : 0,
                    Title = reader.IsDBNull(1) ? string.Empty : reader.GetValue(1).ToString(),
                    Description = reader.IsDBNull(2) ? string.Empty : reader.GetValue(2).ToString(),
                    TagId = reader.IsDBNull(3) ? 0 : int.TryParse(reader.GetValue(3).ToString(), out var tId) ? tId : 0,
                    Name = reader.IsDBNull(4) ? string.Empty : reader.GetValue(4).ToString()
                });
            }
        }
    }
}

Now that you have your list fully populated with all of the rows, you just need to transform them back into the object you're looking for.

var questions = data.GroupBy(x => new {x.QestionId, x.Title, x.Description}).Select(y => new Question
{
    Id = y.Key.QestionId,
    Title = y.Key.Title,
    Description = y.Key.Description,
    Tags = y.Select(z => new Tag
    {
        Id = z.TagId,
        Name = z.Name
    })
}).ToList();

Brittain