ECSQL in iTwin.js Code Examples

This page contains generic example code that can be used across the IModelDb, ECDb, and IModelConnection classes. In the examples, the identifier iModel is used as an object that could be any of those classes.

For more info and examples specific to running in the frontend and backend, check out:

• Executing ECSQL in the Frontend
  • Frontend ECSQL Code Examples
• Executing ECSQL in the Backend
  • Backend ECSQL Code Examples

See also:

• About ECSQL
• Frequently used ECSQL queries

The createQueryReader Function

All of the iModel classes above provide a createQueryReader method for executing ECSQL statements on an iModel and reading the results of the query. The execution and results are handled by the returned ECSqlReader.

For refererence, here are all three createQueryReader methods.

• IModelDb.createQueryReader
• ECDb.createQueryReader
• IModelConnection.createQueryReader

Here is the TypeScript method signature for createQueryReader:

createQueryReader(ecsql: string, params?: QueryBinder, config?: QueryOptions): ECSqlReader

• The ecsql string is the ECSQL statement that will be executed on the iModel. This is where you provide an ECSQL statement to query an iModel. E.g.,

  SELECT ECInstanceId, ECClassId FROM BisCore.Element
  

• The params argument of type QueryBinder contains any bindings for the ECSQL statement.

• The config argument of type QueryOptions is for additional options for how the query will be executed. Some examples are:

  • rowFormat for determining how query results will look. For an explanation of the available formats, see ECSQL Row Formats.
  • limit for specifying how many rows can be returned at most.
  • restartToken for canceling the execution of a previous query and starting a new one.

Iterating Over Query Results

Use the ECSqlReader created by the createQueryReader function to iterate over query results. There are three primary ways to do so:

1. Stream them using ECSqlReader as an asynchronous iterator.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element")) {
  console.log(`ECInstanceId is ${row[0]}`);
  console.log(`ECClassId is ${row.ecclassid}`);
}

Results are QueryRowProxy objects. See Handling a Row of Query Results for how to handle the results.

2. Iterate over them manually using ECSqlReader.step.

const reader = iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element");
while (await reader.step()) {
  console.log(`ECInstanceId is ${reader.current[0]}`);
  console.log(`ECClassId is ${reader.current.ecclassid}`);
}

Results are QueryRowProxy objects. See Handling a Row of Query Results for how to handle the results.

3. Capture all of the results at once in an array using QueryRowProxy.toArray.

const reader = iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element");
const allRows = await reader.toArray();
console.log(`First ECInstanceId is ${allRows[0][0]}`);
console.log(`First ECClassId is ${allRows[0][1]}`);

Results are JavaScript literals. See Working with Rows as JavaScript Literals for how to handle the results.

Handling a Row of Query Results

The format of the query results is dependent on the provided rowFormat in the config parameter. Click here to read about ECSQL Row Formats in detail.

When iterating over each row one at a time (as an asynchronous iterator or with step), each row will be a QueryRowProxy object. The rows value can then be accessed by column index or by name.

Accessing Row Values By Index

When iterating with a for loop:

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element")) {
  console.log(`ECInstanceId is ${row[0]}`);
  console.log(`ECClassId is ${row[1]}`);
}

When iterating with step:

const reader = iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element");
while (await reader.step()) {
  console.log(`ECInstanceId is ${reader.current[0]}`);
  console.log(`ECClassId is ${reader.current[1]}`);
}

The rowFormat used does not matter when accessing by index; only the order of the selected columns does. The two queries below will return the ECInstanceId and ECClassId values as indexes 0,1 and 1,0 respectively.

SELECT ECInstanceId, ECClassId FROM bis.Element
SELECT ECClassId, ECInstanceId FROM bis.Element

Accessing Row Values By Name

When iterating with a for loop:

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element", undefined, { rowFormat: QueryRowFormat.UseECSqlPropertyNames })) {
  console.log(`ECInstanceId is ${row.ECInstanceId}`);
  console.log(`ECClassId is ${row.ECClassId}`);
}

When iterating with step:

const reader = iModel.createQueryReader("SELECT ECInstanceId, ECClassId FROM bis.Element", undefined, { rowFormat: QueryRowFormat.UseECSqlPropertyNames });
while (await reader.step()) {
  console.log(`ECInstanceId is ${reader.current.ECInstanceId}`);
  console.log(`ECClassId is ${reader.current.ECClassId}`);
}

Using Types with the Row Results

Each ECSQL value has a corresponding TypeScript type which is described in ECSQL Parameter Types.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId, Parent, LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  const id: Id64String = row.id;
  const className: string = row.className;
  const parent: NavigationValue = row.parent;
  const lastMod: string = row.lastMod;
}

Working with Rows as JavaScript Literals

Call .toRow() on the row to convert it from a QueryRowProxy object to a JavaScript literal. The format of the literal is dependent on the provided rowFormat in the config parameter. Check out ECSQL Row Formats for more details.

Note: With the deprecation of .query in 3.7 and the switch to using ECSqlReader to handle query results, rows were changed from being JavaScript literals to QueryRowProxys. Using .toRow() may fix any issues that emerged due to this change.

When iterating with a for loop:

for await (const row of iModel.createQueryReader("SELECT * FROM bis.Element")) {
  const jsRow: {} = row.toRow(); // explicitly typed for example purposes
}

When iterating with step:

const reader = iModel.createQueryReader("SELECT * FROM bis.Element");
while (await reader.step()) {
  const jsRow: {} = reader.current.toRow(); // explicitly typed for example purposes
}

When using toArray:

const reader = iModel.createQueryReader("SELECT * FROM bis.Element");
const jsRows = await reader.toArray();

Specifying Row Formats

The format of of a row is dependent on the provided rowFormat in the config parameter of createQueryReader. The row formats are specified by supplying a QueryRowProxy enum.

