我这里有两张桌子：

和

这些将映射到sql语句中，如下所示：

select project.Id, task.Id, task.ProjectId, task.Name, project.Name
from Project project 
left join Task task on project.Id = task.ProjectId 

其中返回以下内容：

现在，我想为此得到一个结构，就像实体框架将返回（我在json中显示，以使其更容易理解）：

{
  "datasets": [
    {
      "records": [
        {
          "fields": [
            {
              "Name": "Name",
              "Value": "Test-Project"
            },
            {
              "Name": "Id",
              "Value": 198
            }
          ],
          "datasets": [
            {
              "records": [
                {
                  "fields": [
                    {
                      "Name": "ProjectId",
                      "Value": 198
                    },
                    {
                      "Name": "Name",
                      "Value": "Task 1"
                    },
                    {
                      "Name": "Id",
                      "Value": 23
                    }
                  ]
                }
...

我尝试了SqlDataReader，但我不知道如何将它们分组...我在逻辑部分失败了...

await using SqlCommand cmd = new SqlCommand(sql, connection).SetParameters(parameters);

connection.Open();

Root root = new ();
DataSet dataSet = new ();
await using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
    while (reader.Read())
    {
        Record record = new ();

        for (int i = 0; i < reader.FieldCount; i++)
        {
            Field field = new ()
            {
                Name = _getColumnName(i),
                Value = reader[i]
            };

            record.DataFields.Add(field);
        }

        dataSet.Records.Add(record);
    }
}
root.DataAreas.Add(dataSet);

我唯一能得到的是一个包含所有数据集的平面列表：

{
  "datasets": [
    {
      "records": [
        {
          "fields": [
            {
              "Name": "Id",
              "Value": 23
            },
            {
              "Name": "Name",
              "Value": "Task 1"
            },
            {
              "Name": "Name",
              "Value": "Test-Project"
            },
            {
              "Name": "Id",
              "Value": 198
            },
            {
              "Name": "ProjectId",
              "Value": 198
            }
          ],
          "datasets": []
        }

有没有办法解决这个问题？