Learning > ecsqltutorial > First Examples
Edit this page

First Examples

We will start off the tutorial by a simple ECSQL example:

First ECSQL

Try it yourself

Goal: Return id, subclass and code of all SpatialLocationElements in the iModel.

ECSQL:

SELECT ECInstanceId, ECClassId, CodeValue FROM bis.SpatialLocationElement

Result

ECInstanceId ECClassId CodeValue
0x1000000001d MyDomain.Building Building A
0x1000000001f MyDomain.Space A-G-1
0x10000000021 MyDomain.Space A-G-2
0x10000000024 MyDomain.Space A-1-1
0x10000000027 MyDomain.Space A-2-1
0x10000000029 MyDomain.Space A-2-2
0x1000000002b MyDomain.Space A-2-3
0x1000000002d MyDomain.Space A-2-4
0x1000000002f MyDomain.Space A-2-5
0x10000000031 MyDomain.Space A-2-6
0x10000000033 MyDomain.Space A-2-7
0x10000000035 MyDomain.Space A-2-8
0x1000000001e MyDomain.Story A-G
0x10000000023 MyDomain.Story A-1
0x10000000026 MyDomain.Story A-2

Fully qualified class names

The example illustrates an important rule. As an iModel contains more than one ECSchema, class names might be ambiguous. Therefore the classes used in an ECSQL have to be fully qualified by their schemas. The schema can either be specified by its name or by its alias.

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

See ECSQL Reference for details.

The example from above uses the schema alias. If you replace it by the schema name, you will get the same result as above.

Try it yourself

Goal: Return id, subclass and code of all SpatialLocationElements in the iModel.

ECSQL

SELECT ECInstanceId, ECClassId, CodeValue FROM BisCore.SpatialLocationElement

Result: As in First ECSQL

ECInstanceId ECClassId CodeValue
0x1000000001d MyDomain.Building Building A
0x1000000001f MyDomain.Space A-G-1
0x10000000021 MyDomain.Space A-G-2
0x10000000024 MyDomain.Space A-1-1
0x10000000027 MyDomain.Space A-2-1
0x10000000029 MyDomain.Space A-2-2
0x1000000002b MyDomain.Space A-2-3
0x1000000002d MyDomain.Space A-2-4
0x1000000002f MyDomain.Space A-2-5
0x10000000031 MyDomain.Space A-2-6
0x10000000033 MyDomain.Space A-2-7
0x10000000035 MyDomain.Space A-2-8
0x1000000001e MyDomain.Story A-G
0x10000000023 MyDomain.Story A-1
0x10000000026 MyDomain.Story A-2

If you omit the schema, you will get an error:

Try it yourself

Goal: Return id, subclass and code of all SpatialLocationElements in the iModel.

ECSQL

SELECT ECInstanceId, ECClassId, CodeValue FROM SpatialLocationElement

Result

Failed to execute the ECSQL: Invalid ECSQL class expression: Valid syntax: [<table space>.]<schema name or alias>.<class name>

Element Count

The above example is not very meaningful. In large iModels the query might return far too many instances. If you want to find out how many Elements there are in the iModel, you can run the following query.

Try it yourself

Goal: Find out how many Elements there are in the iModel.

ECSQL

SELECT count(*) FROM bis.Element

Result

count(*)
81

This query considers all kinds of Elements. If we want to focus only on Elements which represent realworld assets, we can use the BIS class SpatialElements instead.

Try it yourself

Goal: Find out how many SpatialElements there are in the iModel.

ECSQL

SELECT count(*) FROM bis.SpatialElement

Result

count(*)
27

Let's compute some more Element statistic with ECSQL. We want to find out how many SpatialElements there are in the iModel per actual element type (where element type here refers to the subclasses of the Element ECClass).

Try it yourself

Goal: Find out how many SpatialElements there are in the iModel per actual element type.

ECSQL

