-1

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?

  • 1
    Your link above shows you already found a good reference to this question: Fowler's book Patterns of Enterprise Application Architecture. Unfortunately, questions which require a whole book as an answer are definitely too broad for the Q&A format of this site, so don't be disappointed if your question gets closed by the community for exactly that reason. – Doc Brown Jan 13 at 8:16
1

If you always need all tags for each question, then lazy loading is not the best approach.

Instead, I would join the tables directly in SQL. Something like:

cmd.CommandText = "select * from questions 
                       inner join questiontags on question.id = questiontags.questionid
                       inner join tags on questiontags.tagid = tags.tagid";

Now you can create your question object and all tag objects inside a single database call.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.