Model First - Full Text Search Stored Procedure -

Question

I have looked for a few days for a solution but can't find one.

I am creating a stored procedure to search a table using fulltext search. I will then combine the result from 15 of these stored procedures into a list ordered by their ranking.

Here is the code for the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SpAdvancedSearchICAPUsers]
    @searching nvarchar(100) = ''
AS
BEGIN
    SET NOCOUNT ON;
    Set FMTONLY OFF;

    DECLARE @SQLQuery AS NVarchar(max)

    IF @searching IS NULL OR @searching = ''
    BEGIN
        SET @SQLQuery = 'SELECT (all field names listed separated by commas) FROM Users'
    END
    ELSE
    BEGIN
        SET @SQLQuery = 'SELECT (all field names listed separated by commas), fts.Rank FROM Users as p JOIN
                    FREETEXTTABLE(Users, (fields to search for listed separated by commas), @searching) 
                    as fts on p.userID = fts.[KEY] WHERE 1=1'
    END

    EXEC @SQLQuery
END

I did approached this project doing Model first. I added my stored procedures to my model by right clicking and pressing: Add New > Function Import...

I set the name of the function import, selected the stored procedure, selected the "Returns a Collection Of" to Entities: (desired data type the SP returns, in this case, Users).

When I use the stored procedure like this:

newSearch.Users = db.SpAdvancedSearchICAPUsers(search); //search is a string

newSearch.Users is an IENumerable<Users>. It says the return type is an int for the stored procedure. I get the following error:

Cannot implicitly convert type 'int' to 'System.Collections.Generic.IEnumerable;

I have also tried adding a parameter definition variable as such right below declaring @SQLQuery like this:

Declare @ParamDefinition As NVarchar(4000)

and then I set it and try and run it like this:

Set @ParamDefinition = '@searchingnvarchar(100)'

Exec sp_executesql @SQLQuery, @ParamDefinition, @searching

@searching is the string which is passed in to search.

I have even tried dragging all of my tables into a DBML file, because I've used stored procedures this way successfully before. When I dragged in the SP to the table I get this message:

UPDATE: Eddie in the comments specified that in the DBML after your drag in the stored procedure, you can set the return type (I chose the User object type). This works. I would rather not do this though. I'll have it as a temporary solution but I have 15 stored procedures I'd have to do that to.

Does anyone know how to get the correct output from the stored procedure using the EDMX file instead of a DBML? When I look at the properties after right clicking in the EDMX and selection "Mapping Details > Stored Procedures / Functions". The return type in the EDMX doesn't have anything. It has no drop downlist, nothing.

Answer 1

I'm not sure if I'm hitting the spot here, but if you are doing something like

newSearch.Users = db.SpAdvancedSearchICAPUsers(search);

and

newSearch.Users

is of type User Entity, then perhaps writing the search statement as

    Set @SQLQuery = 'SELECT p.* FROM Users as p JOIN
                     FREETEXTTABLE(Users, (fields to search for listed separated by commas), @searching) 
                     as fts on p.userID = fts.[KEY] WHERE 1=1
                     ORDER BY fts.[Rank]'

would work for the simple fact that it is returning the fields a User Entity expects (i.e.: no rank field).

To better diagnose whether this is the case or not, the question turns to:

Does the same problem happen when you send an empty search field?

i.e.:

newSearch.Users = db.SpAdvancedSearchICAPUsers('');

If it works for the latter case, then perhaps the aforementioned change would do.

Please let me know.