Calling an SQL function from a Subsonic.Select

Question

I asked the following question on the subsonic forum, but only seemed to get one response, so I thought I'd post up here as well to see if anyone could shed some more light on the problem...

I wish to create the following SQL statement through SubSonic using the Select tool (or Query tool) .. it uses a custom function called "SPLIT()":

SELECT * FROM VwPropertyList
WHERE VwPropertyList.idCreatedBy = 123
AND VwPropertyList.idCounty = 45
AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))

(the last part of this SQL uses the SPLIT function)

My subsonic equivalent looks like the following...

Dim mySelect As New SubSonic.Select
mySelect.From(VwPropertyList.Schema)
mySelect.Where(VwPropertyList.Columns.IdCreatedBy).IsEqualTo(123)
mySelect.And(VwPropertyList.Columns.IdCounty).IsEqualTo(45)
mySelect.And(29).In(New SubSonic.Select("Item").From("SPLIT(" & VwPropertyList.Columns.DistrictGroupList & ", ',')"))

This doesn't work though due to the last part .. how can I add "AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))" into my Subsonic.Select ?

The response I got from the subsonic forum suggested I do away with Subsonic.Select and replace with hard-coded InlineQuery() statements .. like:

Dim SQL as String = "Select " & VwPropertyList.Columns.Item
SQL = SQL & " From " & VwPropertyList.Schema.TableName
SQL = SQL & " Where " & VwPropertyList.Columns.IdCreatedBy & " = @CreatedBy "
SQL = SQL & " And " & VwPropertyList.Columns.IdCounty & " = @County "
SQL = SQL & " And @DistrictGroup IN (Select Item From SPLIT(DistrictGroupList,',')"

Items = SubSonic.InlineQuery().ExecuteTypedList(Of MyItem)(SQL, 123,45,29)

I would prefer to use SubSonic.Select if possible though so that I can avail of the paging functionality etc.

Any ideas?

Answer 1

You could try to use the original query object (pre 2.1) like so (untested, from memory):

Query q = new Query(VwPropertyList.Schema.TableName);
q.WHERE("29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))");

// pass q.ExecuteReader() to the Load() method of your view.

Answer 2

You could do John's suggestion or you could write the SQL using our InlineQuery - which allows you to write raw SQL and pass in params:

var qry=new InlineQuery("SELECT * FROM table WHERE column=@param",value)

Answer 3

I would suggest that you use the original Query object as you are looking to get paging. Inline Query does not have any methods that allow paging.

If you absolutely wanted to use Subsonic.Select you could mesh the two ideas and run an Inline Query to get the list of values and then use a Regular Subsonic.Select and pass the retrieved values to the select case but then you would be making two trips to the db.

On a side note I prefer reading Subsonic.Select statements written using the fluent interface that it is namely

SubSonic.Select.AllColumnsFrom() .Where(VwPropertyList.Columns.IdCreatedBy).IsEqualTo(123) .And(VwPropertyList.Columns.IdCounty).IsEqualTo(45) .ExecuteAsCollection();

Answer 4

hi guys .. thanks for the responses.

I ended up doing InlineQuery and just re-wrote the paging code that's normally produced by Subsonic.Select ... not the best solution but it seems to work.

It would be good if I could have done something like this though:

Dim s As New SubSonic.Select
s.From(VwPropertyList.Schema)
sWhere(VwPropertyList.Columns.IdCreatedBy).IsEqualTo(123)
sAnd(VwPropertyList.Columns.IdCounty).IsEqualTo(45)
s.And(29).In(New InlineQuery("(SELECT Item FROM SPLIT(DistrictGroupList, ','))"))