CodeFirstFunctions

CodeFirstFunctions copied to clipboard

HI, I have an issue with full-text. The sql script I execute on the server is

select * from dbo.ufn_search('(ISABOUT("mysearch") OR "mysearch")')

My function in the context is

[DbFunction("WordBankContext", "ufn_Search")]
public IQueryable<Search> FullSearch(string search)
{
    if (!string.IsNullOrEmpty(search))
    {
        string[] tmp = search.Split(' ');
        search = "";
        foreach (string s in tmp)
        {
            if (!string.IsNullOrEmpty(search))
                search += " AND ";
            search += $"(ISABOUT(\"{s}\") OR \"{s}\")";
            }
    }

    var searchParameter = search != null ? new ObjectParameter("search", search) 
                          : new ObjectParameter("search", typeof(string));
    return ((IObjectContextAdapter)this)
               .ObjectContext.CreateQuery<Search>(string.Format("[{0}].{1}", 
                                                  GetType().Name, 
                                                  "[ufn_Search](@search)"), 
                                                  searchParameter);
}

You did not describe what problem you are hitting.

Oh, sorry. I revive an error because he parameter is not valid in the sql function. If I copy 'search' and I use it as in my sql example, it is working. I don't know what it happens in CreateFunction

What is the error. Provide all the details including stack trace.

Error The full-text query parameter for Fulltext Query String is not valid.

StackTrace

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Function in WordBankContext.cs

[DbFunction("WordBankContext", "ufn_Search")]
public IQueryable<Search> FullSearch(string search)
{
    if (!string.IsNullOrEmpty(search))
    {
        string[] tmp = search.Split(' ');
        search = "";
        foreach (string s in tmp)
        {
            if (!string.IsNullOrEmpty(search))
                search += " AND ";
            search += $"(ISABOUT('{s}') OR '{s}')";
        }
    }

    var searchParameter = search != null ? new ObjectParameter("search", search) 
                          : new ObjectParameter("search", typeof(string));
    return ((IObjectContextAdapter)this).ObjectContext
               .CreateQuery<Search>(string.Format("[{0}].{1}", 
               GetType().Name, "[ufn_Search](@search)"), searchParameter);
}

ufn_Search in database

CREATE FUNCTION [dbo].[ufn_Search] 
(	
	@search nvarchar(500)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT [ID]
			,[LanguageId]
			,[Title]
			,'' as WordType
			,[Category]
			,[Tags]
			,[LikeNumber]
			,[VisitNumber]
			,ftt.Rank
	FROM [dbo].[References]
	INNER JOIN
	FREETEXTTABLE([References], (Title, [Source]), @search) as ftt
	ON
	ftt.[KEY]=[References].Id
);

Have you tried tracing what EF is sending to the SqlServer (e.g. using Sql Server Profiler)? This should help figure out what is going on.

Using full-text query in a TVF requires the variable that comes from EF to be the same size as the variable defined in the TVF.

By default it will create a variable NVARCHAR(4000) meaning your TVF will need to have an input variable of NVARCHAR(4000).

exec sp_executesql N'SELECT 
    [Extent1].[id] AS [id]
    FROM   [dbo].[Table] AS [Extent1]
    INNER JOIN [dbo].[Search](@Search) AS [Extent2] ON [Extent1].[id] = [Extent2].[Id]',N'@Search nvarchar(4000)',@Search=N'test'

CREATE FUNCTION [dbo].[Search]
(
	@Search NVARCHAR(4000) = '""'
)
RETURNS TABLE AS RETURN
(
	SELECT a.[KEY] AS Id, ISNULL(a.[RANK], 0) AS [Rank]
	FROM FREETEXTTABLE([dbo].[Table], *, @Search, LANGUAGE 0x0) a
)

Full-text query also requires a non blank or a non null value to be passed when called or it will error.

Using full-text query in a TVF requires the variable that comes from EF to be the same size as the variable defined in the TVF.

By default it will create a variable NVARCHAR(4000) meaning your TVF will need to have an input variable of NVARCHAR(4000).

exec sp_executesql N'SELECT 
    [Extent1].[id] AS [id]
    FROM   [dbo].[Table] AS [Extent1]
    INNER JOIN [dbo].[Search](@Search) AS [Extent2] ON [Extent1].[id] = [Extent2].[Id]',N'@Search nvarchar(4000)',@Search=N'test'

CREATE FUNCTION [dbo].[Search]
(
	@Search NVARCHAR(4000) = '""'
)
RETURNS TABLE AS RETURN
(
	SELECT a.[KEY] AS Id, ISNULL(a.[RANK], 0) AS [Rank]
	FROM FREETEXTTABLE([dbo].[Table], *, @Search, LANGUAGE 0x0) a
)

Full-text query also requires a non blank or a non null value to be passed when called or it will error.

Worked in my case