Is there a general consensus on how to use SQL 2005's full text search with SubSonic? I know that I can use the InlineQuery and get an IDataReader, but is this the only way to do this? Also, how would I incorporate paging into it? Would I have to write the paging myself in the InlineQuery?
What I would really like to do is something like this:
new Select().From<Item>().Where("FreeText(Title, @title)").ExecuteAsCollection<ItemCollection>();
This way, I can use the built-in Subsonic paging functions and not have to write the entire query in SQL
Best Answer
This is one case with SubSonic where I think it is easier to create a stored procedure and build the collection from the result. Paging in a sproc isn't that difficult to implement (capture the sql generated by SubSonic and reuse it).
You can build a typed collection from the sproc by passing SPs.SPNameHere.GetReader() to the ItemCollection.Load() method. Make sure the sproc returns what a SELECT * FROM Item would return.