首页 / .NET / 正文

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

4930 发布于: 2018-01-08 读完约需8分钟

问题描述

比如有如下两个实体类:

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

最后,将 courselocations 组合到一起。

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

方案二

使用 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();

版权声明:本作品系原创,版权归码友网所有,如未经许可,禁止任何形式转载,违者必究。

上一篇: .NET[C#]Dapper中如何使用事务进行数据库的操作?

下一篇: [SQL Server]SQL Server修改已有数据表字段(列)的可空选项:从可空修改成不允许为空

本文永久链接码友网 » .NET[C#]Dapper中如何处理嵌套类的映射和查询问题?

分享扩散:

发表评论

登录用户才能发表评论, 请 登 录 或者 注册