I thought Dapper could merge multiple queries like this

c# dapper sqlite

Question

I'm using the Dapper micro ORM. I thought it could handle enumerables like this:

string sql = @"SELECT * FROM Events WHERE TimestampTicks <= @TimestampTicks AND TelemetryId = @TelemetryId ORDER BY TimestampTicks DESC LIMIT 1";
var events = _connection.Query<ReplayEvent>(sql, telemetryIds.Select(ti => new { TimestampTicks = startTime.Ticks, TelemetryId = ti }));

Alas it gives a worthless error. Does the Query method not have a way to merge multiple results? What's the right way to do this?

Accepted Answer

I believe you could do this using a subquery and using the build in 'List Support' referenced on the Dapper page:

string sql = @"SELECT * FROM Events e WHERE TimestampTicks = 
    (SELECT MAX(TimestampTicks) FROM Events WHERE TimestampTicks <=@TimestampTicks AND TelemetryId = e.TelemetryId ) 
    AND e.TelemetryId IN @TelemetryIds;"
var events = _connection.Query<ReplayEvent>(sql, new {TelemtryIds = telemetryIds, TimestampTicks = startTime.Ticks});



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why