IModelDB.createQueryReader doesn't pick up iModel changes

[simihartstein]

Describe the bug I've been seeing some really weird behavior with IModelDB.createQueryReader.

The first query works fine, but future queries using that function aren't picking up changes to the iModel. It seems to be using a cached version of the iModel. Edits are persisted and will show up when you reopen the iModel.

To Reproduce

Here is a very simplified version of my code with the issue.

async function doSomeQuery(): Promise<number> {
  const elements: any[] = [];
  
    const sql = `
      SELECT
        FederationGuid,
        UserLabel,
        ECInstanceId
      FROM
        Bis:SomeElement
    `;
  
    const query = iModel.createQueryReader(sql);
    for await (const row of query) {
      elements.push(row)
    }
  
    return elements.length;
}

console.log(await doSomeQuery()) // 1

// insert an element (simplified. Real version uses EditCommand)
iModel.elements.insertElement({ classNameName: "Bis:SomeElement", ... });

console.log(await doSomeQuery()) // 1

Expected behavior It should have printed 2!

Desktop (please complete the applicable information):

• OS: Windows 11
• Browser: Electron
• iTwin.js Version: 4.11.1

Additional context I can verify this while debugging, but the most obvious side-effect is that the property grid will no longer show up on new elements after createQueryReader has run.

The same code using the deprecated IModelDB.withStatement works fine, but I wouldn't rule out that there's some crazy async bug in my code that's causing it.

[pmconne]

@khanaffan

[khanaffan]

The reason is that ECSqlReader will not show uncommitted changes. You need to call iModel.saveChanges() and then run the ecsql query.

An alternative to the above is to use userPrimaryConn option in ECSqlReader. This will run the query on same connection as we writing to and thus it sees any recent changes without calling iModel.saveChanges()

    const query = iModel.createQueryReader(sql, undefined, { usePrimaryConn: true });

[simihartstein]

@khanaffan Thanks I will verify the effect of usePrimaryConnection option. However, I'm fairly certain this problem persists through calls to iModel.saveChanges(), seeing as the properties grid is still broken after multiple future tools are used and those changes are persisting when I reopen the iModel.

Does IModelDB.withStatement use usePrimaryConnection by default?

[khanaffan]

PropertyGrid is ECPresentation and it could be a separate issue. That API does not use ECSqlReader.

saveChange() should work with ECSqlReader. If it doesn't let me know.

Does IModelDB.withStatement use userPrimaryConnection by default?

There are two different API and withStatement() has been deprecated in favor of ECSqlReader which is async. Second do not wrap the first one. But behind screenwithStatement() also run the query on primary connection like ECSqlReader with the userPrimaryConn.

[simihartstein]

@khanaffan I can confirm:

• The issue does not happen with the usePrimaryConn: true setting.
• I am calling iModel.saveChanges() between invocations of iModel.createQueryReader. It does not pick up changes without the usePrimaryConn option.

[simihartstein]

Someone else on my team can't reproduce the issue with simplified code. I will need to look into my use-case more...

[aruniverse]

@simihartstein is this still relevant or can this be closed?

[simihartstein]

It's still relevant... I will be refactoring the code where I saw the issue shortly and will test it out again afterward.

[khanaffan]

There is enhancement reported here https://github.com/iTwin/itwinjs-backlog/issues/1681 to address this issue as whole and it provide a approach that should enhance api so developer should be aware of passing flags instead its happen automatically under the hood.