如何使用 Dapper 映射嵌套对象列表

小开

最佳答案

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 服务器) ，那么使用 in技巧就可以了。如果你的查询次数多于 2100，那么你可能需要将查询修改为 select * from CourseLocations where CourseId in (select Id from Courses ... )。如果是这种情况，那么你可以使用 QueryMultiple一次性地将所有的结果提取出来

小开

或者，您可以在查找时使用一个查询:

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;

看这里 https://www.tritac.com/blog/dappernet-by-example/

小开

少了点什么。如果在 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.*，我得到了位置列表，但是没有数据。

小开

我知道我真的迟到了，但还有一个选择。您可以在这里使用 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();
}

小开

不知道是否有人需要它，但我有动态版本的它没有模型的快速和灵活的编码。

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;

小开

不需要 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();

小开

对不起，参加聚会迟到了(像往常一样)。对我来说，就性能和可读性而言，使用 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;
}

小开

还有一种使用 JSON 结果的方法。即使接受的答案和其他解释很好，我只是想到了另一种方法来获得结果。

创建一个存储过程或 select qry 以返回 json 格式的结果。然后将 result 对象 Deserialize 为必需的类格式。请通过示例代码。

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
}

这是另一个思考过程，请复习一下。