.NET[C#]Dapper中如何处理嵌套类的映射和查询问题？

问题描述

比如有如下两个实体类：

public class Course{
   public string Title{get;set;}
   public IList<Location> Locations {get;set;}
   ...
}

public class Location{
   public string Name {get;set;}
   ...
}

现要求查询一个 Course 集合并包含 Locations，应该如何实现？

方案一

查询出 Course 集合

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

相询关联的 Location 集合：

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

最后，将 course 与 locations 组合到一起。

注：此方案仅适合于数据量少的情况下，如果数据量比较大，比如大于1W可以考虑使用 Dapper 的 QueryMultiple 方法

方案二

使用 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, 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;

方案四

var coursesWithLocations = 
       conn.Query<Course, Location, Course>(@"
            SELECT c.*, l.*
            FROM Course c
            INNER JOIN Location l ON c.LocationId = l.Id                    
            ", (course, location) => {
                 if (course.Locations == null) 
                     course.Locations = new List<Location>();
                 course.Locations.Add(location); 
                 return course;
             }).AsQueryable();