Working with query classes

Summary This topic discusses creating query classes, manipulating query descriptions, unique ID mapping, and performance considerations and limitations.

In this topic

Eligibility of data for query classes
Creating a query description
Creating a query class
Performance considerations and limitations

Eligibility of data for query classes

To consume the results of a query as a query class, a unique ID is required. This can be a natural unique ID—an existing field used as the Object ID in ArcGIS—or a mapped unique ID—a virtual field created by mapping the values of one or more existing fields to an integer. The results of a query must have one of the following:

A non-nullable integer field (a natural unique ID)
A combination of one or more integer, string, or GUID fields that results in a unique tuple (a mapped unique ID)

In the following table, a query result with a string field and an integer field that are not individually unique but form unique tuples (and contain no null values) can be used for ID mapping:

A table with unique tuples used for ID mapping.

The following requirements and restrictions apply:

Uniqueness—If a single field is used (natural or mapped), all values must be unique. If multiple fields are used, the tuples in each row must be unique. If non-unique values are used for mapping, no error will be raised, but unexpected behavior can occur and analysis results may be incorrect.
No null values—If a mapped ID is used, none of the field(s) used to create the ID can contain a null value. If a null value is used, an error will occur during mapping.
Negative natural values—If a natural unique ID is used, the field should not contain negative values. Rows with a natural unique ID less than 0 are ignored.
Geometry fields—A geometry field is not required; a maximum of one geometry field can be in the result set.
Spatial references—All geometries in a geometry field should share the same SRID.

In some cases query cursors can be used to work with queries that don't meet these requirements, but they do share some of the requirements (such as a maximum of one geometry field).

A special case is the presence of multiple geometry types in a single geometry field, such as a field that contains both points and polygons. This is not valid in ArcGIS, but these query classes can be created by specifying that only rows of one type can be used. Use the IQueryDescription.GeometryType property, described in the following section, to set this.

Creating a query description

A query description is an intermediate object used in the creation of a query class. A query description is created by the ISqlWorkspace.GetQueryDescription method, which takes a Structured Query Language (SQL) query as a parameter. This method auto-detects the properties of the query description if possible. For example, if a non-nullable integer field is found in the query result, the query description will indicate that ID mapping is false and that the ID field is the integer field.

In some cases, a query description does not have to be modified before it is passed to the OpenQueryClass method. If a non-nullable field exists with a type that is appropriate for mapping, the query description will be initialized with this field's name as its OIDFields value. In other cases—for example, when ID mapping is required and an appropriate mapping field cannot be determined automatically—the query description's properties must be set by the application before it can be used to create a query class.

The IQueryDescription interface defines several read-only properties that can be used to determine what the query result will look like; for example, the Fields property returns a field set for the query result. It also defines the following read/write properties that can be used to define how the query class will be created:

SpatialReference—The spatial reference that will be applied to the feature class when it is created. Note that this behaves slightly differently than the related Srid property; to define a spatial reference, this property should be used.
GeometryType —The type of geometry found in the geometry field. If the geometry field contains multiple geometry types, setting this property specifies which subset of rows will be retrieved from the query class (only rows matching a single geometry type can be used).
OIDFields—If a natural ID column is found, OIDFields will contain its name. If mapping is required, set this property to specify the field(s) to use.

The following code shows how to create a query description similar to the table in the previous section:

[C#]


        IQueryDescription queryDescription = sqlWorkspace.GetQueryDescription(
    "SELECT COUNTY, HIGHWAY FROM HIGHWAYS");
queryDescription.OIDFields = "COUNTY, HIGHWAY";

[VB.NET]


        Dim queryDescription As IQueryDescription = sqlWorkspace.GetQueryDescription("SELECT COUNTY, HIGHWAY FROM HIGHWAYS")
queryDescription.OIDFields = "COUNTY, HIGHWAY"

Creating a query class

Once a query description has been created (and modified if necessary), a query class can be created using ISqlWorkspace.OpenQueryClass. The following code shows how to create a query class in a case where the query description requires no modification:

[C#]


        IQueryDescription queryDescription = sqlWorkspace.GetQueryDescription(
    "SELECT * FROM PARCELS");
ITable queryClass = sqlWorkspace.OpenQueryClass("Parcels", queryDescription);

[VB.NET]


        Dim queryDescription As IQueryDescription = sqlWorkspace.GetQueryDescription("SELECT * FROM PARCELS")
Dim queryClass As ITable = sqlWorkspace.OpenQueryClass("Parcels", queryDescription)

The resulting class may or may not implement the IFeatureClass interface in addition to the ITable interface, depending on whether a geometry field is present. From this point, the class can typically be consumed in the same way as an ITable or IFeatureClass reference from other data sources, keeping in mind that it is read-only.

The following section outlines cases where a query class may behave differently than a class from other data sources.

Performance considerations and limitations

In most cases, a query class behaves in the same way as a typical non-geodatabase, read-only dataset. The following are exceptions:

Using methods that fetch rows based on Object IDs (such as ITable.GetRow) when IDs are mapped. When a query class with a mapped ID column is created, the rows are not initially assigned IDs. To assign IDs, the rows must first be read from the class using a search cursor (such as from ITable.Search), which will assign IDs sequentially. Once an ID has been assigned, methods such as GetRow work normally.
Mapped IDs are dependent on the order in which rows are returned from the database management system (DBMS). If the same query class is created on two separate occasions, but the DBMS returns the rows in a different order on the second occasion, the IDs are no longer associated with the same rows.
Some query filter functionality, such as IQueryFilterDefinition.PostfixClause and IQueryFilterDefinition2.PrefixClause, is not supported when performing queries on query classes. To apply one of these clauses, include the clause in the SQL query when creating the query description.

To get the best performance out of a query class, include a natural ID in the initial query. In cases where ID mapping is necessary, give preference to integer fields rather than string fields.