How do I map lists of nested objects with Dapper

Question

I'm currently using Entity Framework for my db access but want to have a look at Dapper. I have classes like this:

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

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

So one course can be taught at several locations. Entity Framework does the mapping for me so my Course object is populated with a list of locations. How would I go about this with Dapper, is it even possible or do I have to do it in several query steps?

Answer 1

Alternatively, you can use one query with a lookup:

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;

See here https://www.tritac.com/blog/dappernet-by-example/

Answer 2

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

Answer 3

No need for lookup Dictionary

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

Answer 4

I know I'm really late to this, but there is another option. You can use QueryMultiple here. Something like this:

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

Answer 5

Sorry to be late to the party (like always). For me, it's easier to use a Dictionary, like Jeroen K did, in terms of performance and readability. Also, to avoid header multiplication across locations, I use Distinct() to remove potential dups:

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

Answer 6

Something is missing. If you do not specify each field from Locations in the SQL query, the object Location cannot be filled. Take a look:

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;

Using l.* in the query, I had the list of locations but without data.

Answer 7

Not sure if anybody needs it, but I have dynamic version of it without Model for quick & flexible coding.

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;

Answer 8

There is another approach using the JSON result. Even though the accepted answer and others are well explained, I just thought about an another approach to get the result.

Create a stored procedure or a select qry to return the result in json format. then Deserialize the the result object to required class format. please go through the sample code.

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        
}

This is an another thought process. Please review the same.

Answer 9

Use GroupBy if you want to avoid dictionary. Something like

var result = await con.QueryAsync<Course, Location, Course>(joinQuery, (course, location) => 
      {
          course.Locations = [location];
          return course;
      }, splitOn: "LocationId")
      .GroupBy(course => course.Id)
      .Select(group => 
      {
          var course = group.First();
          course.Locations = group.SelectMany(course => course.Locations).ToList();
          return Course;
      }
      .ToList();