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.


< Previous   |   Next >

Last Updated: 20 September, 2019