어떻게 dapper를 사용하여 다른 유형의 목록이 포함 된 유형을 반환 할 수 있습니까?

c# dapper

문제

dapper를 사용하여 하나의 쿼리에서 모든 SpaceShipsSightings 을 어떻게 선택합니까?

나는 다음과 같은 물건을 가지고있다.

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)

다음 스키마를 사용합니다.

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)

나는 이것과 같은 관련 Sightings 포함하여 SpaceShip 의 목록을 선택하기 위해 dapper를 사용하려고합니다 :

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)

그러나 결과는 빈 SightingsSpaceShips 의 목록입니다.

최신 정보

Marc의 QueryMultiple 제안을 사용하는 것이 더 쉬워 보였습니다. 이 작업을 수행하기 위해 public int SpaceShipId {get;set;} Sighting 클래스에 public int SpaceShipId {get;set;} 를 추가해야했습니다. 나는 이것으로 끝났다 :

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)

참고 : 분명히 각 쿼리의 where 절에 부모 ID를 포함하려고합니다.

수락 된 답변

첫째, <SpaceShip, Sighting, SpaceShip> 을 사용하고 고유 한 ID 관리자 (사전 읽기)를 작성하여 중복 된 데이터를 고유하게 만들어야합니다. 의사 코드 :

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

shipsDictionary<int, SpaceShip> 또는 유사합니다. 이것이 일반적인 경우라고 생각한다면, 우리가 inbuilt 옵션으로 고려할 수있는 확실한 것입니다.

하나! 이 경우 많은 추가 열이 필요할 수 있습니다. 개인적으로는 QueryMultiple 하여 여기에서 다중 결과 쿼리를 고려하고 두 가지를 함께 후 처리로 연결해야합니다.




아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.