static FacetMoniker[] GetMostUsedFacets(TypeMoniker commandParameterType, CommandParameter parameter, int maxResults) { var rvl = new List <FacetMoniker>(); commandParameterType = parameter == null ? commandParameterType : parameter.Type; var facetTypeIds = Loader.GetFacetTypesImplementing(commandParameterType) .Select(x => x.DatabaseId.ToString()) .JoinStrings(","); if (facetTypeIds == "") { // no facet types support this command parameter type return(rvl.ToArray()); } using (var connection = DatabaseUtil.GetConnection()) { const string innerSelect = @"SELECT cua.FacetMonikerId, COUNT(*) AS _count FROM CommandUsageArguments AS cua INNER JOIN FacetMonikers AS fm ON fm.Id = cua.FacetMonikerId {0} WHERE fm.FacetTypeId IN ({1}) GROUP BY cua.FacetMonikerId"; const string forCommandFragment = @"INNER JOIN CommandUsages AS cu ON cu.Id = cua.CommandUsageId AND cu.CommandId = @CommandId"; var innerX = String.Format("INNER JOIN ({0}) AS x ON x.FacetMonikerId = fm.Id", String.Format(innerSelect, "", facetTypeIds)); var innerY = parameter == null ? null : String.Format("LEFT OUTER JOIN ({0}) AS y ON y.FacetMonikerId = fm.Id", String.Format(innerSelect, forCommandFragment, facetTypeIds)); var selectYCount = parameter == null ? "" : ", y._count"; var order = parameter == null ? "x._count DESC" : "COALESCE(y._count, 0) DESC, x._count DESC"; const string cmdText = @"SELECT fm.FactoryTypeId, fm.FacetTypeId, fm.DisplayName, fm.DateTime, fm.FactoryData, fm.Source, fm.ExtraData, x._count {0} FROM FacetMonikers AS fm {1} {2} WHERE fm.Id = x.FacetMonikerId ORDER BY {3}"; var finalCmdText = String.Format(cmdText, selectYCount, innerX, innerY, order); using (var cmd = new SqlCeCommand(finalCmdText, connection)) { if (parameter != null) { cmd.Parameters.AddWithValue("@CommandId", Loader.GetCommandInfo(parameter.Command).DatabaseId); } foreach (var row in cmd.ExecuteReader().AsEnumerable()) { var moniker = FacetIndex.MaterializeMoniker(row); if (moniker == null || (parameter != null && !parameter.IsUsableAsArgument(moniker))) { continue; } rvl.Add(moniker); if (rvl.Count == maxResults) { break; } } } } return(rvl.ToArray()); }