Using a custom query to select items where their id exists within a list of IDs

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 query 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];

The square brackets aren't valid in MySQL. Is it possible to do this in ormlite?

When I try using the anonymous class to list parameters, as in the dapper example, it can't find the second parameter.

Answer 1

To do a SQL IN statement, OrmLite needs to be able to escape all the values when it needs to (i.e. if they're string values). So if using 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 most of the time you don't need to use Raw SQL in OrmLite, it's more succinct and portable if you instead use the typed API. e.g:

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

You can also use [Alias] if your table wasn't the same name as the POCO, and can also specify the [Schema] with:

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

Which will instead use the specified Schema and table name when querying the db.