将相关对象加载到内存中(无ORM) [英] Loading related objects in memory (without an ORM)

问题描述

我正在使用ADO.NET从数据库中读取一堆数据到内存中的对象中.

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

这是我的域模型:

// 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?

我在加载相关数据时遇到的大多数SO问题都为实体框架提供了解决方案.此重复问题没有答案.

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.

即使我的代码有效，我也想知道其他方法来做到这一点.针对我的特定问题，我遇到的最接近的解释是Martin Fowler的惰性加载模式，相信，将导致以下实施:

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;
        }
    }  
}

还有其他选择吗?

推荐答案

如果您坚持要在ADO.Net中这样做，那么我建议您对匿名类型，LINQ和Enumerable.Range(0， 0).

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).

首先，您需要创建一个匿名类型的列表(或仅创建一个映射回您的SQL语句的实际类)

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();

接下来是您在其中查询数据库并获取结果的ADO.Net内容.

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();

这篇关于将相关对象加载到内存中(无ORM)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！