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.
<Schema name or alias>.<Class name>
Instead of '.' you can also use ':' as delimiter between schema and class name.
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
To bind values to an ECSQL statement after preparation, the following parameter placeholders are supported.
||Positional parameter. Its index is one greater than the previous parameter in the ECSQL statement.|
||Named parameter. This allows to bind the same value to more than one placeholder.|
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.
|ECInstanceId||Is the unique identifier for an ECInstance.|
|ECClassId||Refers to the ECClassId of an ECClass. It uniquely identifies an ECClass in the iModel.|
In iModel.js the ECClassId is formatted as fully qualified class name when used in the SELECT clause.
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.
For Boolean types ECSQL supports the literals
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
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
ECSQL supports dates without time (
DATE), dates with time (
TIMESTAMP), and times without date (
ECSQL does not support time zone conversions. Time zone conversions are to be handled by the application.
TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.nnn][Z]'
The time stamp format matches the ISO 8601 standard (see also https://en.wikipedia.org/wiki/ISO_8601)
||returns the current date|
||returns the current timestamp in UTC.|
||returns the current time of the day.|
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 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:
|X||X coordinate of the Point2d or Point3d|
|Y||Y coordinate of the Point2d or Point3d|
|Z||Z coordinate of the Point3d|
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
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 '.'.
||Returns the Location struct property as a whole|
||Returns the Street and City members of the Location struct property|
||Returns rows that match the bound Location value. The Location must be bound as a whole.|
||Returns rows that match the Location's Zip member value|
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>
In ECSQL you can refer to Array ECProperties only as a whole.
||Returns the PhoneNumbers array of the ACME company|
||Returns the companies that match the bound PhoneNumber array. The array must be bound as a whole.|
based on this ECSchema snippet:
<ECEntityClass typeName="Company"> <ECProperty propertyName="Name" typeName="string" /> <ECArrayProperty propertyName="PhoneNumbers" typeName="string" /> </ECEntityClass>
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:
||ECInstanceId of the related instance|
||ECClassId of the ECRelationshipClass backing the navigation property. It is mainly relevant when the ECRelationshipClass has subclasses.|
Navigation properties are a convenient short-cut for ECSQL Joins.
See also ECRelationshipClasses.
||Returns the Parent navigation property as a whole (including Id and RelECClassId)|
||Returns just the Id member of the Parent navigation property|
||Returns the Id, and the RelECClassId member of the Parent navigation property as two separate columns|
As ECRelationshipClasses are ECClasses as well, they can be used in ECSQL like ECClasses. Their additional relationship semantics is expressed by these system properties.
||ECInstanceId of the instance on the source end of the relationship|
||ECClassId of the instance on the source end of the relationship|
||ECInstanceId of the instance on the target end of the relationship|
||ECClassId of the instance on the target end of the relationship|
- 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.
TargetECClassIdare skipped when performing a
SELECT * FROMstatement or an
INSERT INTOstatement without a property name list.
||Returns the ECInstanceId of all Elements that drive the Element bound to the first parameter|
||Returns the ECInstanceId and ECClassId of all Elements contained by the Model bound to the parameter|
Joins between ECClasses are specified with the standard SQL join syntax (either
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.
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=?
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.
|`SELECT ECInstanceId FROM bis.Element WHERE Model=?``||Returns all Elements of any subclass in the specified Model|
|`SELECT ECInstanceId FROM bis.SpatialViewDefinition WHERE ModelSelector=?``||Returns SpatialViewDefinitions rows and rows of its subclasses for the specified ModelSelector|
|`SELECT ECInstanceId FROM ONLY bis.SpatialViewDefinition WHERE ModelSelector=?``||Returns only SpatialViewDefinitions rows for the specified ModelSelect, but no rows from its subclasses.|
LIMIT and OFFSET
One way to implement paging is to use the
OFFSET clauses in ECSQL.
LIMIT clause is used to limit the number of results returned from an ECSQL statement. Using
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
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, either built into SQLite or custom SQL functions, can be used in ECSQL.
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
Last Updated: 21 May, 2020