ECSQL

Make sure to check out the ECSQL Tutorial as well!

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:

SELECT Model, CodeValue, Parent FROM BisCore.Element

And this one uses the schema alias:

SELECT Model, CodeValue, Parent FROM bis.Element

ECSQL Parameters

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

Example

SELECT ECInstanceId FROM bis.GeometricElement3d WHERE Model=? AND LastMod>=?

SELECT ECInstanceId FROM bis.GeometricElement3d LIMIT :pagesize OFFSET (:pageno * :pagesize)

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

SELECT Parent, ECClassId FROM bis.Element WHERE ECInstanceId=123

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

SELECT ECInstanceId, Model, CodeValue FROM bis.ViewDefinition3d WHERE IsCameraOn = True

SELECT ECInstanceId, Model, CodeValue FROM bis.ViewDefinition3d WHERE IsCameraOn = False

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:

SELECT ECInstanceId, Model, CodeValue FROM bis.ViewDefinition3d WHERE IsCameraOn

SELECT ECInstanceId, Model, CodeValue FROM bis.ViewDefinition3d WHERE NOT IsCameraOn

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

SELECT ECInstanceId, Model, CodeValue FROM bis.Element WHERE LastMod > DATE '2018-01-01'

SELECT ECInstanceId, Model, CodeValue FROM bis.Element WHERE LastMod < TIMESTAMP '2017-07-15T12:00:00.000Z'`

SELECT ECInstanceId, Model, CodeValue FROM bis.Element WHERE LastMod BETWEEN :startperiod AND :endperiod`

SELECT ECInstanceId FROM myschema.CalenderEntry WHERE startTime >= TIME '08:30:00' AND startTime <= TIME '09:00:00'

The last example is based on this ECSchema snippet:

<ECEntityClass typeName="CalenderEntry">
  <ECProperty propertyName="startTime" typeName="dateTime">
    <ECCustomAttributes>
      <DateTimeInfo xmlns="CoreCustomAttributes.01.00.01">
        <DateTimeComponent>TimeOfDay</DateTimeComponent>
      </DateTimeInfo>
    </ECCustomAttributes>
  </ECProperty>
  <ECProperty propertyName="endTime" typeName="dateTime">
    <ECCustomAttributes>
      <DateTimeInfo xmlns="CoreCustomAttributes.01.00.01">
        <DateTimeComponent>TimeOfDay</DateTimeComponent>
      </DateTimeInfo>
    </ECCustomAttributes>
  </ECProperty>
</ECEntityClass>

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

SELECT ECInstanceId, Model, CodeValue FROM bis.GeometricElement3d
WHERE Origin.X BETWEEN 3500000.0 AND 3500500.0 AND
Origin.Y BETWEEN 5700000.0 AND 5710000.0 AND
Origin.Z BETWEEN 0 AND 100.0

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:

<ECStructClass typeName="Address">
  <ECProperty propertyName="Street" typeName="string" />
  <ECProperty propertyName="City" typeName="string" />
  <ECProperty propertyName="Zip" typeName="int" />
</ECStructClass>
<ECEntityClass typeName="Company">
  <ECProperty propertyName="Name" typeName="string" />
  <ECArrayProperty propertyName="Location" typeName="Address" />
</ECEntityClass>

Arrays

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

Examples

based on this ECSchema snippet:

<ECEntityClass typeName="Company">
  <ECProperty propertyName="Name" typeName="string" />
  <ECArrayProperty propertyName="PhoneNumbers" typeName="string" />
</ECEntityClass>

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 ECRelationshipClass is backed by a Navigation property, it is usually much easier to use the navigation property in your ECSQL than the ECRelationshipClass.
• SourceECClassId and TargetECClassId are skipped when performing a SELECT * FROM statement or an INSERT INTO statement without a property name list.

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. 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.

If navigation properties are defined for the ECRelationship class, use the navigation property instead of a join.

Examples

Without navigation property (2 JOINs needed):

SELECT e.CodeValue,e.UserLabel FROM bis.Element driver JOIN bis.ElementDrivesElement ede ON driver.ECInstanceId=ede.SourceECInstanceId JOIN bis.Element driven ON driven.ECInstanceId=ede.TargetECInstanceId WHERE driven.ECInstanceId=? AND ede.Status=?

With navigation property (Element.Model):

Return the CodeValue and UserLabel of all Elements in the Model with the specified condition (1 JOIN needed):

SELECT e.CodeValue,e.UserLabel FROM bis.Element e JOIN bis.Model m ON e.Model.Id=m.ECInstanceId WHERE m.Name=?

Return the Model for an Element with the specified condition (No join needed):

SELECT Model FROM bis.Element WHERE ECInstanceId=?

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:

SELECT ECInstanceId,CodeValue,Parent FROM BisCore.Element WHERE Model=? LIMIT 50

Return the 201st through 250th matching Element:

SELECT ECInstanceId,CodeValue,Parent FROM BisCore.Element WHERE Model=? LIMIT 50 OFFSET 200

SQL Functions

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

Examples

SELECT substr(CodeValue,1,5) FROM bis.Element WHERE Model=?`

SELECT ECInstanceId FROM bis.Element WHERE lower(UserLabel)=?`

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

Last Updated: 02 February, 2022