public QueryDefinition BuildSqlQuerySpec(SearchOptions searchOptions)
{
EnsureArg.IsNotNull(searchOptions, nameof(searchOptions));
if (searchOptions.CountOnly)
{
AppendSelectFromRoot("VALUE COUNT(1)");
}
else
{
AppendSelectFromRoot();
}
AppendSystemDataFilter();
var expressionQueryBuilder = new ExpressionQueryBuilder(
_queryBuilder,
_queryParameterManager);
if (searchOptions.Expression != null)
{
_queryBuilder.Append("AND ");
searchOptions.Expression.AcceptVisitor(expressionQueryBuilder);
}
AppendFilterCondition(
"AND",
(KnownResourceWrapperProperties.IsHistory, false),
(KnownResourceWrapperProperties.IsDeleted, false));
if (!searchOptions.CountOnly)
{
var hasOrderBy = false;
foreach (var sortOptions in searchOptions.Sort)
{
if (string.Equals(sortOptions.searchParameterInfo.Name, KnownQueryParameterNames.LastUpdated, StringComparison.OrdinalIgnoreCase))
{
if (!hasOrderBy)
{
_queryBuilder.Append("ORDER BY ");
hasOrderBy = true;
}
_queryBuilder.Append(SearchValueConstants.RootAliasName).Append(".")
.Append(KnownResourceWrapperProperties.LastModified).Append(" ")
.AppendLine(sortOptions.sortOrder == SortOrder.Ascending ? "ASC" : "DESC");
}
else
{
throw new SearchParameterNotSupportedException(string.Format(Core.Resources.SearchSortParameterNotSupported, sortOptions.searchParameterInfo.Name));
}
}
}
var query = new QueryDefinition(_queryBuilder.ToString());
_queryParameterManager.AddToQuery(query);
return(query);
}
public SqlQuerySpec BuildSqlQuerySpec(SearchOptions searchOptions)
{
EnsureArg.IsNotNull(searchOptions, nameof(searchOptions));
if (searchOptions.CountOnly)
{
AppendSelectFromRoot("VALUE COUNT(1)");
}
else
{
AppendSelectFromRoot();
}
AppendSystemDataFilter("WHERE");
var expressionQueryBuilder = new ExpressionQueryBuilder(
_queryBuilder,
_queryParameterManager);
if (searchOptions.Expression != null)
{
_queryBuilder.Append("AND ");
searchOptions.Expression.AcceptVisitor(expressionQueryBuilder);
}
if (searchOptions.CompartmentType != null)
{
AppendArrayContainsFilter(
"AND",
(GetCompartmentIndicesParamName(searchOptions.CompartmentType.Value), searchOptions.CompartmentId));
}
AppendFilterCondition(
"AND",
(KnownResourceWrapperProperties.IsHistory, false),
(KnownResourceWrapperProperties.IsDeleted, false));
SqlQuerySpec query = new SqlQuerySpec(
_queryBuilder.ToString(),
_queryParameterManager.ToSqlParameterCollection());
return(query);
}
public QueryDefinition GenerateReindexSql(SearchOptions searchOptions, string searchParameterHash)
{
EnsureArg.IsNotNull(searchOptions, nameof(searchOptions));
EnsureArg.IsNotNull(searchParameterHash, nameof(searchParameterHash));
if (searchOptions.CountOnly)
{
AppendSelectFromRoot("VALUE COUNT(1)");
}
else
{
AppendSelectFromRoot();
}
AppendSystemDataFilter();
var expressionQueryBuilder = new ExpressionQueryBuilder(
_queryBuilder,
_queryParameterManager);
if (searchOptions.Expression != null)
{
_queryBuilder.Append("AND ");
searchOptions.Expression.AcceptVisitor(expressionQueryBuilder);
}
AppendFilterCondition(
"AND",
true,
(KnownResourceWrapperProperties.IsDeleted, false));
AppendFilterCondition(
"AND",
false,
(KnownResourceWrapperProperties.SearchParameterHash, searchParameterHash));
var query = new QueryDefinition(_queryBuilder.ToString());
_queryParameterManager.AddToQuery(query);
return(query);
}
public SqlQuerySpec BuildSqlQuerySpec(SearchOptions searchOptions, bool calculateTotalCount = false)
{
EnsureArg.IsNotNull(searchOptions, nameof(searchOptions));
if (searchOptions.CountOnly || calculateTotalCount)
{
AppendSelectFromRoot("VALUE COUNT(1)");
}
else
{
AppendSelectFromRoot();
}
AppendSystemDataFilter();
var expressionQueryBuilder = new ExpressionQueryBuilder(
_queryBuilder,
_queryParameterManager);
if (searchOptions.Expression != null)
{
_queryBuilder.Append("AND ");
searchOptions.Expression.AcceptVisitor(expressionQueryBuilder);
}
AppendFilterCondition(
"AND",
(KnownResourceWrapperProperties.IsHistory, false),
(KnownResourceWrapperProperties.IsDeleted, false));
SqlQuerySpec query = new SqlQuerySpec(
_queryBuilder.ToString(),
_queryParameterManager.ToSqlParameterCollection());
return(query);
}
public QueryDefinition BuildSqlQuerySpec(SearchOptions searchOptions, QueryBuilderOptions queryOptions)
{
EnsureArg.IsNotNull(searchOptions, nameof(searchOptions));
EnsureArg.IsNotNull(queryOptions, nameof(queryOptions));
if (searchOptions.CountOnly)
{
AppendSelectFromRoot("VALUE COUNT(1)");
}
else if (queryOptions.Projection == QueryProjection.IdAndType)
{
AppendSelectFromRoot($"r.{KnownResourceWrapperProperties.ResourceId}, r.{KnownResourceWrapperProperties.ResourceTypeName}", queryOptions.Includes);
}
else if (queryOptions.Projection == QueryProjection.ReferencesOnly)
{
AppendSelectFromRoot(string.Empty, queryOptions.Includes);
}
else
{
AppendSelectFromRoot(includes: queryOptions.Includes);
}
AppendSystemDataFilter();
var expressionQueryBuilder = new ExpressionQueryBuilder(
_queryBuilder,
_queryParameterManager);
if (searchOptions.Expression != null)
{
_queryBuilder.Append("AND ");
searchOptions.Expression.AcceptVisitor(expressionQueryBuilder);
}
AppendFilterCondition(
"AND",
true,
(KnownResourceWrapperProperties.IsHistory, false),
(KnownResourceWrapperProperties.IsDeleted, false));
if (!searchOptions.CountOnly)
{
if (searchOptions.Sort.Any())
{
var sortOption = searchOptions.Sort[0];
_queryBuilder.Append("ORDER BY ");
if (string.Equals(sortOption.searchParameterInfo.Code, KnownQueryParameterNames.LastUpdated, StringComparison.OrdinalIgnoreCase))
{
#pragma warning disable CA1834 // Consider using 'StringBuilder.Append(char)' when applicable
_queryBuilder.Append(SearchValueConstants.RootAliasName).Append('.')
#pragma warning restore CA1834 // Consider using 'StringBuilder.Append(char)' when applicable
.Append(KnownResourceWrapperProperties.LastModified).Append(' ')
.AppendLine(sortOption.sortOrder == SortOrder.Ascending ? "ASC" : "DESC");
}
else
{
#pragma warning disable CA1834 // Consider using 'StringBuilder.Append(char)' when applicable
_queryBuilder.Append(SearchValueConstants.RootAliasName)
#pragma warning restore CA1834 // Consider using 'StringBuilder.Append(char)' when applicable
.Append(".sort[\"").Append(sortOption.searchParameterInfo.Code).Append("\"].")
.Append(sortOption.sortOrder == SortOrder.Ascending ? SearchValueConstants.SortLowValueFieldName : SearchValueConstants.SortHighValueFieldName)
.Append(' ').AppendLine(sortOption.sortOrder == SortOrder.Ascending ? "ASC" : "DESC");
}
}
}
var query = new QueryDefinition(_queryBuilder.ToString());
_queryParameterManager.AddToQuery(query);
return(query);
}
