public async Task <IEnumerable <FlightSummaryViewModel> > GetAllFlights(
[FromServices] IQueryHandler <ListFlightsQuery, IEnumerable <FlightSummary> > queryHandler,
[FromServices] IMapper mapper)
{
var query = new ListFlightsQuery();
var flightSummaries = await queryHandler.HandleAsync(query);
var viewModels = flightSummaries.Select(mapper.Map <FlightSummaryViewModel>);
return(viewModels);
}
public async Task <IEnumerable <FlightSummary> > HandleAsync(ListFlightsQuery query)
{
const string statement = @"
SELECT
[Id],
JSON_VALUE([JsonDocument], '$.Capacity') AS [Capacity],
(
SELECT COALESCE(SUM([NumberOfBookedSeats]), 0)
FROM OPENJSON(JSON_QUERY([JsonDocument], '$.Bookings')) WITH (
[NumberOfBookedSeats] INT '$.NumberOfSeats'
)
) AS [NumberOfBookedSeats]
FROM [Flights]
";
var rows = await _sqlConnection.QueryAsync <FlightSummaryRow>(statement);
return(rows.Select(x => x.ToFlightSummary()));
}
