Linq to SQL fulltext search procedure - weird error

I am in the process of translating an ASP.NET website I made from using table adapters and stored procedures to using Linq to SQL (.NET 4), because it makes the whole thing so much more maintainable.

The app is a library catalogue and I am using fulltext search in SQL Server 2008 R2 express to allow the user to search various fields, like title, author, publisher, etc. The existing fulltext search I have is a stored procedure which generates a dynamic query according to the search parameters specified by the user. In LinQ to SQL I was planning on reusing this same query, since there is no direct access to fulltext search.

The query by itself has always executed fine. Now I keep getting a "fulltext predicate null or empty" from the compiler at runtime, specifically at the line executing the stored procedure:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), section_ID, division_ID, start_Catalogue_Number, end_Catalogue_Number, start_Revision_Date, end_Revision_Date, start_Added_Date, end_Added开发者_如何学JAVA_Date, book_Type, searchTitle, searchAuthor, searchPublisher, searchDealer, searchPrinter, searchSummary, searchComments, searchString, searchStringsCombinator, searchConditionsCombinator, pageNumber, pageSize, resultCount);

So, I have tried rebuilding the query from scratch adding clause by clause to the stored procedure and the fulltext search was working fine, until that "fulltext predicate null or empty" error started coming up again.

I just can't see what is wrong with what I am doing and it is hitting my head against a brick wall repeatedly. Any help would be greatly appreciated.

The stored procedure which was working until a moment ago is:

ALTER PROCEDURE dbo.AAATest
(
    /*Non-fulltext search parameters*/
    @Section_ID int = null,
    @Division_ID int = null,
    @Start_Catalogue_Number int = null,
    @End_Catalogue_Number int = null,
    @Start_Revision_Date smalldatetime = null,
    @End_Revision_Date smalldatetime = null,
    @Start_Added_Date smalldatetime = null,
    @End_Added_Date smalldatetime = null,
    @Book_Type int = null,

    /*Fulltext search parameters*/
    @SearchTitle bit = 0,
    @SearchAuthor bit = 0,
    @SearchPublisher bit = 0,
    @SearchDealer bit = 0,
    @SearchPrinter bit = 0,
    @SearchSummary bit = 0,
    @SearchComments bit = 0,
    @SearchString nvarchar(4000) = NULL,
    @SearchStringsCombinator nvarchar(3) = 'OR',
    @SearchConditionsCombinator nvarchar(3) = 'OR',

    /*Paging variables*/
    @PageNumber int = 0,
    @PageSize int = 10,

    /*Output parameters*/
    @ResultCount int OUTPUT
)

AS

DECLARE @DynamicSearchQuery nvarchar(MAX),
        @DynamicSearchClause nvarchar(MAX),
        @DynamicSearchParameters nvarchar(MAX),
        @FullTextClauseSubjects nvarchar(MAX),
        @FullTextClauseKeywords nvarchar(MAX)

SELECT @DynamicSearchQuery = 'SELECT Teikoku_Lib_Current_Books.Book_ID, Teikoku_Lib_Current_Books.Book_Catalogue_Number, Teikoku_Lib_Current_Books.Book_ISBN_Number, Teikoku_Lib_Current_Books.Book_Title, Teikoku_Lib_Current_Books.Book_Author, Teikoku_Lib_Current_Books.Book_Publication_Date, Teikoku_Lib_Current_Books.Book_Revision_Date, Teikoku_Lib_Current_Books.Book_Publisher, Teikoku_Lib_Current_Books.Book_Edition_Date, Teikoku_Lib_Current_Books.Book_Edition_Number, Teikoku_Lib_Current_Books.Book_Printer, Teikoku_Lib_Current_Books.Book_Dealer, Teikoku_Lib_Current_Books.Book_Number_Of_Copies, Teikoku_Lib_Current_Books.Book_Part, Teikoku_Lib_Current_Books.Book_Summary, Teikoku_Lib_Current_Books.Book_Comments, Teikoku_Lib_Current_Books.Book_Section_ID, Teikoku_Lib_Current_Books.Book_Division_ID, Teikoku_Lib_Current_Books.Book_Added_Date, Teikoku_Lib_Current_Books.Book_Type';
    SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' ' + 'FROM Teikoku_Lib_Current_Books';
    SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' ' + 'WHERE ';

