Is there a way to create a temp table in OrmLite?

ServiceStack.OrmLite
1

My question comes from the need to write a query that uses an array of ints as filter for an Id column.

The number of IDs is high, and it surpasses easily the 1000 limit of any provider for the IN clause.

I usually solve this creating a temp table with 1 column, dump every ID I have into the table and join the table with the ID column i want to filter. This works nicely if I can write a DBMS-specific command, because the temp table creation is different from DBMS to DBMS (from the Sqlite CREATE TEMP TABLE to the SQL Server CREATE TABLE #name). The big problem is that our application should support at least: SQL Server and Postgres for production environment and Sqlite for debug purposes.

Is there a way or some kind of support from OrmLite?
I imagine a table marked as [Temporary] or something like that.

If you have alternative solutions to the massive Sql.In issue, feel free to share them.
Thank you.

2

No OrmLite doesn’t have a cross database solution for avoiding IN for large queries or any concept of [Temporary] tables, you can use temp tables in custom SQL since it doesn’t affect the resultset returned, but this is unrelated to OrmLite.

SQL Server 2016 has a STRING_SPLIT for getting around this where you can pass in a comma-delimited string which returns a single column table you can join on. PostgreSQL has something similar called regexp_split_to_table. Otherwise you should be able to create your own functions in both RDBMS’s that behave similarly.