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