使用linq对ado.net查询出来dataset集合转换成对象(查询出来的数据结构为一对多)

 public async Task<IEnumerable<QuestionAllInfo>> GetAllQuestionByTypeIdAsync(int id)
        {
            string sql = "SELECT q.Id as questionId,i.Id as instanceId,q.name as questionName,q.Intention as questionIntention,i.`Name` as instancesName FROM `t_questions` q LEFT JOIN t_instances i on q.Id = i.QuestionId WHERE q.QuestionTypeId = @p1";
            MySqlParameter[] mySqlParameters = new MySqlParameter[] { new MySqlParameter { MySqlDbType = MySqlDbType.Int32, Value = id, ParameterName = "@p1" } };
            var dataSet = await SQLHelp.ExecuteQueryAsync(sql, mySqlParameters);
            var table = dataSet.Tables[0];
            List<Temp> temps = new List<Temp>();
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            foreach (DataRow row in table.Rows)
            {
                temps.Add(new Temp
                {
                    QuestionId = (int)row["questionId"],
                    QuestionIntention = row["questionIntention"].ToString(),
                    QuestionName = row["questionName"].ToString(),
                    InstanceId = IsIntDBNULL(row["instanceId"]),
                    InstanceName = row["instancesName"].ToString()
                }); ;
            }
            var result = temps.GroupBy(t => new { t.QuestionId, t.QuestionIntention, t.QuestionName })
                  .Select(question =>
              new QuestionAllInfo
              {
                  QuestionId = question.Key.QuestionId,
                  QuestionName = question.Key.QuestionName,
                  QuestionIntention = question.Key.QuestionIntention,
                  Instances = question.Select(q => new Instance
                  {
                      Id = q.InstanceId,
                      Name = q.InstanceName,
                      QuestionId = q.QuestionId
                  })
              });
            return result;
        }

要转换成的实体对象

class Temp
    {
        public int QuestionId { get; set; }
        public string QuestionIntention { get; set; }
        public string QuestionName { get; set; }
        public int? InstanceId { get; set; }
        public string InstanceName { get; set; }
    }


 public class QuestionAllInfo
    {
        public int QuestionId { get; set; }
        public string QuestionIntention { get; set; }
        public string QuestionName { get; set; }
        public IEnumerable<Instance> Instances { get; set; }
    }

 public class Instance 
    {
        public int Id { get; set; }
        public string Name { get; set; }      
        public int QuestionId { get; set; }
    }

 

posted @ 2019-10-30 14:53  an_blog  阅读(153)  评论(0编辑  收藏