0

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?

  • 1
    Is this SQL-Server (please tag with RDBMS, product and version)? If yes, you might start the *Profiler* and monitor the incoming calls. Doing so, you should see the call exactly as it is handed over to the server... – Shnugo Apr 12 '17 at 11:07
  • Tag the dbms you're using. That code is product specific. – jarlh Apr 12 '17 at 11:14
  • Sorry.. Azure SQL thanks! – karl schulmeisters Apr 12 '17 at 17:27
  • any suggestions from anyone? I'm really stuck here and frankly feeling stupid – karl schulmeisters Apr 13 '17 at 17:51
  • OK for some reason as a newbie I cannot post an answer.... so I will post the answer as a comment OK so this is poorly documented but here is the answer **MSFT Entity Foundation (EF) 5.0 passes in strings of Length 4000** So any function or procedure you declare that takes as an input of String from EF.. **MUST BE DECLARED AS `[n]varchar(4000)` ** or SQL will throw an exception for EF passing in a string that is too long. Bizarre – karl schulmeisters Apr 14 '17 at 07:43

0 Answers0