SubSonic "SELECT WHERE IN" solution
The 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…
July 11th, 2008 at 11:29 am
It’s much easier than that if you are using 2.1 which by the date of your post, you should be
Here are 3 different ways to do it:
// Define your collection object
MyTableCollection MyTableQuery;
// Define an array list for the first example
ArrayList ids = new ArrayList();
// Add some Guid values to the array list
ids.Add(new Guid(”80FB189C-C3EA-4774-B15E-018DA88D3FE2″));
ids.Add(new Guid(”88DE9C1D-26A7-4545-AA1E-01BDAEFF0588″));
// Poplulate the collection using an array list
MyTableQuery = new SubSonic.Select()
.From(MyTable.Schema)
.Where(MyTable.Columns.Id)
.In(ids)
.ExecuteAsCollection();
// Poplulate the collection using an array list using an array of strings
MyTableQuery = new SubSonic.Select()
.From(MyTable.Schema)
.Where(MyTable.Columns.Id)
.In(new string[] {
“80FB189C-C3EA-4774-B15E-018DA88D3FE2″,
“88DE9C1D-26A7-4545-AA1E-01BDAEFF0588″})
.ExecuteAsCollection();
// Poplulate the collection using an inline select statment
MyTableQuery = new SubSonic.Select()
.From(MyTable.Schema)
.Where(MyTable.Columns.Id)
.In(new SubSonic.Select(MyTable.Columns.Id)
.Top(”3″)
.From(MyTable.Schema))
.ExecuteAsCollection();
July 11th, 2008 at 2:21 pm
Great to know!
I did something more straight forward:
I wrote an extension method to create a comma-separated string and used a QueryCommand with raw SQL.
public static string ToCommaSeparatedString(this int[] input)
{
int l = input.Length;
StringBuilder sb = new StringBuilder(l);
for (int i = 0; i < l; i++)
{
sb.AppendFormat(”{0},”, input[i]);
}
return sb.ToString().TrimEnd(’,');
}
Your solution is more in tune with the library.
Cheers!
July 26th, 2008 at 8:08 pm
Boyen,
Thanks! I will definitely check this out.
I needed another SELECT IN on friday and googled myself to find the example because I couldn’t remember exactly where I’d used it and, frankly, google is faster and more effective that search vs.net.