Dapper中的Multi-Mapper一对多关系 [英] Multi-Mapper in Dapper one to many relations
问题描述
我正试图以一对多的关系获取数据库的价值
i像这样的对象
学生
[Table（ Student）]
公共班级学生：学生
{
public int Id {get;组; }
公用字符串姓氏{get;组; }
公用字串FirstMidName {get;组; }
公共DateTime？ EnrollmentDate {获取；组; }
[Write（false）]
public IEnumerable< Enrollment>报名{组; }
}
报名
[Table（ Enrollment）]
公共类注册
{
public int Id {get;组; }
public int CourseId {get;组; }
public int StudentId {get;组; }
公用字串Grade {get;组; }
公共虚拟课程课程{get;组; }
个公共虚拟学生Student {组; }
}
课程
[Table（ Course）]
公共课程课程
{
public int Id {get;组; }
公用字串Title {get;组; }
public int积分{get;组; }
公共虚拟IEnumerable< Enrollment>报名{组; }
}
这是我的代码
_connection = Connect.GetOpenConnection（）;
const字符串查询= @ SELECT * FROM学生stu，其中id = @id +
SELECT * FROM从注册enr，其中enr.StudentId in（SELECT id FROM Student stu，其中id = @id） +
SELECT * FROM Course cou WHERE cou.Id in（从注册中的SELECT CourseId，其中StudentId = @id）；
var结果= _connection.QueryMultiple（query，new {id = id}）
.Map< Student，Enrollment，Course，int>（
学生=>学生。 Id，
入学=> enrollment.StudentId，
课=> course.Id，
（学生，入学）=> student.Enrollments =入学，
（学生，course）=> student.Enrollments.ForEach（s => courses.ForEach（x => s.Course.Title = x.Title））
）.FirstOrDefault（）;
按照说明
更新1
我注意到我在这里得到空值，它没有进入if语句内
IEnumerable< TSecond>第二;
if（secondMap.TryGetValue（firstKey（item），out second））
{
addSecond（item，second）;
}
更新2
我解决了这里的问题
var ctr = 0;
var mapping2 = conn.QueryMultiple（query，new {id}）
.Map< Student，Enrollment，Course，int>（
student => student.StudentId，
入学人数=> ctr =入学人数.StudentId，
course => course.CourseId = ctr，
（（学生，入学人数）=> {学生。 b（（学生，课程）=> course.ToList（）。ForEach（s => student.Enrollments.ToList（）。ForEach（x => x.Course =新课程
{
标题= s。标题
}））））;
注意我添加了 ctr 注册中。 CourseId 的值=> ctr = enrollment.StudentId
好吧，现在我面临另一个问题，如何获取课程的价值
，这是我的代码
（（（学生，课程）=> course.ToList（）。ForEach（s => student.Enrollments.ToList（）。ForEach（x => x.Course =新课程
{
Title = s.Title
}））
我只得到最后一个值
这是我解决问题的方式
正如上面的 UPDATE 2 详细信息一样，我添加了一个变量 ctr 以获取 CourseId 从注册并传递给课程以获取正确的值，此后，我面临另一个问题从 Student.Enrollment.Course 传递 Course 的值，这就是我的计算方法。
我创建一个变量 var enrollLst = new List< Enrollment>（）; 保留第一个操作中入学人数的值
（（ ，人数）=>
{
enrollLst = enrollments.ToList（）;
}），
第二次行动
（（学生，课程）=>
{
var ctrId = 0;
courses.ToList（）。ForEach（cour => ;
{
if（cour！= null）
{
enrollLst [ctrId] .Course = cour;
ctrId ++;
}
else
{
enrollLst [ctrId] .Course = null;
ctrId ++;
}
}）;
student.Enrollments = enrollLst;
}））。FirstOrDefault（）;
下面是我的整个代码
_connection = Connect.GetOpenConnection（）;
const字符串查询=选择*来自学生stu，其中StudentId = @id +
选择*来自注册snr，其中enr.StudentId in（SELECT StudentId from学生stu，其中Studentid = @id） +
SELECT * FROM Course cou WHERE cou.CourseId in（从注册中选择SELECT CourseId，其中StudentId = @id）;
var enrollLst = new List< Enrollment>（）;
var ctr = 0;
var结果= _connection.QueryMultiple（query，new {id}）
.Map< Student，Enrollment，Course，int>（
学生=> Student.StudentId，
入学==> ctr =入学.StudentId，
课程=>课程.CourseId = ctr，
（（学生，入学人数）= >>
{
enrollLst =入学人数。 ToList（）;
}），
（（学生，课程）=>;
{
var ctrId = 0;
course.ToList（）。ForEach（ cour =>
{
if（cour！= null）
{
enrollLst [ctrId] .Course = cour;
ctrId ++;
}
else
{
enrollLst [ctrId]。课程=空；
ctrId ++;
}
}）；
学生。报名人数= enrollLst;
}））。FirstOrDefault（）;
GridReader具有映射器，扩展了3级层次结构，如此处
公共静态IEnumerable< TFirst>映射< TFirst，TSecond，TThird，TKey>
（
此SqlMapper.GridReader阅读器，
Func< TFirst，TKey> firstKey，
Func< TSecond，TKey> secondKey，
Func< TThird，TKey> thirdKey ，
Action< TFirst，IEnumerable< TSecond> addSecond，
Action< TFirst，IEnumerable< TThird> addThird
）
{
var result = reader .Read< TFirst>（）。ToList（）;
var secondMap =阅读器
.Read< TSecond>（）
.GroupBy（secondKey）
.ToDictionary（g => g.Key，g => ; g.AsEnumerable（））;
var thirdMap =阅读器
.Read< TThird>（）
.GroupBy（thirdKey）
.ToDictionary（g => g.Key，g => ; g.AsEnumerable（））;
foreach（结果中的可变项）
{
IEnumerable< TSecond>第二;
if（secondMap.TryGetValue（firstKey（item），out second））
{
addSecond（item，second）;
}
IEnumerable< TThird>第三;
if（thirdMap.TryGetValue（firstKey（item），out third）））
{
addThird（item，third）;
}
}
返回结果。ToList（）;
}
I am trying to get the value of my database with a relation one to many i have my object like this
Student
[Table("Student")]
public class Student : IStudent
{
public int Id { get; set; }
public string Lastname { get; set; }
public string FirstMidName { get; set; }
public DateTime? EnrollmentDate { get; set; }
[Write(false)]
public IEnumerable<Enrollment> Enrollments { get; set; }
}
Enrollment
[Table("Enrollment")]
public class Enrollment
{
public int Id { get; set; }
public int CourseId { get; set; }
public int StudentId { get; set; }
public string Grade { get; set; }
public virtual Course Course { get; set; }
public virtual Student Student { get; set; }
}
Course
[Table("Course")]
public class Course
{
public int Id { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public virtual IEnumerable<Enrollment> Enrollments { get; set; }
}
Here is my dapper code
_connection = Connect.GetOpenConnection();
const string query = @"SELECT * FROM Student stu where id = @id " +
"SELECT * FROM Enrollment enr WHERE enr.StudentId in (SELECT id FROM Student stu where id = @id) " +
"SELECT * FROM Course cou WHERE cou.Id in (SELECT CourseId from Enrollment where StudentId = @id)";
var result = _connection.QueryMultiple(query, new { id = id })
.Map<Student, Enrollment, Course, int>(
student => student.Id,
enrollment => enrollment.StudentId,
course=>course.Id,
(student, enrollments) => student.Enrollments = enrollments ,
(student, courses) => student.Enrollments.ForEach(s=>courses.ForEach(x=>s.Course.Title = x.Title) )
).FirstOrDefault();
as per instruction Here All i have to do is to extend 3 level hierarchy but i cant make it work
here is my GridReader with mapper
public static IEnumerable<TFirst> Map<TFirst, TSecond, TThird, TKey>
(
this SqlMapper.GridReader reader,
Func<TFirst, TKey> firstKey,
Func<TSecond, TKey> secondKey,
Func<TThird, TKey> thirdKey,
Action<TFirst, IEnumerable<TSecond>> addSecond,
Action<TFirst, IEnumerable<TThird>> addThird
)
{
var result = reader.Read<TFirst>().ToList();
var secondMap = reader
.Read<TSecond>()
.GroupBy(s=>secondKey(s))
.ToDictionary(g => g.Key, g => g.AsEnumerable());
var thirdMap = reader
.Read<TThird>()
.GroupBy(t => thirdKey(t))
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in result)
{
IEnumerable<TThird> third;
if (thirdMap.TryGetValue(firstKey(item), out third))
{
addThird(item, third);
}
IEnumerable<TSecond> second;
if (secondMap.TryGetValue(firstKey(item), out second))
{
addSecond(item, second);
}
}
return result.ToList();
}
when i run my app here is the result with the id of 1 notice that the Course title have no value. i hope you can help me thank you.
UPDATE 1
I notice that i am getting the null value here, it doesnt get inside the if statement
IEnumerable<TSecond> second;
if (secondMap.TryGetValue(firstKey(item), out second))
{
addSecond(item, second);
}
UPDATE 2
I solve the problem here is what i do
var ctr = 0;
var mapped2 = conn.QueryMultiple(query, new {id})
.Map<Student, Enrollment, Course, int>(
student => student.StudentId,
enrollment => ctr = enrollment.StudentId,
course=>course.CourseId = ctr,
((student, enrollments) => { student.Enrollments = enrollments; }),
((student, courses) => courses.ToList().ForEach(s=> student.Enrollments.ToList().ForEach(x=>x.Course = new Course
{
Title = s.Title
}))));
notice that i added ctr to get the value of CourseId in enrollment => ctr = enrollment.StudentId.
Ok now I face another problem, how to get the value of courses
here is my code
((student, courses) => courses.ToList().ForEach(s=> student.Enrollments.ToList().ForEach(x=>x.Course = new Course { Title = s.Title }))
i am only getting the last value
Here is how i solve my problem
As the above UPDATE 2 details i added a variable ctr to get the value of CourseId from Enrollment and pass to Course to get the right value. After that i face another problem how to pass the value of Course from Student.Enrollment.Course here's how i figure it out.
I create a variable var enrollLst = new List<Enrollment>(); to hold the value of Enrollment from the first action
((student, enrollments) =>
{
enrollLst = enrollments.ToList();
}),
Second Action
((student, courses) =>
{
var ctrId = 0;
courses.ToList().ForEach(cour =>
{
if (cour != null)
{
enrollLst[ctrId].Course = cour;
ctrId++;
}
else
{
enrollLst[ctrId].Course = null;
ctrId++;
}
});
student.Enrollments = enrollLst;
})).FirstOrDefault();
below is my whole code
_connection = Connect.GetOpenConnection();
const string query = "SELECT * from Student stu where StudentId = @id " +
"SELECT * from Enrollment enr where enr.StudentId in ( SELECT StudentId from Student stu where Studentid = @id) " +
"SELECT * FROM Course cou WHERE cou.CourseId in (SELECT CourseId from Enrollment where StudentId = @id)";
var enrollLst = new List<Enrollment>();
var ctr = 0;
var result = _connection.QueryMultiple(query, new { id })
.Map<Student, Enrollment, Course, int>(
student => student.StudentId,
enrollment => ctr = enrollment.StudentId,
course => course.CourseId = ctr,
((student, enrollments) =>
{
enrollLst = enrollments.ToList();
}),
((student, courses) =>
{
var ctrId = 0;
courses.ToList().ForEach(cour =>
{
if (cour != null)
{
enrollLst[ctrId].Course = cour;
ctrId++;
}
else
{
enrollLst[ctrId].Course = null;
ctrId++;
}
});
student.Enrollments = enrollLst;
})).FirstOrDefault();
GridReader with mapper extended with 3 level hierarchy as instructed here
public static IEnumerable<TFirst> Map<TFirst, TSecond, TThird, TKey>
(
this SqlMapper.GridReader reader,
Func<TFirst, TKey> firstKey,
Func<TSecond, TKey> secondKey,
Func<TThird, TKey> thirdKey,
Action<TFirst, IEnumerable<TSecond>> addSecond,
Action<TFirst, IEnumerable<TThird>> addThird
)
{
var result = reader.Read<TFirst>().ToList();
var secondMap = reader
.Read<TSecond>()
.GroupBy(secondKey)
.ToDictionary(g => g.Key, g => g.AsEnumerable());
var thirdMap = reader
.Read<TThird>()
.GroupBy(thirdKey)
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in result)
{
IEnumerable<TSecond> second;
if (secondMap.TryGetValue(firstKey(item), out second))
{
addSecond(item, second);
}
IEnumerable<TThird> third;
if (thirdMap.TryGetValue(firstKey(item), out third))
{
addThird(item, third);
}
}
return result.ToList();
}
这篇关于Dapper中的Multi-Mapper一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
