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());
}