/*Add static search conditions*/
/*Section and division data*/
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Section_ID = @dynSection_ID OR @dynSection_ID IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Division_ID = @dynDivision_ID OR @dynDivision_ID IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
/*Book type*/
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Type = @dynBook_Type OR @dynBook_Type IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
/*Catalogue number*/
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Catalogue_Number >= @dynStart_Catalogue_Number OR @dynStart_Catalogue_Number IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Catalogue_Number <= @dynEnd_Catalogue_Number OR @dynEnd_Catalogue_Number IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
/*Revision date*/
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Revision_Date >= @dynStart_Revision_Date OR @dynStart_Revision_Date IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Revision_Date <= @dynEnd_Revision_Date OR @dynEnd_Revision_Date IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Added_Date >= @dynStart_Added_Date OR @dynStart_Added_Date IS NULL)';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + ' AND ';
SELECT @DynamicSearchQuery = @DynamicSearchQuery + '(Teikoku_Lib_Current_Books.Book_Added_Date <= @dynEnd_Added_Date OR @dynEnd_Added_Date IS NULL)';

/*Dynamic search conditions*/
IF @SearchString IS NOT NULL
BEGIN

    /*Set combinator for the search strings*/ 
    IF @SearchStringsCombinator <> 'OR' AND @SearchStringsCombinator <> 'AND'
        SELECT @SearchStringsCombinator = 'OR'

    /*Replace underscore separators used in the querystring with the search string combinator keyword*/
    SELECT @SearchString = REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@SearchString)), '_', ' '), '　', ' '))), ' ', ' ' + @SearchStringsCombinator + ' ');

    /*Fulltext search on the title*/
    IF @SearchTitle > 0
        SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Title, @dynSearchString)';

    /*Fulltext search on the author*/
    IF @SearchAuthor > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Author, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' + 'AND CONTAINS(Teikoku_Lib_Current_Books.Book_Author, @dynSearchString)';
        END

    /*Fulltext search on the publisher*/
    IF @SearchPublisher > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Publisher, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' + 'AND CONTAINS(Teikoku_Lib_Current_Books.Book_Publisher, @dynSearchString)';
        END

    /*Fulltext search on the dealer*/
    IF @SearchDealer > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Dealer, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' +'CONTAINS(Teikoku_Lib_Current_Books.Book_Dealer, @dynSearchString)';
        END

    /*Fulltext search on the printer*/
    IF @SearchPrinter > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Printer, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' + 'CONTAINS(Teikoku_Lib_Current_Books.Book_Printer, @dynSearchString)';
        END

    /*Fulltext search on the summary*/
    IF @SearchSummary > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Summary, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' + 'CONTAINS(Teikoku_Lib_Current_Books.Book_Summary, @dynSearchString)';
        END

    /*Fulltext search on the comments*/
    IF @SearchComments > 0
        BEGIN
        IF @DynamicSearchClause IS NULL
            SELECT @DynamicSearchClause = 'CONTAINS(Teikoku_Lib_Current_Books.Book_Comments, @dynSearchString)';
        ELSE
            SELECT @DynamicSearchClause = @DynamicSearchClause + ' ' + @SearchConditionsCombinator + ' ' + 'CONTAINS(Teikoku_Lib_Current_Books.Book_Comments, @dynSearchString)';
        END

    /*Add the dynamic conditions to the query*/
    IF @DynamicSearchClause IS NOT NULL
        SELECT @DynamicSearchQuery = @DynamicSearchQuery + 'AND (' + @DynamicSearchClause + ')';

END

/*Dynamic query variables declaration*/
SELECT @DynamicSearchParameters = '@dynSection_ID int = null, @dynDivision_ID int = null, @dynStart_Catalogue_Number int = null,
    @dynEnd_Catalogue_Number int = null, @dynStart_Revision_Date smalldatetime = null, @dynEnd_Revision_Date smalldatetime = null,
    @dynStart_Added_Date smalldatetime = null, @dynEnd_Added_Date smalldatetime = null, @dynBook_Type int = null, @dynSearchString nvarchar(4000)';

