I have a database with a table valued function in it that I use to implement a CONTAINSTABLE query. It looks like this:
CREATE Function [dbo].[FreeTextSearch_Usage]
(@SearchString varchar(1024) )
returns table
AS
return Select top 750 rank, internalid, myVal1, myVal2, myVal3
FROM (SELECT K.RANK, C.internalid, C.Realid as myVal1,
D.valStr2 as myVal2, D.valStr3 as myVal3
FROM [dbo].[idmapping] AS C
INNER JOIN
CONTAINSTABLE ( [dbo].myTable1,
myColumn1,
@SearchString
) AS K ON C.[internalid] = K.[KEY]
join [dbo].[valTbl] as D on
D.valID = K.[key]
union --- more CONTAINSTABLE queries
) AS Match
ORDER BY rank desc
This works and has been tested as standalone code and it also works when invoked using t–sql select * from FreeTextSearch_Usage( 'keyword')
But now I'm trying to call it from inside my C# code where the database is declared (automatically via code gen) as
public partial class myDBContextContainer : DbContext
{
public myDBContextContainer() : base (name=myDBContextContainer)
{}
o o o
[DbFunction(myDBContextContainer, FreeTextSearch_Usage)]
public virtual IQueryable<FreeTextSearch_Usage_Result> FreeTextSearch_Usage ( string searchString )
{
var searchStringParameter = new ObjectParameter ("SearchString", SearchString );
return ((IObjectContextAdapter)this.....very long - will reproduce if needed
}
So in my code I do the following:
using( var myDBctx = new myDBContextContainer () )
{
try
{
var usageList = myDBctx.FreeTextSearch_Usage("'keyword'").ToList();
var usageList2 = from s in myDBctx.FreeTextSearch_Usage( "keyword" )
select new List<usageListType> {};
}
catch (e )
and pretty much no matter what I pass as the keyword it throws an exception with an Inner exception value of "The Full-text query parameter for FullText Query String is not valid "
so if I pass a string of the form "keyword" I get an exception if I pass a string of the form 'keywod' I get an exception if I pass a string of the form "'FORMSOF(INFLECTIONAL, 'keyword')'" I get an exception if I pass a string of the form ""FORMSOF(INFLECTIONAL, 'keyword')"" I get an exception
how do I debug the underlying function to figure out what is being passed into it that is throwing the exception?
Or alternatively - what am I doing wrong in my declarations?