Working with query classes

Summary
This topic discusses creating query classes, manipulating query descriptions, unique ID mapping, and 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, which is 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:
  • Non-nullable integer field (a natural unique ID)
  • Combination of one or more integers, strings, or globally unique identifier (GUID) fields that results in a unique tuple (a mapped unique ID)
In the following illustration of a 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:
  
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 can be incorrect.
  • No null values—If a mapped ID is used, no field or fields used to create the ID can contain a null value. If a null value is used, an error occurs 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 spatial reference identifier (SRID) and by definition, the same spatial reference. 
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 geometry 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 automatically 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 indicates ID mapping is false and 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 looks 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 contain its name. If mapping is required, set this property to specify the field or fields to use.
The following code example 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 example 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 like 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 like 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 assigns 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.
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. Since this field or fields will be used heavily by ArcGIS, always make sure that these fields have up to date attribute indexes built in the database. 


See Also:

Working with query cursors
Query classes and cursors



To use the code in this topic, reference the following assemblies in your Visual Studio project. In the code files, you will need using (C#) or Imports (VB .NET) directives for the corresponding namespaces (given in parenthesis below if different from the assembly name):
Development licensing Deployment licensing
ArcGIS for Desktop Basic ArcGIS for Desktop Basic
ArcGIS for Desktop Standard ArcGIS for Desktop Standard
ArcGIS for Desktop Advanced ArcGIS for Desktop Advanced
Engine Developer Kit Engine