public override object VisitSqlRoot()

in src/Microsoft.Health.Fhir.SqlServer/Features/Search/Expressions/Visitors/QueryGenerators/SqlQueryGenerator.cs [68:261]
151 lines of code
27 McCabe index (conditional complexity)

        public override object VisitSqlRoot(SqlRootExpression expression, SearchOptions context)
        {
            if (!(context is SearchOptions searchOptions))
            {
                throw new ArgumentException($"Argument should be of type {nameof(SearchOptions)}", nameof(context));
            }

            _rootExpression = expression;

            if (expression.SearchParamTableExpressions.Count > 0)
            {
                if (expression.ResourceTableExpressions.Count > 0)
                {
                    throw new InvalidOperationException("Expected no predicates on the Resource table because of the presence of TableExpressions");
                }

                StringBuilder.Append("WITH ");

                StringBuilder.AppendDelimited($",{Environment.NewLine}", expression.SearchParamTableExpressions, (sb, tableExpression) =>
                {
                    sb.Append(TableExpressionName(++_tableExpressionCounter)).AppendLine(" AS").AppendLine("(");

                    using (sb.Indent())
                    {
                        tableExpression.AcceptVisitor(this, context);
                    }

                    sb.Append(")");
                });

                StringBuilder.AppendLine();
            }

            string resourceTableAlias = "r";
            bool selectingFromResourceTable;

            if (searchOptions.CountOnly)
            {
                if (expression.SearchParamTableExpressions.Count > 0)
                {
                    // The last CTE has all the surrogate IDs that match the results.
                    // We just need to count those and don't need to join with the Resource table
                    selectingFromResourceTable = false;
                    StringBuilder.AppendLine("SELECT COUNT(DISTINCT Sid1)");
                }
                else
                {
                    // We will be counting over the Resource table.
                    selectingFromResourceTable = true;
                    StringBuilder.AppendLine("SELECT COUNT(*)");
                }
            }
            else
            {
                selectingFromResourceTable = true;

                // DISTINCT is used since different ctes may return the same resources due to _include and _include:iterate search parameters
                StringBuilder.Append("SELECT DISTINCT ");

                if (expression.SearchParamTableExpressions.Count == 0)
                {
                    StringBuilder.Append("TOP (").Append(Parameters.AddParameter(context.MaxItemCount + 1, includeInHash: false)).Append(") ");
                }

                StringBuilder.Append(VLatest.Resource.ResourceTypeId, resourceTableAlias).Append(", ")
                    .Append(VLatest.Resource.ResourceId, resourceTableAlias).Append(", ")
                    .Append(VLatest.Resource.Version, resourceTableAlias).Append(", ")
                    .Append(VLatest.Resource.IsDeleted, resourceTableAlias).Append(", ")
                    .Append(VLatest.Resource.ResourceSurrogateId, resourceTableAlias).Append(", ")
                    .Append(VLatest.Resource.RequestMethod, resourceTableAlias).Append(", ");

                // If there's a table expression, use the previously selected bit, otherwise everything in the select is considered a match
                StringBuilder.Append(expression.SearchParamTableExpressions.Count > 0 ? "CAST(IsMatch AS bit) AS IsMatch, " : "CAST(1 AS bit) AS IsMatch, ");
                StringBuilder.Append(expression.SearchParamTableExpressions.Count > 0 ? "CAST(IsPartial AS bit) AS IsPartial, " : "CAST(0 AS bit) AS IsPartial, ");

                StringBuilder.Append(VLatest.Resource.IsRawResourceMetaSet, resourceTableAlias).Append(", ");

                if (_schemaInfo.Current >= SchemaVersionConstants.SearchParameterHashSchemaVersion)
                {
                    StringBuilder.Append(VLatest.Resource.SearchParamHash, resourceTableAlias).Append(", ");
                }

                StringBuilder.Append(VLatest.Resource.RawResource, resourceTableAlias);

                if (IsSortValueNeeded(context))
                {
                    StringBuilder.Append(", ").Append(TableExpressionName(_tableExpressionCounter)).Append(".SortValue");
                }

                StringBuilder.AppendLine();
            }

            if (selectingFromResourceTable)
            {
                StringBuilder.Append("FROM ").Append(VLatest.Resource).Append(" ").Append(resourceTableAlias);

                if (_schemaInfo.Current < SchemaVersionConstants.PartitionedTables &&
                    expression.SearchParamTableExpressions.Count == 0 &&
                    !_searchType.HasFlag(SqlSearchType.History) &&
                    expression.ResourceTableExpressions.Any(e => e.AcceptVisitor(ExpressionContainsParameterVisitor.Instance, SearchParameterNames.ResourceType)) &&
                    !expression.ResourceTableExpressions.Any(e => e.AcceptVisitor(ExpressionContainsParameterVisitor.Instance, SearchParameterNames.Id)))
                {
                    // If this is a simple search over a resource type (like GET /Observation)
                    // make sure the optimizer does not decide to do a scan on the clustered index, since we have an index specifically for this common case
                    StringBuilder.Append(" WITH(INDEX(").Append(VLatest.Resource.IX_Resource_ResourceTypeId_ResourceSurrgateId).AppendLine("))");
                }
                else
                {
                    StringBuilder.AppendLine();
                }

                if (expression.SearchParamTableExpressions.Count > 0)
                {
                    StringBuilder.AppendLine().Append("INNER JOIN ").AppendLine(TableExpressionName(_tableExpressionCounter));
                    StringBuilder.Append("ON ")
                        .Append(VLatest.Resource.ResourceTypeId, resourceTableAlias).Append(" = ").Append(TableExpressionName(_tableExpressionCounter)).AppendLine(".T1 AND ")
                        .Append(VLatest.Resource.ResourceSurrogateId, resourceTableAlias).Append(" = ").Append(TableExpressionName(_tableExpressionCounter)).AppendLine(".Sid1");
                }

                using (var delimitedClause = StringBuilder.BeginDelimitedWhereClause())
                {
                    foreach (var denormalizedPredicate in expression.ResourceTableExpressions)
                    {
                        delimitedClause.BeginDelimitedElement();
                        denormalizedPredicate.AcceptVisitor(ResourceTableSearchParameterQueryGenerator.Instance, GetContext());
                    }

                    if (expression.SearchParamTableExpressions.Count == 0)
                    {
                        AppendHistoryClause(delimitedClause);
                        AppendDeletedClause(delimitedClause);
                        AppendSearchParameterHashClause(delimitedClause);
                    }
                }

                if (!searchOptions.CountOnly)
                {
                    StringBuilder.Append("ORDER BY ");

                    if (_rootExpression.SearchParamTableExpressions.Any(t => t.Kind == SearchParamTableExpressionKind.Include))
                    {
                        // ensure the matches appear before includes
                        StringBuilder.Append("IsMatch DESC, ");
                    }

                    if (IsPrimaryKeySort(searchOptions))
                    {
                        StringBuilder.AppendDelimited(", ", searchOptions.Sort, (sb, sort) =>
                            {
                                Column column = sort.searchParameterInfo.Name switch
                                {
                                    SearchParameterNames.ResourceType => VLatest.Resource.ResourceTypeId,
                                    SearchParameterNames.LastUpdated => VLatest.Resource.ResourceSurrogateId,
                                    _ => throw new InvalidOperationException($"Unexpected sort parameter {sort.searchParameterInfo.Name}"),
                                };
                                sb.Append(column, resourceTableAlias).Append(" ").Append(sort.sortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                            })
                            .AppendLine();
                    }
                    else if (IsSortValueNeeded(searchOptions))
                    {
                        StringBuilder
                            .Append($"{TableExpressionName(_tableExpressionCounter)}.SortValue ")
                            .Append(searchOptions.Sort[0].sortOrder == SortOrder.Ascending ? "ASC" : "DESC").Append(", ")
                            .Append(VLatest.Resource.ResourceSurrogateId, resourceTableAlias).AppendLine(" ASC ");
                    }
                    else
                    {
                        StringBuilder
                            .Append(VLatest.Resource.ResourceSurrogateId, resourceTableAlias).AppendLine(" ASC ");
                    }
                }
            }
            else
            {
                // this is selecting only from the last CTE (for a count)
                StringBuilder.Append("FROM ").AppendLine(TableExpressionName(_tableExpressionCounter));
            }

            if (Parameters.HasParametersToHash)
            {
                // Add a hash of (most of the) parameter values as a comment.
                // We do this to avoid re-using query plans unless two queries have
                // the same parameter values. We currently exclude from the hash parameters
                // that are related to TOP clauses or continuation tokens.
                // We can exclude more in the future.

                StringBuilder.Append("/* HASH ");
                Parameters.AppendHash(StringBuilder);
                StringBuilder.AppendLine(" */");
            }

            return null;
        }