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 tofalse
, 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
Last Updated: 21 May, 2020