Useful ECSQL Queries

The following ECSQL select statements are examples of useful queries that an app backend or a service might want to perform. They are written in a form that can be executed in backend code.

Select Elements in a particular Model

    const modelId: Id64String = IModelDb.repositoryModelId;
    iModel.withPreparedStatement(`SELECT ECInstanceId AS id FROM ${Element.classFullName} WHERE Model.Id=:modelId`, (statement: ECSqlStatement) => {
      statement.bindId("modelId", modelId);
      while (DbResult.BE_SQLITE_ROW === statement.step()) {
        // do something with each row
      }
    });

Select Top-Level Elements in a particular Model

    const modelId: Id64String = IModelDb.repositoryModelId;
    iModel.withPreparedStatement(`SELECT ECInstanceId AS id FROM ${Element.classFullName} WHERE Model.Id=:modelId AND Parent.Id IS NULL`, (statement: ECSqlStatement) => {
      statement.bindId("modelId", modelId);
      while (DbResult.BE_SQLITE_ROW === statement.step()) {
        // do something with each row
      }
    });

Select Child Elements

    const parentId: Id64String = IModelDb.rootSubjectId;
    iModel.withPreparedStatement(`SELECT ECInstanceId AS id FROM ${Element.classFullName} WHERE Parent.Id=:parentId`, (statement: ECSqlStatement) => {
      statement.bindId("parentId", parentId);
      while (DbResult.BE_SQLITE_ROW === statement.step()) {
        // do something with each row
      }
    });

Look up element by code value

    // Suppose an iModel has the following breakdown structure:
    // * The root subject
    // * * Subject with CodeValue="Subject1"
    // * * * PhysicalPartition with CodeValue="Physical"

    // Suppose you want to look up the PhysicalPartition whose code value is "Physical".
    // You could write the following query to find it. This query specifies that the
    // element you want is a PhysicalPartition, it has a code value of "Physical",
    // and it is a child of a Subject named "Subject1".
    const partitionIds: Id64Set = iModel.withPreparedStatement(`
      select
        partition.ecinstanceid
      from
        ${PhysicalPartition.classFullName} as partition,
        (select ecinstanceid from ${Subject.classFullName} where CodeValue=:parentName) as parent
      where
        partition.codevalue=:partitionName and partition.parent.id = parent.ecinstanceid;
    `, (stmt: ECSqlStatement) => {
      stmt.bindValue("parentName", "Subject1");
      stmt.bindValue("partitionName", "Physical");
      const ids: Id64Set = new Set<Id64String>();
      while (stmt.step() === DbResult.BE_SQLITE_ROW)
        ids.add(stmt.getValue(0).getId());
      return ids;
    });

    assert.isNotEmpty(partitionIds);
    assert.equal(partitionIds.size, 1);
    for (const eidStr of partitionIds) {
      assert.equal(iModel.elements.getElement(eidStr).code.getValue(), "Physical");
    }

As an alternative, you can use the IModelDb.queryEntityIds convenience method for simple cases.

Last Updated: 23 April, 2020