我目前正在使用实体框架来访问我的数据库,但我想看看Dapper。我有这样的类:
public class Course{
public string Title{get;set;}
public IList<Location> Locations {get;set;}
...
}
public class Location{
public string Name {get;set;}
...
}
因此,一门课程可以在多个地点授课。实体框架会为我进行映射,因此我的课程对象会填充一个位置列表。我该如何使用Dapper来做这件事,这是可能的,还是我必须在几个查询步骤中完成它?
发布于 2013-07-19 22:33:10
或者,您可以将一个查询与查找一起使用:
var lookup = new Dictionary<int, Course>();
conn.Query<Course, Location, Course>(@"
SELECT c.*, l.*
FROM Course c
INNER JOIN Location l ON c.LocationId = l.Id
", (c, l) => {
Course course;
if (!lookup.TryGetValue(c.Id, out course))
lookup.Add(c.Id, course = c);
if (course.Locations == null)
course.Locations = new List<Location>();
course.Locations.Add(l); /* Add locations to course */
return course;
}).AsQueryable();
var resultList = lookup.Values;
发布于 2017-02-23 23:27:19
我知道我来晚了,但还有另一个选择。你可以在这里使用QueryMultiple。如下所示:
var results = cnn.QueryMultiple(@"
SELECT *
FROM Courses
WHERE Category = 1
ORDER BY CreationDate
;
SELECT A.*
,B.CourseId
FROM Locations A
INNER JOIN CourseLocations B
ON A.LocationId = B.LocationId
INNER JOIN Course C
ON B.CourseId = B.CourseId
AND C.Category = 1
");
var courses = results.Read<Course>();
var locations = results.Read<Location>(); //(Location will have that extra CourseId on it for the next part)
foreach (var course in courses) {
course.Locations = locations.Where(a => a.CourseId == course.CourseId).ToList();
}
发布于 2014-02-14 22:16:44
少了点什么。如果没有在SQL查询中指定Locations
中的每个字段,则无法填充对象Location
。看一看:
var lookup = new Dictionary<int, Course>()
conn.Query<Course, Location, Course>(@"
SELECT c.*, l.Name, l.otherField, l.secondField
FROM Course c
INNER JOIN Location l ON c.LocationId = l.Id
", (c, l) => {
Course course;
if (!lookup.TryGetValue(c.Id, out course)) {
lookup.Add(c.Id, course = c);
}
if (course.Locations == null)
course.Locations = new List<Location>();
course.Locations.Add(a);
return course;
},
).AsQueryable();
var resultList = lookup.Values;
在查询中使用l.*
,我得到了位置列表,但没有数据。
https://stackoverflow.com/questions/7508322
复制相似问题