如何使用Dapper映射嵌套对象的列表 [英] How do I map lists of nested objects with Dapper

问题描述

我目前正在使用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做到这一点，甚至有可能还是我必须在几个查询步骤中做到这一点?

解决方案

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()}
);

全部映射

将其留给读者，您将创建一些地图，并遍历包含位置的课程.

如果您的 解决方案

Dapper is not a full blown ORM it does not handle magic generation of queries and such.

For your particular example the following would probably work:

Grab the courses:

var courses = cnn.Query<Course>("select * from Courses where Category = 1 Order by CreationDate");

Grab the relevant mapping:

var mappings = cnn.Query<CourseLocation>(
   "select * from CourseLocations where CourseId in @Ids", 
    new {Ids = courses.Select(c => c.Id).Distinct()});

Grab the relevant locations

var locations = cnn.Query<Location>(
   "select * from Locations where Id in @Ids",
   new {Ids = mappings.Select(m => m.LocationId).Distinct()}
);

Map it all up

Leaving this to the reader, you create a few maps and iterate through your courses populating with the locations.

Caveat the in trick will work if you have less than 2100 lookups (Sql Server), if you have more you probably want to amend the query to select * from CourseLocations where CourseId in (select Id from Courses ... ) if that is the case you may as well yank all the results in one go using QueryMultiple

这篇关于如何使用Dapper映射嵌套对象的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！