public async Task <PaginatedResponse <OrderModel> > Query(GetOrdersRequest request) { var parameters = new Dictionary <string, object>(); var where = new SqlWhereBuilder(request, _columns).Build(); var orderBy = new SqlOrderByBuilder($@"{nameof(OrderModel.Id)} ASC", _columns, request).Build(); var pagination = new SqlPaginationBuilder(request).Build(); var select = $@" SELECT DISTINCT {nameof(OrderModel.Id)}, {nameof(OrderModel.CarFrame)}, {nameof(OrderModel.Model)}, {nameof(OrderModel.LicensePlate)}, {nameof(OrderModel.DeliveryDate)} FROM {nameof(CarsContext.Orders)} {where.Sql} {orderBy} {pagination.Sql}"; var count = $@"SELECT COUNT(*) FROM {nameof(CarsContext.Orders)} O {where.Sql}"; parameters.AddRange(where.Parameters); parameters.AddRange(pagination.Parameters); var result = await _db.QueryMultipleAsync($"{select};{count}", parameters); return(new PaginatedResponse <OrderModel>(result.Read <OrderModel>(), result.ReadFirst <long>())); }
public async Task <PaginatedResponse <UserListItemResponseModel> > Query(GetUsersRequest request, CancellationToken cancellationToken) { var parameters = new Dictionary <string, object>(); var orderBy = new SqlOrderByBuilder($"U.[{nameof(User.Name)}] ASC", _columns, request).Build(); var pagination = new SqlPaginationBuilder(request).Build(); var gendersLeftJoin = $"LEFT JOIN [{nameof(DataBaseContext.Genders)}] G ON G.[{nameof(Gender.Id)}] = U.[{nameof(User.GenderId)}]"; var select = $@" SELECT U.[{nameof(User.Id)}] AS [{nameof(UserListItemResponseModel.Id)}], U.[{nameof(User.Email)}] AS [{nameof(UserListItemResponseModel.Email)}], U.[{nameof(User.Name)}] AS [{nameof(UserListItemResponseModel.Name)}], U.[{nameof(User.Birthdate)}] AS [{nameof(UserListItemResponseModel.Birthdate)}], U.[{nameof(User.GenderId)}] AS [{nameof(UserListItemResponseModel.GenderId)}], G.[{nameof(Gender.Name)}] AS [{nameof(UserListItemResponseModel.GenderName)}] FROM [{nameof(DataBaseContext.Users)}] U {gendersLeftJoin} {orderBy} {pagination.Sql}"; var count = $@" SELECT COUNT(U.[{nameof(User.Id)}]) FROM [{nameof(DataBaseContext.Users)}] U {gendersLeftJoin}"; parameters.AddRange(pagination.Parameters); var result = await WithConnection(async connection => { var queryResult = await connection.QueryMultipleAsync($"{select};{count}", parameters); return(new PaginatedResponse <UserListItemResponseModel>(queryResult.Read <UserListItemResponseModel>(), queryResult.ReadFirst <long>())); }, cancellationToken); return(result); }