ECSQL Code Examples

Parameter Bindings

See section "ECSQL parameter types in iTwin.js" to learn which types to use for the parameters when binding all parameters at once.

Positional parameters

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

Working with the query result

ECSQL query results are returned as array of JavaScript literals, where each literal represents an ECSQL row in the ECSQL row format).

The following example is intended to illustrate the ECSQL row format:

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

Output

{id: "0x312", className: "StructuralPhysical.Slab", parent: {id: "0x433", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2018-02-03T13:43:22Z"}

{id: "0x313", className: "StructuralPhysical.Slab", parent: {id: "0x5873", relClassName: "BisCore.PhysicalElementAssemblesElements"}, lastMod: "2017-11-24T08:21:01Z"}

...

Note how the ECProperties used in the ECSQL are converted to members of the JavaScript literal and how their names are transformed according to the rules described in the ECSQL row format.

The following example illustrates how to work with the ECSQL row JavaScript literal:

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

  console.log(`${id}|${className}|${parent.id}|${parent.relClassName}|${lastMod}`);
}

Output

Last Updated: 20 June, 2023