ECSQL Data Types
ECInstanceId and ECClassId
ECSQL defines a set of built-in system properties. They don't have to be defined in the ECSchemas (see also ECSQL reference).
Property | Description |
---|---|
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.
Try it yourself
Goal: Return the actual Element subclass of the Element with id 0x10000000020.
ECSQL
SELECT ECClassId, CodeValue FROM bis.Element WHERE ECInstanceId=0x10000000020
Result
ECClassId CodeValue MyDomain.Device DEV-A-G-1
Primitive Data Types
ECSQL supports all primitive types built into the EC system. This means that in addition to the basic numeric and string data types in SQL-92, ECSQL also supports boolean, BLOBs, date-time and point types.
Boolean
For Boolean types ECSQL supports the literals True
and False
.
Try it yourself
Goal: Find out which Model are private or not.
ECSQL
SELECT ECInstanceId, ECClassId, IsPrivate FROM bis.Model
Result
ECInstanceId ECClassId IsPrivate 0x1 BisCore.RepositoryModel false 0xe BisCore.LinkModel true 0x10 BisCore.DictionaryModel true 0x10000000002 BisCore.PhysicalModel false 0x10000000003 BisCore.DocumentListModel false 0x10000000004 BisCore.DefinitionModel false 0x10000000037 BisCore.DrawingModel false 0x1000000003d BisCore.DrawingModel false 0x10000000042 BisCore.DrawingModel false
Boolean properties or expressions do not need to be compared to True
and False
as they return a
boolean value already.
Try it yourself
Goal: Find private Models.
ECSQL
SELECT ECInstanceId,ECClassId FROM bis.Model WHERE IsPrivate = True
and
SELECT ECInstanceId,ECClassId FROM bis.Model WHERE IsPrivate
are equivalent.
Result
ECInstanceId ECClassId 0xe BisCore.LinkModel 0x10 BisCore.DictionaryModel
And the same example with False
:
Try it yourself
Goal: Find non-private Models.
ECSQL
SELECT ECInstanceId,ECClassId FROM bis.Model WHERE IsPrivate = False
and
SELECT ECInstanceId,ECClassId FROM bis.Model WHERE NOT IsPrivate
are equivalent.
Result
ECInstanceId ECClassId 0x1 BisCore.RepositoryModel 0x10000000002 BisCore.PhysicalModel 0x10000000003 BisCore.DocumentListModel 0x10000000004 BisCore.DefinitionModel 0x10000000037 BisCore.DrawingModel 0x1000000003d BisCore.DrawingModel 0x10000000042 BisCore.DrawingModel
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.
See ECSQL Reference for details.
Try it yourself
Goal: Find all Devices which were modified between 4pm and 6pm UTC on December, 18th 2018.
ECSQL
SELECT CodeValue,LastMod FROM mydomain.Device WHERE LastMod BETWEEN TIMESTAMP '2018-12-18T16:00:00Z' AND TIMESTAMP '2018-12-18T18:00:00Z'
Result
CodeValue LastMod DEV-A-G-4 2018-12-18T16:03:15.206Z
Points
Points are a built-in primitive type of 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 (See ECSQL Reference for details):
Property | Description |
---|---|
X |
X coordinate of the Point2d or Point3d |
Y |
Y coordinate of the Point2d or Point3d |
Z |
Z coordinate of the Point3d |
Try it yourself
Goal: Find all Devices whose origin lies within the cube with the lower corner point (50, 30, 10) and the upper corner point (70, 40, 20).
ECSQL
SELECT CodeValue, Origin FROM MyDomain.Device WHERE Origin.X BETWEEN 50 AND 70 AND Origin.Y BETWEEN 30 AND 40 AND Origin.Z BETWEEN 10 AND 20
Result
CodeValue Origin DEV-A-2-6 {"x":55,"y":35,"z":11} DEV-A-2-7 {"x":65,"y":35,"z":11}
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 (See ECSQL Reference for details):
Property | Description |
---|---|
Id |
ECInstanceId of the related instance |
RelECClassId |
ECClassId of the ECRelationshipClass backing the navigation property. It is mainly relevant when the ECRelationshipClass has subclasses. |
In iModel.js the RelECClassId is formatted as fully qualified class name when used in the SELECT clause.
Try it yourself
Goal: Return the parent Element for the Space with code value A-G-2.
ECSQL
SELECT Parent FROM MyDomain.Space WHERE CodeValue='A-G-2'
Result
Parent {"id":"0x1000000001e","relClassName":"BisCore.ElementOwnsChildElements"}
Try it yourself
Goal: Return the id of the parent Element for the Space with code value A-G-2.
ECSQL
SELECT Parent.Id FROM MyDomain.Space WHERE CodeValue='A-G-2'
Result
Parent.Id 0x1000000001e
Try it yourself
Goal: Return the id and RelECClassId of the parent Element separately for the Space with code value A-G-2.
ECSQL
SELECT Parent.Id, Parent.RelECClassId FROM MyDomain.Space WHERE CodeValue='A-G-2'
Result
Parent.Id Parent.RelECClassId 0x1000000001e BisCore.ElementOwnsChildElements
Find more examples in the lesson about Joins and ECRelationshipClasses.
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 '.'.
The sample iModel does not have struct properties. However, the Change Summaries of the iModel have them. Follow the steps in the sections Generate Change Summaries and Attach Change Summaries, before you run the following queries.
Try it yourself
Goal: Return ChangedInstance struct (of type InstanceKey) as a whole and OpCode for the InstanceChange object
0x36
.ECSQL
SELECT ChangedInstance,OpCode FROM change.InstanceChange WHERE ECInstanceId=0x36
Result
ChangedInstance OpCode {"classId":"0x100","id":"0x20000000002"} Update
And here is an example where individual members of the struct are used.
Try it yourself
Goal: Return the ids of changed instances (structs of type InstanceKey) that are Devices (ECClass
0x100
) and the corresponding Change Summary id and OpCode.ECSQL
SELECT Summary.Id,ChangedInstance.Id,OpCode FROM change.InstanceChange WHERE ChangedInstance.ClassId=0x100
Result
Summary.Id ChangedInstance.Id OpCode 0x1 0x20000000001 Delete 0x35 0x20000000001 Update 0x35 0x20000000002 Insert 0x6c 0x20000000001 Insert
You can find more ECSQL examples in the respective section of the ECSQL Reference.
Arrays
In ECSQL you can refer to Array ECProperties only as a whole.
Try it yourself
Goal: Return the ECEnumeration values for the ECEnumeration FlameDetectionTechnique. The ECEnumeration values are stored in the array property ECEnumerationDef.EnumValues.
ECSQL
SELECT Name, EnumValues FROM meta.ECEnumerationDef WHERE Name='FlameDetectionTechnique'
Result
Name EnumValues FlameDetectionTechnique [{"name": "Optical", "intValue":0},{"name":"Ultraviolet", "intValue":1},{"name":"Infrared", "intValue":2}]
You can find more ECSQL examples in the respective section of the ECSQL Reference.
Last Updated: 21 May, 2020