private string SqlClauseForQueryingCatalogProducts(GetCatalogCategoryDetailRequest.CatalogProductSearchRequest searchRequest) { var fieldsDefinition = new Dictionary <string, string> { { $"{nameof(GetCatalogCategoryDetailResult.CatalogProductResult.CatalogProductId)}", $"{nameof(CatalogProduct)}.{nameof(CatalogProduct.CatalogProductId)}" }, { $"{nameof(GetCatalogCategoryDetailResult.CatalogProductResult.DisplayName)}", $"{nameof(CatalogProduct)}.{nameof(CatalogProduct.DisplayName)}" }, { $"{nameof(GetCatalogCategoryDetailResult.CatalogProductResult.ProductId)}", $"{nameof(CatalogProduct)}.{nameof(CatalogProduct.ProductId)}" }, { $"{nameof(GetCatalogCategoryDetailResult.CatalogProductResult.ProductName)}", $"{nameof(Product)}.{nameof(Product.Name)}" }, }; var sqlSelectedFields = string.Join(",", fieldsDefinition.Select(x => $"{x.Key}={x.Value}")); var groupByFields = string.Join(",", fieldsDefinition.Select(x => x.Value)); var sqlClauseBuilder = new StringBuilder($"SELECT {sqlSelectedFields}") .Append($" FROM {nameof(CatalogProduct)} AS {nameof(CatalogProduct)}") .Append($" INNER JOIN {nameof(Product)} AS {nameof(Product)}") .Append($" ON {nameof(CatalogProduct)}.{nameof(CatalogProduct.ProductId)} = {nameof(Product)}.Id") .Append($" WHERE {nameof(CatalogProduct)}.{nameof(CatalogCategoryId)} = @CatalogCategoryId"); if (!string.IsNullOrWhiteSpace(searchRequest.SearchTerm)) { sqlClauseBuilder = sqlClauseBuilder .Append($" AND {nameof(CatalogProduct)}.{nameof(CatalogProduct.DisplayName)} LIKE @SearchTerm"); } sqlClauseBuilder = sqlClauseBuilder .Append($" GROUP BY {groupByFields}") .Append($" ORDER BY {nameof(CatalogProduct)}.{nameof(CatalogProduct.DisplayName)}") .Append(" OFFSET @Offset ROWS ") .Append(" FETCH NEXT @PageSize ROWS ONLY "); return(sqlClauseBuilder.ToString()); }
private string SqlClauseForCountingCatalogProducts(GetCatalogCategoryDetailRequest.CatalogProductSearchRequest searchRequest) { var sqlClauseBuilder = new StringBuilder($"SELECT COUNT(*)") .Append($" FROM {nameof(CatalogProduct)}") .Append($" WHERE {nameof(CatalogCategoryId)} = @CatalogCategoryId"); if (!string.IsNullOrWhiteSpace(searchRequest.SearchTerm)) { sqlClauseBuilder = sqlClauseBuilder .Append($" AND {nameof(CatalogProduct)}.{nameof(CatalogProduct.DisplayName)} LIKE @SearchTerm"); } return(sqlClauseBuilder.ToString()); }