Room RawQuery in Compose: Cannot access database on the main thread. Need to return a Flow<List<MyEntity>>

I need to return a Flow<List<MyEntity<>> from Room @RawQuery in Compose but get runtime error: Cannot access database on the main thread.

I have found many posts for @Query about using Coroutines, LiveData, and others, but don't know how to convert to get the Flow<List<MyEntity<>> at the Compose Level. (<> is >, I don't know how to Escape the text to show the correct syntax)

My current code:

Compose/View Level:

var filterVal by remember { mutableStateOf("") }
var filterValT by remember { mutableStateOf("") }
var logicV by remember { mutableStateOf(false) }
key(filter, filterT, logic){
    filterVal = filter
    filterValT = filterT
    logicV = logic
}

val items by viewModel.items(parentId, filterVal, if (filterVal.isEmpty() || filterValT.isEmpty()) "" else {logicV.toString()}, filterValT).collectAsState(initial = emptyList())

ViewModel Level:

fun items(subroomId: Long, filter:String = "", logic:String = "", filter2:String = ""): Flow<List<ItemEntity>> {
    val flow: Flow<List<ItemEntity>> = flow {
        val source = repository.getitems(subroomId, filter, logic, filter2)
        emitAll(source)
    }
    return flow
}

Repository Level:

fun getitems(subroomId:Long, filter:String = "", logic:String = "", filter2:String = ""):Flow<List<ItemEntity>> {
    var member = 
        when(true) {
            (filter == "" && filter2 == "") -> inventoryDB.itemDao().getitems(subroomId)
            else -> {
                inventoryDB.itemDao().getItemsByFilters(subroomId, filter, filter2, logic)
            }
        }

Dao Level:

@RawQuery(observedEntities = [ItemEntity::class])
fun getItemsByQuery(query: SupportSQLiteQuery): List<ItemEntity>

fun getItemsByFilters(subroomId: Long, filter:String = "", filter2:String = "", logic:String = ""): Flow<List<ItemEntity>> {
    var pl: MutableList<String> = emptyList<String>().toMutableList()
    var al: MutableList<String> = emptyList<String>().toMutableList()
    al.add(subroomId.toString())
    if (filter != "") {
        pl.add("itemname ${if (filter.startsWith(">")) " Like ?" else ">= ?"}")
        al.add(if (filter.startsWith(">")) filter.substring(1) else filter)
    }
    if (filter2 != "") {
        pl.add("itemtype ${if (filter.startsWith(">")) " Like ?" else " >= ?"}")
        al.add(if (filter2.startsWith(">")) filter2.substring(1) else filter2)
    }
    val placeholders = if (logic == "OR") pl.joinToString(" OR ") else pl.joinToString(" OR ")
    val args = al.toTypedArray()
    val query = SimpleSQLiteQuery("SELECT * FROM item WHERE category isnull and subroomId= ? and ($placeholders) ORDER BY category, itemtype, itemname ASC", args)
    return getItemsByQuery(query).asFlowOfLists()
}

There's a lot missing in your example code so we cannot reproduce the issue, but just from looking at the code the problem seems to be in the Dao. You need to change the return type of the raw query to a Flow:

@RawQuery(observedEntities = [ItemEntity::class])
fun getItemsByQuery(query: SupportSQLiteQuery): Flow<List<ItemEntity>>

It's the same as for regular queries. The only difference is that you need to specify observedEntities; but you already do that.

Just pass this flow through to the view model, convert it into a StateFlow (using stateIn, as usual), and collect in in your composables using collectAsStateWithLifecycle. It looks like you can throw out the majority of the code you posted here, it doesn't seem to be needed.

If you want to transform the data in the flow after it was returned from Room but before it is collected, you can use mapLatest and the other flow transformation operators like flatMapLatest, combine and so on.