Active Query Builder support area

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

Chris King

11 years ago
updated 9 years ago
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

Replies 1

Oldest first
Newest first Oldest first

Andrey Zavyalov, PM

10 years ago

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.

Reply Link