Using a custom query to select items where their identifier exists in the list of identifiers

Question

In dapper you can do something like:

var items = connection.Query<Items>("SELECT * FROM `@database`.`table` WHERE `id` IN @idList;", new {database = DatabaseName, idList = someList.Select(n => n.id)}); 

trying to do the same in ormlite:

 var items = connection.Query<Items>("SELECT * FROM {0}`.`table` WHERE `id` IN {1};", DatabaseName, someList.Select(n => n.id)}); 

returns an error. Dapper creates the request as such:

 SELECT * FROM `someDB`.`table` WHERE `id` IN (1,2,3,4); 

where ormlite generates:

 SELECT * FROM `someDB`.`table` WHERE `id` IN [1,2,3,4]; 

Square brackets are not valid in MySQL. Is it possible to do this in ormlite?

When I try to use an anonymous class to display parameters, as in the dapper example, it cannot find the second parameter.

Answer 1

To execute the SQL IN statement , OrmLite must be able to avoid all values ​​when necessary (for example, if they are string values). Therefore, if you use raw SQL in OrmLite, you can do:

 var ids = someList.Select(n => n.id).ToArray(); var items = db.Select<Items>( "SELECT * FROM `{0}`.`table` WHERE `id` IN ({1})", DatabaseName, new SqlInValues(ids)); 

Although in most cases you do not need to use Raw SQL in OrmLite, it is more concise and portable if you use a typed API instead. eg:

 var items = db.Select<Items>(q => Sql.In(q.Id, ids)); 

You can also use [Alias] if your table does not match the POCO name, and can also specify [Schema] with:

 [Schema("DatabaseName")] [Alias("table")] public class Items { public int Id { get; set; } public string Name { get; set; } } 

Instead, the specified schema and table name will be used when querying db.