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.

Goddord

Actually, dapper creates the query as IN (@idList0, @idList1, @idList2) ... just sayin' ;) But this sounds like a question for @Mythz... – Goggin

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.

Wheeling

Hi mythz, I rewrote the code and am now using the typed API. I did experience an issue though using the "Sql.In" statement. It would still put the square brackets around an int collection. If I converted the collection to <string> first, the issue didn't occur. Is this the expected behaviour, or an issue with using MySQL? – Goddord

hmmm, must be a bug. Can you post an issue on GitHub with a small snippet that shows the repro so we can track it? – Wheeling