Stored Procedure Return Type Can Not Be Detected

I am trying to drag this procedure over to the dbml in VS 2012 and I am getting the return type cannot be detected message.

I have tried these: LINQ to SQL – Stored Procedure Return Type Error The return types for the following stored procedures could not be detected

I tried re-writing the procedure as a CTE and also as union to remove the OR, but it is giving me the same message.

The only return type in the designer properties for the method is int32.

Here is my procedure:

ALTER PROCEDURE [dbo].[GetStringFromFiles]
    @SearchWord NVARCHAR(100) = null
AS
BEGIN

    SET NOCOUNT ON
    SET @SearchWord = UPPER(@SearchWord);

Select    
       a.FileId     <---Guid
     , a.FileData   <---Binary
     , a.BaselineId <---Guid
     , a.FileName   <---NVARCHAR
     , a.FileExtension  <---NVARCHAR
     , b.FileByItemId   <----Guid
     , b.ItemId   <---Guid
From FileTable a

    Inner Join

     FileByItem b on a.FileId = b.FileId 

     WHERE CONTAINS(a.FileData,'FORMSOF(INFLECTIONAL, @SearchWord)') or FREETEXT(a.FileData, @SearchWord)
    RETURN 1
END

UPDATE:

A. I can add it if I comment out the entire Where Clause — Auto-Generated-ReturnType

B. If I take away the or and just use:

WHERE CONTAINS(a.FileData,'FORMSOF(INFLECTIONAL, @SearchWord)')

it lets me add it — Auto-Generated-ReturnType

C. If I just use

WHERE FREETEXT(a.FileData, @SearchWord)

It throws the error so it doesnt like the FREETEXT

If I comment the where clause out and add it and let it generate the return type then alter the procedure in the db are there any issues with that?

Answer

The line

RETURN 1

is the culprit in both the cases. Lose it and Linq should be able to detect the types your result set.

Linq to Sql won’t get the results directly from a Stored procedure. Instead an ISingleResult will be returned. You have to enumerate through the result set to get the values returned from the stored procedure

Advertisement

Answer