ECSQL

Make sure to check out the ECSQL Tutorial as well!

See ECSql Syntax & Features for an over view of ECSql syntax

See ECSQL Code Examples for examples of how to query ECSQL in with the iTwin API.

What is ECSQL

ECSQL is a text-based command language for CRUD (create, read, update, delete) operations against the business data in an iModel or ECDb file.

ECSQL is an implementation of SQL — a proven, well-adopted text-based command language. It adheres to standard SQL (SQL-92 and SQL-99) wherever possible.

Especially the SQL-99 standard came with a lot of features ECSchemas have too: boolean, date time, binary data types, structs, arrays, polymorphism. This allows ECSQL to deviate only in very few exceptions from standard SQL.

Anyone familiar with SQL should intuitively understand ECSQL.

The key difference between ECSQL and SQL is that ECSQL targets the logical schema, and not the underlying database’s persistence schema.

ECSQL in detail

This is not a comprehensive documentation of the SQL subset of ECSQL. This document only describes the exceptions to standard SQL and the cases where less known features of the standard are used. Standard SQL refers to SQL-92 (aka SQL 2), and to SQL-99 (aka SQL 3) whenever SQL-92 is not sufficient.

All ECSQL examples in the following sections refer to classes and relationships from the BisCore ECSchema (unless mentioned otherwise).

Fully qualifying ECClasses in ECSQL

The classes used in an ECSQL have to be fully qualified by their schemas.

Syntax: <Schema name or alias>.<Class name>

Instead of '.' you can also use ':' as delimiter between schema and class name.

Example

The following examples are equivalent. This one uses the schema name:

And this one uses the schema alias:

ECSQL Parameters

To bind values to an ECSQL statement after preparation, the following parameter placeholders are supported.

For the full list of supported iTwin.js parameter types, see ECSQL Parameter Types.

Example

See also sections ECInstanceId and ECClassId and LIMIT and OFFSET.

ECInstanceId and ECClassId

ECSQL defines a set of built-in system properties. They don't have to be defined in the ECSchemas.

In iTwin.js the ECClassId is formatted as fully qualified class name when used in the SELECT clause.

Example

Basic data types in ECSQL

ECSQL supports all primitive types built into EC. This means that in addition to the basic numeric and string data types in SQL-92, ECSQL also supports boolean, BLOBs, date-times and points.

Boolean

For Boolean types ECSQL supports the literals True and False.

Examples

Boolean properties or expressions do not need to be compared to True and False as they return a boolean value already. So the above examples can also be written like this:

DateTime

ECSQL supports dates without time (DATE), dates with time (TIMESTAMP), and times without date (TIME).

ECSQL does not support time zone conversions. Time zone conversions are to be handled by the application.

Literals

DATE 'yyyy-mm-dd'

TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.nnn][Z]'

TIME 'hh:mm:ss[.nnn]'

The time stamp format matches the ISO 8601 standard (see also https://en.wikipedia.org/wiki/ISO_8601)

Basic functions

Example

The last example is based on this ECSchema snippet:

Points

Points are a built-in primitive type in ECSchemas and are therefore supported in ECSQL.

In the context of ECSQL Point ECProperties are interpreted as structs made up of the following system properties:

Example

Structs

In ECSQL you can refer to a struct ECProperty either as a whole or by just referring to some of its members. The operator for referencing members of structs in an ECSQL is the '.'.

Examples

based on this ECSchema snippet:

Arrays

In ECSQL you can refer to Array ECProperties only as a whole.

Examples

based on this ECSchema snippet:

Navigation Properties

Navigation properties are ECProperties that point to a related object. They are always backed by an ECRelationshipClass.

In the context of ECSQL navigation properties are interpreted as structs made up of the following system properties:

Navigation properties are a convenient short-cut for ECSQL Joins.

See also ECRelationshipClasses.

Examples

ECRelationshipClasses

As ECRelationshipClasses are ECClasses as well, they can be used in ECSQL like ECClasses. Their additional relationship semantics is expressed by these system properties.

• If the schema exposes a Navigation property for the traversal you need, that navigation property is often the simpler way to write the query.
• SourceECClassId and TargetECClassId are skipped when performing a SELECT * FROM statement or an INSERT INTO statement without a property name list.

In plain SQL terms, an ECRelationshipClass is the logical "middle layer" between two classes. For many-to-many data, you can think of it like the link table that stores one row per related pair. ECSQL lets you work with that relationship directly, without needing to know whether the underlying persistence uses a separate link table, a foreign key, or another physical layout.

The examples below start with one built-in BIS relationship that will run on many iModels, then continue one walkthrough scenario: a school gym has a functional requirement called Light the Gym, and several physical light fixtures may be assigned to fulfill it.

Examples

Joins

Joins between ECClasses are specified with the standard SQL join syntax (either JOIN ... ON ... or the theta style).

In ECSchemas ECRelationshipClasses are used to relate two ECClasses. ECRelationshipClasses can therefore be seen as virtual link tables between those two classes. A useful beginner mental model is: start at one class, step through the relationship rows, then step to the related class. If you want to join two ECClasses via their ECRelationshipClass, you need to join the first class to the relationship class and then the relationship class to the second class.

In this gym example, one fixture might also fulfill another requirement such as Provide Emergency Egress Lighting. That is a many-to-many relationship, so the relationship rows record the fixture-to-requirement pairings.

Some traversals are also exposed through navigation properties, which give you another way to reach the related data.

Examples

Using an ECRelationshipClass directly (2 JOINs needed):

Scenario: given the FunctionalElement for Light the Gym, return the PhysicalElements assigned to fulfill it.

The result might look like this:

Each row is one lighting fixture assigned to the Light the Gym requirement. The query lists assigned fixtures, not proof that the requirement has been satisfied.

The gym query above is an example. It assumes a schema with functional data such as func.PhysicalElementFulfillsFunction. The next examples stay in the same gym walkthrough, but switch to the built-in Element.Model navigation property to show a different case. Here, the schema already exposes a navigation path from an element to its containing model, so ECSQL can express that traversal more directly.

Using a navigation property (Element.Model):

Return the CodeValue and UserLabel of all Elements in the Model named Gym Lighting Model (1 JOIN needed):

Return the Model for a specific light fixture element (navigation property only, no join needed):

Polymorphic Queries

By default, any ECClass in the FROM clause of an ECSQL is treated polymorphically, i.e. all its subclasses are considered as well. If an ECClass should be treated non-polymorphically, i.e. only the class itself and not its subclasses should be considered, add the ONLY keyword in front of it.

This also applies to Mixins. Mixins technically are ECClasses (abstract Entity ECClasses to be precise). So you can simply query against a mixin class without knowing which classes actually implement the mixin.

Examples

LIMIT and OFFSET

One way to implement paging is to use the LIMIT and OFFSET clauses in ECSQL. The LIMIT clause is used to limit the number of results returned from an ECSQL statement. Using LIMIT together with OFFSET allows specifying a range of rows to be returned. The OFFSET hereby specifies how many rows will be omitted from the result set. The LIMIT specifies the number of rows to be returned.

Examples

Return only the first 50 matching Elements:

Return the 201st through 250th matching Element:

SQL Functions

SQL functions, either built into SQLite or custom SQL functions, can be used in ECSQL.

Examples

See also SQLite Functions overview.

Spatial Queries and Geometry Functions

ECSQL can perform spatial queries.

ECSQL has a number of built-in geometry functions

Common Table Expressions

ECSQL can do regular and recursive CTE queries