public async Task <PadSeriesReadModel?> ReadById(Guid id) { using (var conn = OpenConnection()) { using (var reader = await conn.QueryMultipleAsync( @" select * from pad_series ps join brands b on ps.brand_id = b.id where ps.id = @Id; select * from pad_sizes where pad_series_id = @Id; select pi.* from pad_images pi join pads p on pi.pad_id = p.id where p.pad_series_id = @Id; select p.* from pads p where pad_series_id = @Id; select po.* from pad_options po left join pads p on po.pad_id = p.id where pad_series_id = @Id; select po.id as pad_option_id, pn.* from part_numbers pn join pad_option_part_numbers popn on pn.id = popn.part_number_id join pad_options po on po.id = popn.pad_option_id join pads p on po.pad_id = p.id where p.pad_series_id = @Id; ", new { Id = id } )) { var series = reader.Read <PadSeriesRow, BrandRow, PadSeriesReadModel>( (ps, b) => new PadSeriesReadModel( ps.Id, ps.Name, new BrandReadModel( b.Id, b.Name ), ps.PolisherTypes.ToList() )).ElementAtOrDefault(0); if (series == null) { return(null); } series.Sizes.AddRange(reader.Read <PadSizeRow>() .Select(s => new PadSizeReadModel( s.Id, new MeasurementReadModel(s.DiameterAmount, s.DiameterUnit), s.ThicknessAmount != null ? new MeasurementReadModel(s.ThicknessAmount ?? 0, s.ThicknessUnit !) : null ))); var images = reader.Read <(Guid PadId, Guid ImageId)>(); var keyValues = new List <KeyValuePair <Guid, PadReadModel> >(); var rawPads = reader.Read(); foreach (var pad in rawPads) { Guid?imageId = images.Where(i => i.PadId == pad.id).FirstOrDefault().ImageId; if (imageId == Guid.Empty) { imageId = null; } keyValues.Add(new KeyValuePair <Guid, PadReadModel>( pad.id, new PadReadModel( pad.id, pad.name, ((PadCategoryBitwise)pad.category).ToList(), pad.material, pad.texture, pad.color, pad.has_center_hole, imageId, new List <PadOptionReadModel>() ) )); } var pads = new Dictionary <Guid, PadReadModel>(keyValues); var options = reader.Read <PadOptionRow>(); var optionDict = new Dictionary <Guid, PadOptionReadModel>(); foreach (var opt in options) { PadReadModel?pad; if (pads.TryGetValue(opt.PadId, out pad)) { var po = new PadOptionReadModel(opt.Id, opt.PadSizeId); pad.Options.Add(po); optionDict.Add(opt.Id, po); } } var partNumbers = reader.Read <Guid, PartNumberRow, (Guid PadOptionId, PartNumberRow PartNumber)>((id, pn) => (id, pn)); foreach (var partNumber in partNumbers) { PadOptionReadModel?option; if (optionDict.TryGetValue(partNumber.PadOptionId, out option)) { option.PartNumbers.Add(new PartNumberReadModel(partNumber.PartNumber.Id, partNumber.PartNumber.Value, partNumber.PartNumber.Notes)); } } series.Pads.AddRange(pads.Values); return(series); } } }
public async Task <PagedCollection <PadSeriesReadModel> > ReadAll(PagingOptions paging) { using (var conn = OpenConnection()) { // Pull in the parent records first var series = await conn.QueryAsync <PadSeriesRow, BrandRow, PadSeriesReadModel>( @" select * from pad_series ps join brands b on ps.brand_id = b.id order by b.name limit @Limit offset @Offset; ", (ps, b) => new PadSeriesReadModel( ps.Id, ps.Name, new BrandReadModel( b.Id, b.Name ), ps.PolisherTypes.ToList() ), new { Limit = paging.PageSize, Offset = paging.Offset } ); var seriesLookup = new Dictionary <Guid, PadSeriesReadModel>(series.Select(p => new KeyValuePair <Guid, PadSeriesReadModel>(p.Id, p))); // Now get the rest using (var reader = await conn.QueryMultipleAsync( @" select count(*) from pad_series; select * from pad_sizes where pad_series_id = any(@Series); select pi.* from pad_images pi join pads p on pi.pad_id = p.id where pad_series_id = any(@Series); select p.* from pads p where pad_series_id = any(@Series) group by p.id order by name; select po.* from pad_options po left join pads p on po.pad_id = p.id; select po.id as pad_option_id, pn.* from part_numbers pn join pad_option_part_numbers popn on pn.id = popn.part_number_id join pad_options po on po.id = popn.pad_option_id; " , new { Series = series.Select(s => s.Id).ToArray() // We only want series that we got back. })) { var totalCount = reader.ReadFirst <int>(); var sizes = reader.Read <PadSizeRow>(); foreach (var size in sizes) { PadSeriesReadModel?s; if (seriesLookup.TryGetValue(size.PadSeriesId, out s)) { s.Sizes.Add(new PadSizeReadModel( size.Id, new MeasurementReadModel(size.DiameterAmount, size.DiameterUnit), size.ThicknessAmount != null ? new MeasurementReadModel(size.ThicknessAmount ?? 0, size.ThicknessUnit !) : null ) ); } } var images = reader.Read <(Guid PadId, Guid ImageId)>(); var pads = new Dictionary <Guid, PadReadModel>(); var rawPads = reader.Read(); foreach (var raw in rawPads) { Guid?imageId = images.Where(i => i.PadId == raw.id).FirstOrDefault().ImageId; if (imageId == Guid.Empty) { imageId = null; } var pad = new PadReadModel( raw.id, raw.name, ((PadCategoryBitwise)raw.category).ToList(), raw.material, raw.texture, raw.color, raw.has_center_hole, imageId, new List <PadOptionReadModel>() ); pads.Add(pad.Id, pad); PadSeriesReadModel?s; if (seriesLookup.TryGetValue(raw.pad_series_id, out s)) { s !.Pads.Add(pad); } } var options = reader.Read <PadOptionRow>(); var optionDict = new Dictionary <Guid, PadOptionReadModel>(); foreach (var opt in options) { PadReadModel?pad; if (pads.TryGetValue(opt.PadId, out pad)) { var po = new PadOptionReadModel(opt.Id, opt.PadSizeId); pad.Options.Add(po); optionDict.Add(opt.Id, po); } } var partNumbers = reader.Read <Guid, PartNumberRow, (Guid PadOptionId, PartNumberRow PartNumber)>((id, pn) => (id, pn)); foreach (var partNumber in partNumbers) { PadOptionReadModel?option; if (optionDict.TryGetValue(partNumber.PadOptionId, out option)) { option.PartNumbers.Add(new PartNumberReadModel(partNumber.PartNumber.Id, partNumber.PartNumber.Value, partNumber.PartNumber.Notes)); } } return(new PagedCollection <PadSeriesReadModel>(new Paging(paging, totalCount), seriesLookup.Values)); } } }