Check out ECSQL Row Formats for more details.

QueryRowFormat.UseECSqlPropertyIndexes

This is the default format when no rowFormat is specified. Column values should refered to by an index which is ordered by the columns specified in the SELECT statement.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId, Parent, LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`ECInstanceId is ${row[0]}`);
  console.log(`ECClassId is ${row[1]}`);
  console.log(`Parent is ${row[2]}`);
  console.log(`LastMod is ${row[3]}`);
}

Here is an example using .toArray:

const reader = iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames });
const jsRows = await reader.toArray();
console.log(jsRows);

Example Output:

Notice that the individual rows are returned as arrays.

[
  [
    '0x17',
    '0x8d',
    null,
    '2017-07-25T20:44:59.711Z'
  ],
  [
    '0x18',
    '0x67',
    { Id: '0x17', RelECClassId: '0x66' },
    '2017-07-25T20:44:59.711Z'
  ],
  ...
]

QueryRowFormat.UseECSqlPropertyNames

Column values should refered to by their ECSQL property names.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId, ECClassId, Parent, LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`ECInstanceId is ${row.ECInstanceId}`);
  console.log(`ECClassId is ${row.ECClassId}`);
  console.log(`Parent is ${row.Parent}`);
  console.log(`LastMod is ${row.LastMod}`);
}

Here is an example using .toArray:

const reader = iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames });
const jsRows = await reader.toArray();
console.log(jsRows);

Example Output:

[
   {
    ECInstanceId: '0x17',
    ECClassId: '0x8d',
    LastMod: '2017-07-25T20:44:59.711Z'
  },
  {
    ECInstanceId: '0x18',
    ECClassId: '0x67',
    Parent:
    {
      Id: '0x17',
      RelECClassId: '0x66'
    },
    LastMod: '2017-07-25T20:44:59.711Z'
  },
  ...
]

QueryRowFormat.UseJsPropertyNames

Column values should be refered to by their JavaScript property names. The mapping from ECSQL property names to JavaScript property names is described in ECSQL Row Formats.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`ECInstanceId is ${row.id}`);
  console.log(`ECClassId is ${row.className}`);
  console.log(`Parent is ${row.parent}`);
  console.log(`LastMod is ${row.lastMod}`);
}

Here is an example using .toArray:

const reader = iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE Model.Id=?", QueryBinder.from(["0x10"]), { rowFormat: QueryRowFormat.UseJsPropertyNames });
const jsRows = await reader.toArray();
console.log(jsRows);

Example Output:

[
  {
    id: '0x17',
    className: 'BisCore.SpatialCategory',
    lastMod: '2017-07-25T20:44:59.711Z'
  },
  {
    id: '0x18',
    className: 'BisCore.SubCategory',
    parent:
    {
      id: '0x17',
      relClassName: 'BisCore.CategoryOwnsSubCategories'
    },
    lastMod: '2017-07-25T20:44:59.711Z'
  },
  ...
]

Notice how the keys in the above JSON are converted from ECProperty names to names that conform to JavaScript standards as described in ECSQL Row Formats. For example, "ECInstanceId" is mapped to "id".

Parameter Bindings

See ECSQL Parameter Types to learn which types to use for the parameters when binding.

Positional parameters

for await (const row of iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=? AND LastMod>=?",
  QueryBinder.from(["MyCode", "2018-01-01T12:00:00Z"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.id}, ${row.className}, ${row.parent}, ${row.lastMod}`);
}

Named parameters

for await (const row of iModel.createQueryReader("SELECT ECInstanceId,ECClassId,Parent,LastMod FROM bis.Element WHERE CodeValue=:code AND LastMod>=:lastmod",
  QueryBinder.from({ code: "MyCode", lastmod: "2018-01-01T12:00:00Z" }), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.id}, ${row.className}, ${row.parent}, ${row.lastMod}`);
}

Navigation properties

Navigation properties are structs made up of the Id of the related instance and the backing ECRelationshipClass. The NavigationBindingValue interface is used to bind values to navigation property parameters.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId FROM bis.Element WHERE Parent=?", QueryBinder.from([{ id: "0x132" }]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.id}`);
}

Because of the struct nature of navigation properties, you can also use its members in the ECSQL. The following example illustrates this by specifying the Id member of a navigation property.

for await (const row of iModel.createQueryReader("SELECT ECInstanceId FROM bis.Element WHERE Parent.Id=?", QueryBinder.from(["0x132"]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.id}`);
}

Struct properties

You can either parameterize a struct property as a whole or parameterize individual members of the struct. See Struct properties in ECSQL for the ECSQL background.

The ECSQL examples used in this section refer to the sample ECSchema in Struct properties in ECSQL.

Binding structs as a whole

for await (const row of iModel.createQueryReader("SELECT Name FROM myschema.Company WHERE Location=?", QueryBinder.from([{ street: "7123 Main Street", zip: 30211 }]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.name}`);
}

Binding to individual struct members

for await (const row of iModel.createQueryReader("SELECT Name FROM myschema.Company WHERE Location.Street=? AND Location.Zip=?", QueryBinder.from(["7123 Main Street", 32443]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.name}`);
}

The two ECSQL examples used in this section amount to the same results.

Array properties

See Array properties in ECSQL for the ECSQL background.

The ECSQL examples used in this section refer to the sample ECSchema in Array properties in ECSQL.

for await (const row of iModel.createQueryReader("SELECT Name FROM myschema.Company WHERE PhoneNumbers=?", QueryBinder.from([["+16134584201", "+16134584202", "+16134584222"]]), { rowFormat: QueryRowFormat.UseJsPropertyNames })) {
  console.log(`${row.name}`);
}

Last Updated: 30 May, 2023