Why can't Entity Framework see my Stored Proce

Question

I have the following stored procedure and when I attempt to Function Import it says my Stored Procedure returns no columns. What am I missing? Any Suggestions?

The Proc:

ALTER PROCEDURE [healthc].[ev_kc_Products_Search]
(
    @SearchString   VARCHAR(1000)
)
AS
SET NOCOUNT ON

    DECLARE @SQL    VARCHAR(max),
        @SQL1   VARCHAR(max),
        @Tag    VARCHAR(5)

    CREATE TABLE #T
    (   ID      INT,
        VendorName  VARCHAR(255),
        ItemName        VARCHAR(255),
        Type        VARCHAR(2),
        Sequence        TINYINT
    )


 SET @SQL = '

    INSERT  #T

    SELECT  VendorID ID,
        Name VendorName,
        NULL ItemName,
        ''V'' Type,
        0 Sequence
    FROM    tblVendors
    WHERE   '+REPLACE(@SQL1,@Tag,'Name')+'

    UNION ALL

    BLAH BLAH BLAH'


 EXEC(@SQL)


 SELECT ID, VendorName, ItemName, Type FROM #T

Answer 1

I'd like to add something to Sudhanshu Singh's answer: It works very well, but if you have more complex structures, combine it with a table declaration.

I have used the following successfully (place it at the very beginning of your stored procecure):

CREATE PROCEDURE [EY].[MyStoredProc] 
AS
BEGIN

SET NOCOUNT ON;

IF (1=0) 
BEGIN 
    SET FMTONLY OFF 
        BEGIN
            -- declaration + dummy query 
            -- to allow EF obtain complex data type:
            DECLARE @MyStoredProcResult TABLE(
                ID      INT,
                VendorName  VARCHAR(255),
                ItemName    VARCHAR(255),
                Type        VARCHAR(2),
                Sequence    TINYINT
                );
            SELECT * FROM @MyStoredProcResult WHERE (1=0)
        END
END   

-- your code follows here (SELECT ... FROM ...)
-- this code must return the same columns/data types
--
-- if you require a temp table / table variable like the one above
-- anyway, add the results during processing to @MyStoredProcResult
-- and then your last statement in the SP can be
-- SELECT * FROM @MyStoredProcResult
END

Note that the 1=0 guarantees that it never gets executed, but the EF deducts the structure from it.

After you have saved your stored procedure, open the EDMX file in Visual Studio, refresh the data model, go to the Entity Frameworks model browser. In the model browser, locate your stored procedure, open up the "Edit Function Import" dialog, select "Returns a collection of ... Complex", then click on the button "Get Column Information".

It should show up the structure as defined above. If it does, click on "Create New Complex Type", and it will create one with the name of the stored procedure, e.g. "MyStoredProc_Result" (appended by "_Result").

Now you can select it in the combobox of "Returns a collection of ... Complex" on the same dialog.

Whenever you need to update something, update the SP first, then you can come back to the Edit Function Import dialog and click on the "Update" button (you don't need to re-create everything from scratch).

Answer 2

I had this issue, what I had to do was create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
(  ID      INT,
    VendorName  VARCHAR(255),
    ItemName        VARCHAR(255),
    Type        VARCHAR(2),
    Sequence        TINYINT
);
GO

Your Stored Procedure will now look like this:

ALTER PROCEDURE [healthc].[ev_kc_Products_Search]
(
@SearchString   VARCHAR(1000)
)
AS
SET NOCOUNT ON

DECLARE @SQL    VARCHAR(max),
    @SQL1   VARCHAR(max),
    @Tag    VARCHAR(5)

@T [schema].T1

SET @SQL = 'SELECT  VendorID ID,
    Name VendorName,
    NULL ItemName,
    ''V'' Type,
    0 Sequence
    FROM    tblVendors
    WHERE   '+REPLACE(@SQL1,@Tag,'Name')+'
    UNION ALL
    BLAH BLAH BLAH'

INSERT INTO @T
EXEC(@SQL)

SELECT ID, VendorName, ItemName, Type FROM @T

Answer 3

Try adding this line to the beginning of your stored procedure:

SET FMTONLY OFF

You can remove this after you have finished importing.

Answer 4

in completeness and making simple @benshabatnoam answer, just put the following code at the begining :

IF (1=2)
    SET FMTONLY OFF

Note: it works in EF 6.1.3 and Visual Studio 2015 Update 3

Answer 5

Just add the select statement without the quotation, execute the stored proc, go get update the model, edit your function import and get column information. This should populate the new columns. Update the result set and go back to your stored proc and remove the select list you just added. And execute the stored proc. This way your columns will get populated in the result set. See below where to add the select list without quote.

    ALTER PROCEDURE [healthc].[ev_kc_Products_Search]
(
    @SearchString   VARCHAR(1000)
)

AS
SET NOCOUNT ON;
SELECT  VendorID ID,
        Name VendorName,
        NULL ItemName,
        ''V'' Type,
        0 Sequence
    FROM    tblVendors

DECLARE @SQL    VARCHAR(max),
    @SQL1   VARCHAR(max),
    @Tag    VARCHAR(5)

CREATE TABLE #T
(   ID      INT,
    VendorName  VARCHAR(255),
    ItemName        VARCHAR(255),
    Type        VARCHAR(2),
    Sequence        TINYINT
)

SET @SQL = '

INSERT  #T

SELECT  VendorID ID,
    Name VendorName,
    NULL ItemName,
    ''V'' Type,
    0 Sequence
FROM    tblVendors
WHERE   '+REPLACE(@SQL1,@Tag,'Name')+'

UNION ALL

BLAH BLAH BLAH'

EXEC(@SQL)

SELECT ID, VendorName, ItemName, Type FROM #T

I hope this helps someone out there.

Answer 6

As a quick and dirty way to make EF find the columns, comment out the where clause in your stored proc (maybe add a TOP 1 to stop it returning everything), add the proc to the EF and create the Complex Type, then uncomment the where clause again.