none
Imported function causing issues RRS feed

  • Question

  • (Note the solution of nvarchar(4000) that works all over the web doesn't work for me for some reason. Hard coding the @keywords to a fixed text makes it work but obviously isn't useful)
    I have the following function:
    CREATE FUNCTION SearchContacts
          (@keywords nvarchar(4000))
    returns table
    as
      return (select [KEY],[rank]
                from  FREETEXTTABLE(Members, *, @keywords))
    Obviously this is my hack to get around full text searching not being in Linq to Entities
    So I've added this by updating the model from the database.
    Then right clicked and imported the function. Check off iscomposable so that I had options, and got the results and made a complex type out of the results.
    Problem is that I'm getting the following error every time I call this:
    The full-text query parameter for Fulltext Query String is not valid.
    If I execute the sql that it passes directly in management studio it works fine.
    What am I doing wrong?

    • Edited by JohnGalt Monday, November 7, 2011 9:23 PM Update
    Monday, November 7, 2011 9:17 PM

All replies

  • Hi JohnGalt,

    I'm not sure if your problem ralates to the Fixed length entity key.

    You can refer here: http://blogs.msdn.com/b/alexj/archive/2009/05/20/tip-20-how-to-deal-with-fixed-length-keys.aspx

    http://msdn.microsoft.com/en-us/library/dd283139.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 8, 2011 8:42 AM
    Moderator
  • If you run SQL Profiler, does the SQL you send to the database look all right?
    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, November 8, 2011 1:17 PM
  • It's definitely not the KB article on fixed length entity key. The key in the database is a sequential guid and it's passing an nvarchar and I never use nchar or char in any fields.

     

    Tuesday, November 8, 2011 2:00 PM
  • Here's what profiler saw as the query:

     

     

    exec sp_executesql N'SELECT 

    1 AS [C1], 

    [Extent1].[ID] AS [ID], 

    [Extent1].[Name] AS [Name], 

    REPLACE([Extent1].[Address], N''

    '', N'', '') AS [C2], 

    CASE WHEN ([Extent1].[Email] = @p__linq__0) THEN [Extent1].[SiteURL] ELSE [Extent1].[Email] END AS [C3], 

    [Extent1].[Phone1] AS [Phone1], 

     CAST( [Extent1].[MemberType] AS tinyint) AS [C4], 

    [Extent2].[rank] AS [rank]

    FROM  [dbo].[Members] AS [Extent1]

    INNER JOIN [dbo].[SearchContacts](@keywords) AS [Extent2] ON [Extent1].[ID] = [Extent2].[KEY]',N'@keywords nvarchar(max) ,@p__linq__0 nvarchar(max) ',@keywords=N'cardi',@p__linq__0=N''

     

     

    Clearly it's passing keywords forced as nvarchar(max) which won't work.  How do I get it to send it as a proper nvarchar(4000)?

     

    Thanks!

    Tuesday, November 8, 2011 2:01 PM
  • And if you take the query above and change the nvarchar(MAX) to nvarchar(4000) it works?

     

    Can you show me the <Function Name="SearchContacts"> tags from your .edmx ?

     

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, November 8, 2011 2:59 PM
  • I tried replacing max with 4000 and it still throws the same error in sql... don't have a clue why. Any one have solutions?
    Tuesday, November 8, 2011 7:39 PM
  • Ping?Pong?
    Thursday, November 10, 2011 1:20 PM
  • If you can replicate the problem with just SQL, try to ask in a MSSQL forum, maybe the people there will be able to help.
    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Thursday, November 10, 2011 4:19 PM
  • There's two problems:

     

    1. EF is passing (max) to the function in the first place. I need to get that solved. I think this is the right forum for it.

    2. Why is the query failing even after that is manually fixed? (I'll pass this one to the sql people... Thanks!

     

    Thursday, November 10, 2011 4:28 PM
  • Hi JohnGalt,

    I'm not sure how do you import the function to EF. TVF is not supported in EF4, I think you can try to use Stroed Procedure and import the Stroed Procedure to EF as Function.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 15, 2011 6:08 AM
    Moderator