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.value, "Physical");
}

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