我目前使用Entity Framework进行数据库访问，但想看看Dapper。我的类看起来像这样：
public class Course{
public string Title{get;set;}
public IList<Location> Locations {get;set;}
...
}
public class Location{
public string Name {get;set;}
...
}
因此，可以在多个位置上授课。Entity Framework 会为我执行映射，因此我的 Course 对象会填充一个位置列表。对于 Dapper，我该如何处理这种情况？是否可能，或者我必须分几步查询来完成？
或者，您可以使用一个带有查找的查询：
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;
Dapper不是完整的ORM，它不处理查询和类似操作的神奇生成。
对于您的特定示例，以下内容可能有效：
var courses = cnn.Query<Course>("select * from Courses where Category = 1 Order by CreationDate");
var mappings = cnn.Query<CourseLocation>(
"select * from CourseLocations where CourseId in @Ids",
new {Ids = courses.Select(c => c.Id).Distinct()});
var locations = cnn.Query<Location>(
"select * from Locations where Id in @Ids",
new {Ids = mappings.Select(m => m.LocationId).Distinct()}
);
让读者来完成这个步骤，你需要创建一些地图并迭代你的课程，将位置填入其中。
注意：如果你的查询超过了2100条（对于Sql Server），使用in技巧可能无效，此时你可能需要修改查询语句为select * from CourseLocations where CourseId in (select Id from Courses ... )，如果是这种情况，最好一次性获取所有结果使用QueryMultiple
CourseLocations感到困惑，这是问题中从未提到的一个表/结构。OP只列出了2个实体 - 这只是假设可能存在一个中间表吗？ - PandaWood无需使用lookup字典
var coursesWithLocations =
conn.Query<Course, Location, Course>(@"
SELECT c.*, l.*
FROM Course c
INNER JOIN Location l ON c.LocationId = l.Id
", (course, location) => {
course.Locations = course.Locations ?? new List<Location>();
course.Locations.Add(location);
return course;
}).AsQueryable();
我知道我来晚了，但还有另外一个选择。您可以在此处使用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();
}
很抱歉总是迟到。对于我来说，使用Dictionary（像Jeroen K一样）在性能和可读性方面更加容易。此外，为了避免跨位置头部重复，我使用Distinct()来消除潜在的重复项：
string query = @"SELECT c.*, l.*
FROM Course c
INNER JOIN Location l ON c.LocationId = l.Id";
using (SqlConnection conn = DB.getConnection())
{
conn.Open();
var courseDictionary = new Dictionary<Guid, Course>();
var list = conn.Query<Course, Location, Course>(
query,
(course, location) =>
{
if (!courseDictionary.TryGetValue(course.Id, out Course courseEntry))
{
courseEntry = course;
courseEntry.Locations = courseEntry.Locations ?? new List<Location>();
courseDictionary.Add(courseEntry.Id, courseEntry);
}
courseEntry.Locations.Add(location);
return courseEntry;
},
splitOn: "Id")
.Distinct()
.ToList();
return list;
}
有些东西缺失了。如果您在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.*，我得到了位置列表但没有数据。
不确定是否有人需要，但我有一个动态版本，没有模型可以进行快速和灵活的编程。
var lookup = new Dictionary<int, dynamic>();
conn.Query<dynamic, dynamic, dynamic>(@"
SELECT A.*, B.*
FROM Client A
INNER JOIN Instance B ON A.ClientID = B.ClientID
", (A, B) => {
// If dict has no key, allocate new obj
// with another level of array
if (!lookup.ContainsKey(A.ClientID)) {
lookup[A.ClientID] = new {
ClientID = A.ClientID,
ClientName = A.Name,
Instances = new List<dynamic>()
};
}
// Add each instance
lookup[A.ClientID].Instances.Add(new {
InstanceName = B.Name,
BaseURL = B.BaseURL,
WebAppPath = B.WebAppPath
});
return lookup[A.ClientID];
}, splitOn: "ClientID,InstanceID").AsQueryable();
var resultList = lookup.Values;
return resultList;
还有一种使用JSON结果的方法。尽管已经有很好的解释，但我想到了另一种获取结果的方法。
创建一个存储过程或选择查询以JSON格式返回结果。然后将结果对象反序列化为所需的类格式。请查看示例代码。
using (var db = connection.OpenConnection())
{
var results = await db.QueryAsync("your_sp_name",..);
var result = results.FirstOrDefault();
string Json = result?.your_result_json_row;
if (!string.IsNullOrEmpty(Json))
{
List<Course> Courses= JsonConvert.DeserializeObject<List<Course>>(Json);
}
//map to your custom class and dto then return the result
}