How to Inner Join an UDF Function with parameters using SubSonic

Question

Hi guys, I need to query a table using FreeTextTable (because I need ranking), with SubSonic. AFAIK, Subsonic doesn't support FullText, so I ended up creating a simple UDF function (Table Function) which takes 2 params (keywords to search and max number of results). Now, how can I inner join the main table with this FreeTextTable? InlineQuery is not an option. Example:

table ARTICLE with fields Id, ArticleName, Author, ArticleStatus.

The search can be done by one of more of the following fields: ArticleName (fulltext), Author (another FullText but with different search keywords), ArticleStatus (an int).

Actually the query is far more complex and has other joins (depending on user choice).

If SubSonic cannot handle this situation, probably the best solution is good old plain sql (so there would be no need to create an UDF, too).

Thanks for your help

ps: will SubSonic 3.0 handle this situation?

Answer 1

3.0 can do this for you but you'd need to make a template for it since we don't handle functions (yet) out of the box. I'll be working on this in the coming weeks - for now I don't think 2.2 will do this for you.

Answer 2

I realize your question is more complex than this, but you can get results from a table valued function via SubSonic 2.2 with a little massaging.

Copy the .cs file from one of your generated views into a safe folder, and then change all the properties to match the columns returned by your UDF.

Then, on your collection, add a constructor method with your parameters and have it execute an InlineQuery.

public partial class UDFSearchCollection
{
    public UDFSearchCollection(){}

    public UDFSearchCollection(string keyword, int maxResults)
    {
         UDFSearchCollection coll = new InlineQuery().ExecuteAsCollection<UDFSearchCollection>("select resultID, resultColumn from dbo.udfSearch(@keyword, @maxResults)",keyword,maxResults);
         coll.CopyTo(this);
         coll = null;
    }
}

public partial class UDFSearch : ReadOnlyRecord<UDFSearch>, IReadOnlyRecord
{
    //all the methods for read only record go here
    ...

}

An inner join would be a little more difficult because the table object doesn't have it's own parameters collection. But it could...