Active Query Builder support area

Is it possible to enumerate all selected fields in a query and determine if they are foreign keys

Avatar
  • updated
  • Completed

Hi there, 

I am attempting to get a list of selected fields from a query and also determine whether those fields are involved in a foreign key relationship.

So far, I believe this code will get me the list of active fields:

foreach (StatisticsField field in queryBuilderDataSource.QueryStatistics.UsedDatabaseObjectFields)
if (field.Selected)
{

}

Is it possible to link the Statistics field to anything which will tell me if the field is a foreign key as well?

TIA,

Chris

Avatar
Andrey Zavyalov, PM

Sorry for the late response, but I think it's better late than never. Besides, other users can benefit from this answer.

I am not sure if it's reasonable to check the QueryStatistics.UsedDatabaseObjectFields, as I suppose that the task is to learn if there are any result query columns representing fields involved in a foreign key relationship. If so, it is better to use the QueryStatistics.OutputColumns collection. Anyway, I will post code samples for both variants. Here they are, using the OutputColumns collection:

    var stats = queryBuilder1.QueryStatistics;
    var sb = new StringBuilder();
    foreach (var outputColumn in stats.OutputColumns)
    {
        var obj = outputColumn.MetadataObject;
        if (obj == null) continue;
        var field = outputColumn.MetadataField;
        if (field == null) continue;
        foreach (var foreignKey in obj.Items.ForeignKeys)
        {
            if (foreignKey.ReferencedFields.Count == 1 &&
                foreignKey.Fields.Count == 1 &&
                foreignKey.Fields.Find(field.Name))
            {
                sb.AppendLine(field.NameFull + " -> " + foreignKey.ReferencedObject.NameFull + "." + foreignKey.Fields[0]);
            }
        }
    }
    MessageBox.Show(sb.ToString());

And using the UsedDatabaseObjectFields collection:

    var stats = queryBuilder1.QueryStatistics;
    var sb = new StringBuilder();
    foreach (var usedField in stats.UsedDatabaseObjectFields)
    {
        if (usedField.Selected)
        {
            var obj = usedField.MetadataObject;
            if (obj == null) continue;
            var field = usedField.MetadataField;
            if (field == null) continue;
            foreach (var foreignKey in obj.Items.ForeignKeys)
            {
                // continue the same way as in the first code sample to get foreign keys
            }
    }
    MessageBox.Show(sb.ToString());

Note that there was a bug in the UsedDatabaseObjectField.Selected property that did not allow to use the second code sample until now, so please update your version to the latest before using this code.