HomeActive Query Builder - Metadata handling and filtrationWorking with metadata in .NET versionWorking with Metadata Filters

1.3. Working with Metadata Filters

Using metadata filters you can prevent loading of objects with specific names or to load objects with specific names only. You can define metadata filter to apply to the entire Metadata Container (MetadataContainer.MetadataLoadingOptions.MetadataFilter) or to specific node of the Metadata Structure (MetadataStructureItem.MetadataFilter).

Metadata Filter is a set of filtration rules. Each rule in the list is represented by the MetadataFilterItem object. An item can define objects to be set excluded or included in result set according to the Exclude property.  Metadata Filter can have multiple exclusion and inclusion items (rules). If Metadata Filter has no inclusion items, the exculsion items will be applied to the whole database schema. If Metadata Filter has some inclusion items, they will constitute a subset of database schema, to which the exclusion items will be applied to.

Each Metadata Filter Item has a mask for each type of metadata items: Server, Database, Schema, Package, Object and Field. Each mask determines a subset of objects which names match this mask. Masks act similar to the "LIKE" SQL operator: you can use the percent sign (%) to represent any sequence of characters; use the underscore sign (_) to represent any single character. The CaseSensivite property determines case sensitivity of masks. The ObjectTypes property determines which object types (Table, View, Synonym, Procedure or Function) will be included or excluded.

You may treat those rules as SQL conditions combined with OR operators in WHERE clause of the SELECT statement that retrieves the list of objects to be loaded to the Metadata Container. Conditions marked with the Exclude flag are prefixed with NOT operator. The result SQL condition will look like the following: "([inculsion rule] OR [inclusion rule] OR ...) AND NOT ([exculsion rule] OR [exclusion rule] OR ...)". Masks of one rule are combined with the AND operator.

Alternate way to define metadata filtration rules is to use the MetadataLoadingOptions.ExcludeFilter and MetadataLoadingOptions.IncludeFilter, but it can only be used to filter by database object name, schema name and database object type.  You can use both types of filters at a time: the conditions set using MetadataSimpleFilter will be seamlessly converted to a set of MetadataFilterItem objects which will be added to the MetadataFilter.

The following example adds filter item that excludes objects with names starting with "pre_":

MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add();
mfi.Exclude = true;
mfi.Object = "pre_%";
mfi.CaseSensitive = true;

And the following example adds filter item that excludes all schemas except the "schema":

MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add();
mfi.Exclude = false;
mfi.Schema = "dbo";

The following sample demostrates filtration by object type:

MetadataFilterItem metadataFilterItem = queryBuilder.MetadataLoadingOptions.MetadataFilter.Add();
metadataFilterItem.Exclude = false;
metadataFilterItem.ObjectTypes = MetadataType.Table | MetadataType.View;
metadataFilterItem.FlagSystem = false;
metadataFilterItem.FlagUser = true;


This page was: Helpful | Not Helpful