How To Inner Join UDF Function With Parameters With SubSonic
I need to query a table using FreeTextTable (because I need a rating) with SubSonic. AFAIK, Subsonic does not support FullText, so I created a simple UDF (table function) that takes 2 parameters (search keywords and maximum number of results). Now, how can I join the main table using this FreeTextTable? InlineQuery is not an option. Example:
table ARTICLE with fields Id, ArticleName, Author, ArticleStatus.
Searches can be performed in one of the following fields: ArticleName (full text), Author (another FullText, but with different search keywords), ArticleStatus (int).
In fact, the query is much more complex and has different joins (depending on the user's choice).
If SubSonic can't handle this situation, plain old sql is probably the best solution (so it wouldn't need to create UDFs either).
thanks for the help
ps: will SubSonic 3.0 handle this situation?
source to share
I understand that your question is more complex than this, but you can get results from the table related function via SubSonic 2.2 with a little massaging.
Copy the .cs file from one of your generated views to a safe folder and then change all properties to match the columns returned by your UDF.
Then in your collection add a constructor method with your parameters and execute it with 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 complicated because the table object doesn't have its own collection of parameters. But it could ...
source to share