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;
// eslint-disable-next-line @typescript-eslint/no-deprecated
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;
// eslint-disable-next-line @typescript-eslint/no-deprecated
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;
// eslint-disable-next-line @typescript-eslint/no-deprecated
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".
// eslint-disable-next-line @typescript-eslint/no-deprecated
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;`,
// eslint-disable-next-line @typescript-eslint/no-deprecated
(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.
Last Updated: 29 April, 2025
Found something wrong, missing, or unclear on this page? Raise an issue in our repo.