How can I use dapper to return a type that contains a list of some other type?

Question

How can I use dapper to select all SpaceShips and their Sightings in one query?

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 schema:

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'm trying to use dapper to select a list of SpaceShip including their associated 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 Marc's suggestion of QueryMultiple and wire it myself. To make this work I had to add 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: You'll obviously want to include parent id in the where clause for each query.

Answer 1

Firstly, you would have to use <SpaceShip, Sighting, SpaceShip>, and write your own identity manager (read: a dictionary) to make the duplicated 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 a Dictionary<int, SpaceShip> or similar. If you think this is a common case, it is certainly something we could consider as an inbuilt option.

However! this may require a lot of extra columns. Personally I'd be tempted to consider a multi-result query here, with QueryMultiple and tie the two together as post-processing.