SELECT ECClassId, count(*) ElementCount FROM bis.SpatialElement GROUP BY ECClassId ORDER BY ECClassId

Result

ECClassId ElementCount
MyDomain.Building 1
MyDomain.Device 12
MyDomain.Space 11
MyDomain.Story 3

Limiting the result set

Another way to deal with large query results is to use LIMIT and OFFSET. See LIMIT and OFFSET in ECSQL Reference for details.

Let's apply LIMIT and OFFSET to he first ECSQL example from above (first ECSQL example) to shrink the returned rows to a more digestible number.

Try it yourself

Goal: Return the first 5 SpatialLocationElements only.

ECSQL

SELECT ECInstanceId, ECClassId, CodeValue FROM bis.SpatialLocationElement LIMIT 5

Result

ECInstanceId ECClassId CodeValue
0x1000000001d MyDomain.Building Building A
0x1000000001f MyDomain.Space A-G-1
0x10000000021 MyDomain.Space A-G-2
0x10000000024 MyDomain.Space A-1-1
0x10000000027 MyDomain.Space A-2-1

Try it yourself

Goal: Return the 11th through 15th SpatialLocationElement only.

ECSQL

SELECT ECInstanceId, ECClassId, CodeValue FROM bis.SpatialLocationElement LIMIT 5 OFFSET 10

Result

ECInstanceId ECClassId CodeValue
0x10000000033 MyDomain.Space A-2-7
0x10000000035 MyDomain.Space A-2-8
0x1000000001e MyDomain.Story A-G
0x10000000023 MyDomain.Story A-1
0x10000000026 MyDomain.Story A-2

Formatting the Output

Aliases for the expressions in the SELECT clause are a convenient way to format the output of the ECSQL query.

Try it yourself

Goal: Find out how many SpatialElements there are in the iModel and give the resulting column the more meaningful name Element Count.

ECSQL

SELECT count(*) ElementCount FROM bis.SpatialElement

Result

ElementCount
27

Try it yourself

Goal: Return id and code of all Elements in the iModel and give the id column the name ElementId and the code value column the name Code.

ECSQL

SELECT ECInstanceId ElementId, ECClassId, CodeValue Code FROM bis.Element LIMIT 3

Result

ElementId ECClassId Code
0x1 BisCore.Subject My Campus
0xe BisCore.LinkPartition BisCore.RealityDataSources
0x10 BisCore.DefinitionPartition BisCore.DictionaryModel

One aspect of the power of ECSQL (and SQL) is the richness of expressiveness. Instead of just returning the property values from some class, you can let ECSQL do calculations. The following example uses ECSQL as a simple calculator.

Try it yourself

Goal: Compute the perimeter and area of a circle with a radius of 10 cm.

ECSQL

SELECT 10 Radius, (2 * 3.1415 * 10) Perimeter, (3.1415 * 10 * 10) Area FROM bis.Element LIMIT 1

Result

Radius Perimeter Area
10 62.83 314.15

Using aliases is also helpful when working with the iModel.js API. The API returns query results as JavaScript object literals where each expression of the SELECT clause becomes the member of the object.

If you, for example, used the Element Count example with the iModel.js API, you would get this JavaScript object literal:

{ "count(*)" : 27 }

The power of JavaScript object literals is lost here, because count(*) is not a valid member name. If you applied an alias to the count expression though so that the ECSQL would look like this:

SELECT count(*) elementCount FROM bis.SpatialElement

the JavaScript object would now look like this:

 { elementCount : 27 }

Now the result can be consumed in TypeScript as desired:

 iModelDb.withPreparedStatement("SELECT count(*) elementCount FROM bis.SpatialElement", (stmt: ECSqlStatement) => {
    stmt.step();
    const row: any = stmt.getRow();
    console.log("Element count: " + row.elementCount);
    });

Parametrizing the ECSQL

To reuse the same ECSQL statement with different values, parameters can be used. Reusing ECSQL statements should always be considered because preparing an ECSQL statement can be costly. See the ECSQL Reference for details and some examples. Values for the parameters are bound to the statement via the iModel.js API.

