public async Task <List <string> > SearchAsync(RepositorySearch repositorySearch) { var query = @" select rcs.name from repository_current_state as rcs join repository_team as rt on rt.repository_current_state_id = rcs.repository_current_state_id join repository_snapshot as rs on rs.repository_current_state_id = rcs.repository_current_state_id join repository_type_and_implementations as rti on rti.repository_snapshot_id = rs.repository_snapshot_id join repository_implementation as ri on ri.repository_type_and_implementations_id = rti.repository_type_and_implementations_id join repository_dependency as rd on rd.repository_snapshot_id = rs.repository_snapshot_id {{DEPENDENCY_JOIN}} where 1=1 {{WHERE_CLAUSES}} group by rcs.name order by rcs.name "; var whereClausesStringBuilder = new StringBuilder(); if (!repositorySearch.AsOf.HasValue) { whereClausesStringBuilder.AppendLine("and rs.window_ends_on is null"); } if (repositorySearch.HasContinuousDelivery.HasValue) { whereClausesStringBuilder.AppendLine($"and rcs.continuous_delivery = {repositorySearch.HasContinuousDelivery.ToString()}"); } if (!string.IsNullOrWhiteSpace(repositorySearch.Team)) { whereClausesStringBuilder.AppendLine($"and rt.name = '{repositorySearch.Team}'"); } if (!string.IsNullOrWhiteSpace(repositorySearch.TeamPermissions)) { whereClausesStringBuilder.AppendLine($"and rt.permission = '{repositorySearch.TeamPermissions}'"); } if (!string.IsNullOrWhiteSpace(repositorySearch.TypeName)) { whereClausesStringBuilder.AppendLine($"and rti.type_name = '{repositorySearch.TypeName}'"); } query = query.Replace("{{DEPENDENCY_JOIN}}", BuildDependenciesJoin()); query = query.Replace("{{WHERE_CLAUSES}}", whereClausesStringBuilder.ToString()); var dbConnection = repositoryAnalysisContext.Database.GetDbConnection(); var repositoryNames = await dbConnection.QueryAsync <string>(query); return(repositoryNames.AsList()); string GetRangeSpecifierString(RangeSpecifier rangeSpecifier) { switch (rangeSpecifier) { case RangeSpecifier.GreaterThan: return(">"); case RangeSpecifier.GreaterThanOrEqualTo: return(">="); case RangeSpecifier.LessThan: return("<"); case RangeSpecifier.LessThanOrEqualTo: return("<="); case RangeSpecifier.EqualTo: return("="); default: return(null); } } string BuildDependenciesJoin() { if (repositorySearch.Dependencies.Any()) { var depdendencyJoinStringBuilder = new StringBuilder(); depdendencyJoinStringBuilder.AppendLine("and rd.repository_snapshot_id in ("); var dependenciesAdded = 0; foreach (var dependency in repositorySearch.Dependencies) { if (dependenciesAdded > 0) { depdendencyJoinStringBuilder.AppendLine("intersect"); } depdendencyJoinStringBuilder.AppendLine("select repository_snapshot_id"); depdendencyJoinStringBuilder.AppendLine("from public.repository_dependency"); depdendencyJoinStringBuilder.AppendLine($"where name ilike '{dependency.Name}'"); if (!string.IsNullOrWhiteSpace(dependency.Version)) { var paddedVersion = versionManager.GetPaddedVersion(dependency.Version); var rangeSpecifierText = GetRangeSpecifierString(dependency.RangeSpecifier); depdendencyJoinStringBuilder.AppendLine($"and padded_version {rangeSpecifierText} '{paddedVersion}'"); } dependenciesAdded++; } depdendencyJoinStringBuilder.AppendLine(")"); return(depdendencyJoinStringBuilder.ToString()); } else { return(string.Empty); } } }