SubSonic "SELECT WHERE IN" solution
June 25th, 2008 RustyThe In Problem
I searched to find out how to execute a pretty common query in SubSonic but found nothing.
Let’s say you want to accomplish the following result
SELECT *
FROM MyTable
WHERE Id IN( ‘val1′, ‘val2′, ‘val3′ )
That’s pretty straight forward, no?!
However, There doesn’t seem to be any way to make subsonic collections do that. At least this appears to be true when using Guid’s
I tried to make SubSonic.Comparison.In work for me but had no luck.
The Solution
public SubsonicDB.MyClassCollection GetByIds( List<Guid> MyClassIds )
{
// SubsonicDB represents my generated namespace of active record classes
// wtf? have to convert to object[]…
object[] items = new object[MyClassIds.Count];
for( int idx = 0; idx < MyClassIds.Count; idx++ )
{
items[idx] = MyClassIds[idx].ToString();
}
// create "IN" query
SubSonic.Query query = new SubSonic.Query( SubsonicDB.MyClass.Schema );
// create collection
SubsonicDB.MyClassCollection results = new SubsonicDB.MyClassCollection();
// create query and execute reader, filling collection
results.LoadAndCloseReader( query.IN( SubsonicDB.MyClass.Columns.Id, items ).ExecuteReader() );
return results;
}
This worked. I am posting it in the hopes of saving someone the time and trouble of trying ten other ways that look like they should work bu, indeed, do not…