Not binding a value to a parameter is like binding NULL to it.

Try it yourself

Goal: Return all SpatialElements that do not have a user label.

ECSQL

SELECT ECInstanceId,ECClassId FROM bis.SpatialElement WHERE CodeValue=? LIMIT 5

Result

ECInstanceId ECClassId
no rows

As you cannot bind values to parameters in the iModelConsole, the above query returns the same as if you did the following.

Try it yourself

Goal: Return all SpatialElements that do not have a user label.

ECSQL

SELECT ECInstanceId,ECClassId FROM bis.SpatialElement WHERE CodeValue = NULL LIMIT 5

Result

ECInstanceId ECClassId
no rows

Comparing to NULL

The above example can be used to mention SQLite's semantics of comparing to NULL (see also https://www.sqlite.org/nulls.html ). The rule in SQLite is:

SQLite evaluates the expression myProp = NULL always to false, even if the property is unset.

If you want to check whether a property is NULL, i.e. unset, use the IS NULL or IS NOT NULL expressions.

Try it yourself

Goal: Return all SpatialElements that do not have a user label.

ECSQL

SELECT ECInstanceId,ECClassId FROM bis.SpatialElement WHERE UserLabel IS NULL LIMIT 5

Result

ECInstanceId ECClassId
0x1000000001d MyDomain.Building
0x10000000020 MyDomain.Device
0x10000000022 MyDomain.Device
0x10000000025 MyDomain.Device
0x10000000028 MyDomain.Device

And to illustrate the difference, the same query using = NULL does not return any rows.

Try it yourself

Goal: Illustrate that expressions like = NULL are always false.

ECSQL

SELECT ECInstanceId,ECClassId FROM bis.SpatialElement WHERE UserLabel = NULL LIMIT 5

Result

ECInstanceId ECClassId
no rows

SQL Functions

Any SQL function can be used in ECSQL. This includes functions built into SQLite (see SQLite Functions overview) or functions built into iModel.js, like the geometry functions which you can use for spatial queries.

Try it yourself

Goal: For all SpatialElements whose code contains the string 'DEV' return a more human-readable form of the code by replacing 'DEV' by 'Device'.

ECSQL

SELECT ECInstanceId, CodeValue, replace(CodeValue,'DEV','Device') ReadableCode FROM bis.SpatialElement WHERE instr(CodeValue,'DEV') LIMIT 5

Result

ECInstanceId CodeValue ReadableCode
0x10000000020 DEV-A-G-1 Device-A-G-1
0x10000000022 DEV-A-G-2 Device-A-G-2
0x10000000025 DEV-A-1-1 Device-A-1-1
0x10000000028 DEV-A-2-1 Device-A-2-1
0x1000000002a DEV-A-2-2 Device-A-2-2

The example uses the SQLite functions replace to replace the substring 'DEV' in the code and instr to only do this on rows where the code contains the substring 'DEV' at all.

Note, that the instr function can be replaced by using the standard SQL LIKE operator together with the wildcard %.

Try it yourself

Goal: For all SpatialElements whose code contains the string 'DEV' return a more human-readable form of the code by replacing 'DEV' by 'Device'.

ECSQL

SELECT ECInstanceId, CodeValue, replace(CodeValue,'DEV','Device') ReadableCode FROM bis.SpatialElement WHERE CodeValue LIKE '%DEV%' LIMIT 5

Result

ECInstanceId CodeValue ReadableCode
0x10000000020 DEV-A-G-1 Device-A-G-1
0x10000000022 DEV-A-G-2 Device-A-G-2
0x10000000025 DEV-A-1-1 Device-A-1-1
0x10000000028 DEV-A-2-1 Device-A-2-1
0x1000000002a DEV-A-2-2 Device-A-2-2

< Previous   |   Next >

Last Updated: 13 June, 2024