views:

18

answers:

0

Hi,

I have something like the following table to search some text:

SearchedTextTable
Id int primary key
Text nvarchar(max)
Code int

The Text is full text indexed, so I made the following table valued function to work with LinqToSQL:

ALTER FUNCTION [dbo].[SearchText] (@keywords nvarchar(4000))
returns @results table (Id int not null)
as
begin   
    INSERT @results
        SELECT 
            Id
        from SearchedTextTable
        where contains(Text,@keywords)  
    return;
end;

What I want to do is apply some conditions before searching text, like the following code:
NOTE: The conditions is actually kind of complex, so I don't want to put them into the table valued function.

using(var contetxt= new FooDataContext())
{
 var list = context.SearchedTextTable.Where(x => x.Code==code);
 var results = context.SearchText(list, keywords).ToList();
}

And The SearchText function should be like the following:

ALTER FUNCTION [dbo].[SearchText] (@table table, @keywords nvarchar(4000))
returns @results table (Id int not null)
as
begin   
    INSERT @results
        SELECT 
            Id
        from @table
        where contains(Text,@keywords)  
    return;
end;

Is this possible? If so could you tell me how?

Thanks in advance,
Yoo

EDIT

After some tests, seems the performance isn't affected anything even if I set some conditions before doing full text search. Both of them return less than 10ms.