Aaquagua!: SubSonic

Friday, July 11, 2008

Using "select in" with SubSonic

While looking for SubSonic related information on something totally unrelated, I came across a post about trying to use the T-SQL "in" keyword to build a SubSonic collection at http://www.vitaminzproductions.com/technology-blog/index.php/2008/06/25/subsonic-select-where-in-solution. If that sounds confusing, imagine that you would like to have SubSonic return data that you would usually get with the following T-SQL statement:

select *
from MyTable 
where id in ('val1', 'val2', 'val3')

The author of the blog post had a solution but it was not one I liked. It seemed like a hack and I hate hacks. So, I took a few minutes and here is the result. The key is using the new SubSonic.Select query available in SubSonic v2.1. The code offers 3 different ways to accomplish the task.
C# Code:

// 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 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 statement
MyTableQuery = new SubSonic.Select()
 .From(MyTable.Schema)
 .Where(MyTable.Columns.Id)
 .In(new SubSonic.Select(MyTable.Columns.Id)
 .Top("3")
 .From(MyTable.Schema))
 .ExecuteAsCollection();

VB.NET Code:

' Define your collection object
Dim MyTableQuery As MyTableCollection

' Define an array list for the first example
Dim ids As New ArrayList

' Define an array list for the first example
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(Of MyTableCollection)()

' Poplulate the collection 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(Of MyTableCollection)()

' Poplulate the collection using an inline select statement
MyTableQuery = New SubSonic.Select() _
    .From(MyTable.Schema) _
    .Where(MyTable.Columns.Id) _
    .In(New SubSonic.Select(MyTable.Columns.Id) _
    .Top("3") _
    .From(MyTable.Schema)) _
    .ExecuteAsCollection(Of MyTableCollection)()