/*Create table to hold results temporarily*/
CREATE table #searchResults
(
    temp_Book_ID int IDENTITY(1,1),
    Book_ID int,
    Book_Catalogue_Number int,
    Book_ISBN_Number nvarchar(13),
    Book_Title nvarchar(200),
    Book_Author nvarchar(200),
    Book_Publication_Date datetime,
    Book_Revision_Date datetime,
    Book_Publisher nvarchar(100),
    Book_Edition_Date datetime,
    Book_Edition_Number smallint,
    Book_Printer nvarchar(100),
    Book_Dealer nvarchar(100),
    Book_Number_Of_Copies int,
    Book_Part int,
    Book_Summary nvarchar(MAX),
    Book_Comments nvarchar(MAX),
    Book_Section_ID int,
    Book_Division_ID int,
    Book_Added_Date datetime,
    Book_Type int
)

INSERT INTO #searchResults  EXEC sp_executesql @DynamicSearchQuery, @DynamicSearchParameters, @Section_ID, @Division_ID, @Start_Catalogue_Number, 
    @End_Catalogue_Number, @Start_Revision_Date, @End_Revision_Date, @Start_Added_Date, @End_Added_Date, @Book_Type, @SearchString;

SELECT #searchResults.Book_ID, #searchResults.Book_Catalogue_Number, #searchResults.Book_ISBN_Number, 
    #searchResults.Book_Title, #searchResults.Book_Author, #searchResults.Book_Publication_Date, #searchResults.Book_Revision_Date, 
    #searchResults.Book_Publisher, #searchResults.Book_Edition_Date, #searchResults.Book_Edition_Number, #searchResults.Book_Printer, 
    #searchResults.Book_Dealer, #searchResults.Book_Number_Of_Copies, #searchResults.Book_Part, #searchResults.Book_Summary,
    #searchResults.Book_Comments, #searchResults.Book_Section_ID, #searchResults.Book_Division_ID, #searchResults.Book_Added_Date, 
    #searchResults.Book_Type 
    FROM #searchResults WHERE (#searchResults.temp_Book_ID >= (@PageNumber * @PageSize) AND #searchResults.temp_Book_ID <=  ((@PageNumber + 1) * @PageSize));

SELECT @ResultCount = COUNT(*) FROM #searchResults;

DROP TABLE #searchResults;

RETURN

I know this is quite long, sorry. But, it is essentially the same bit of code over and over again. The last bit in the stored procedure I need to get the total number of results the query returns so I can get my paging interface to display correctly.

I am then calling that from my BLL as

return db.AAATest(SectionID, DivisionID, StartCatalogueNumber, EndCatalogueNumber, StartRevisionDate, EndRevisionDate,
        StartAddedDate, EndAddedDate, BookType, SearchTitle, SearchAuthor, SearchPublisher, SearchDealer, SearchPrinter,
        SearchSummary, SearchComments, SearchStrings, SearchStringCombinator, 
        SearchConditionsCombinator, PageNumber, PageSize, ref TotalRecords);

And as a test I am passing arguments

lvCatalogueContent.DataSource = currentBook_Repository.GetCurrentBooksFiltered(null, null, null, null, null, null, null,
        null, null, true, false, false, false, false, false, false, true, true, "橋", null, null, null, null, null, 0, 20,
        ref totalRecords);

Those parameters work absolutely fine when I test the stored procedure in SQL Server... And they worked in ASP.NET until a moment ago.

UPDATE:

The exact error I am getting in the compiler is:

SqlException: "Null or Empty full-text predicate" Class: 15 ErrorCode: -2146232060

---

OK, after spending all morning looking at the stored procedure bit by bit, I have found that the error is caused by the following line:

/*Replace underscore separators used in the querystring with the search string combinator keyword*/
SELECT @SearchString = REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@SearchString)), '_', ' '), '　', ' '))), ' ', ' ' + @SearchStringsCombinator + ' ');

This is wrapped in a

IF @SearchString IS NOT NULL 
BEGIN
END

block to avoid a fractional amount of unnecessary extra computations, but this seems to be causing the compiler to think that the full-text predicate is null or empty. I have moved that and the previous line which checks whether combinator is OR or AND outside the wrapper if block and it seems to be working (for now anyway)...

Another alternative I found was to treat the search string in code before calling the stored procedure.

Hope this helps somebody someday.