How can I use dapper to return a type containing a list of another type?

Question

How to use dapper to select all SpaceShips and their Sightings in one request?

I have the following objects:

 public class SpaceShip { public int Id { get; set; } public string DriveType { get; set; } public List<Sighting> Sightings { get; set; } } public class Sighting { public int Id { get; set; } public double Lat { get; set; } public double Lon { get; set; } } 

With the following diagram:

 If Exists(Select * from sysobjects where name = 'Sightings') Drop Table Sightings If Exists(Select * from sysobjects where name = 'SpaceShips') Drop Table SpaceShips CREATE TABLE [dbo].[SpaceShips]( [Id] [int] IDENTITY(1,1) NOT NULL, [DriveType] [varchar](max) NOT NULL, CONSTRAINT [PK_SpaceShips] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO CREATE TABLE [dbo].[Sightings]( [Id] [int] IDENTITY(1,1) NOT NULL, [SpaceShipId] [int] NOT NULL, [Lat] [decimal](18, 0) NOT NULL, [Lon] [decimal](18, 0) NOT NULL, CONSTRAINT [PK_Sightings] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO ALTER TABLE [dbo].[Sightings] WITH CHECK ADD CONSTRAINT [FK_Sightings_SpaceShips] FOREIGN KEY([SpaceShipId]) REFERENCES [dbo].[SpaceShips] ([Id]) GO ALTER TABLE [dbo].[Sightings] CHECK CONSTRAINT [FK_Sightings_SpaceShips] GO Insert into SpaceShips (DriveType) Values ('X18-9'),('PV-276M') Insert into Sightings (SpaceShipId, Lat, Lon) Values (1, 10, 90), (1, 20, 80), (1, 30, 70), (1, 40, 60) Insert into Sightings (SpaceShipId, Lat, Lon) Values (2, 104, 64), (2, 105, 63), (2, 106, 62), (2, 107, 61) 

I am trying to use dapper to select a list of SpaceShip , including related Sightings , like this:

 using (var con = MuzakiFactory.OpenPortal()) { try { var sql = @"Select * From SpaceShips ship left join Sightings s on s.SpaceShipId = ship.id"; var result = con.Query<SpaceShip, List<Sighting>, SpaceShip> (sql, (ship, sightings) => { ship.Sightings = sightings; return ship; }); return result; } catch (Exception ex) { Captains.Log(ex); throw; } } 

But the result is a list of SpaceShips with empty Sightings .

Update

It seemed easier to use Mark's QueryMultiple and build it yourself. To do this, I had to add the public int SpaceShipId {get;set;} to my Sighting class. I ended up with this:

 var sql = @"Select * From SpaceShips; Select * from Sightings;"; using (var multi = con.QueryMultiple(sql)) { var ships = multi.Read<SpaceShip>().ToList(); var sightings = multi.Read<Sighting>().ToList(); foreach(var ship in ships) { ship.Sightings = new List<Sighting>(sightings.Where(x => x.SpaceShipId == ship.Id)); } return ships; } 

Note. . Obviously, you will want to include the parent id in the where clause for each request.

Answer 1

First, you will need to use <SpaceShip, Sighting, SpaceShip> and write your own identifier manager (read: dictionary) to make duplicate data unique. Pseudo Code:

 (ship, sighting) => { SpaceShip actualShip; if(!ships.TryGetValue(ship.Id, out actualShip)) { ships.Add(ship.Id, actualShip = ship); } actualShip.Sightings.Add(sighting); return actualShip; } 

where ships is Dictionary<int, SpaceShip> or similar. If you think that this is a common case, this is certainly what we can consider as a built-in option.

However! this may require many additional columns. Personally, I will be tempted to consider here a query with several results, with QueryMultiple and link them together as post-processing.