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
.
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.
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.