Using FreeText with SubSonic

Question

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

Answer 1

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.

Answer 2

This won't work with SubSonic as it is now. I made a patch a while ago that would do something like this but it never got in. I'm wondering if it should be their or not what do you think?