Why can't Entity Framework see my Stored Procedure's column information?

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

Bazar

possible duplicate of Entity Framework can't handle a simple table variable? – Dicentra

Yes, that was exactly the issue. I added SET FMTONLY OFF to the top of the stored procedure and it found the columns. Thanks. – Bazar

Answer 1

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

SET FMTONLY OFF

You can remove this after you have finished importing.

Stopcock

This alone wouldn't work for the stored proc. in the question because there's no handler for when the input parameter is NULL. Explanation here: https://mcmap.net/q/275826/-why-can-39-t-entity-framework-see-my-stored-procedure-39-s-column-information – Reinertson

Answer 2

Whats happening here behind the scenes?

1. While doing function import -> Get Column Information ... Visual Studio executes the stored proc with all the param values as NULL (you can cross-check this through MS SQL Profiler).

2. Doing step 1, the stored proc's resulting columns are returned with its data type and length info.

3. Once the column info is fetched, clicking on 'Create New Complex Type' button creates the Complex type of the SP in contention.

In your case, the stored proc params are not nullable, hence the Visual Studio call fails and returns no columns.

How to handle this?

IF (1=0) 
BEGIN 
    SET FMTONLY OFF 
    if @param1 is null and @param2 is null then
        begin
            select
            cast(null as varchar(10)) as Column1,
            cast(null as bit) as Column2,
            cast(null as decimal) as Column3
        END
END   

To be precise (in your case):

IF (1=0) 
BEGIN 
    SET FMTONLY OFF 
    if @SearchString is null then
        BEGIN
            select
            cast(null as int) as ID,
            cast(null as varchar(255)) as VendorName,
            cast(null as varchar(255)) as ItemName,
            cast(null as varchar(2)) as Type
        END
END   

Reference: http://mysoftwarenotes.wordpress.com/2011/11/04/entity-framework-4-%E2%80%93-the-selected-stored-procedure-returns-no-columns-part-2/

Lorgnon

Thank you for that hint! One question: Why do you need if @SearchString is null then? If I understand it right, you just need IF (1=0) at the beginning to prevent execution (always false) and allow for column scanning. In my case it worked without the null checking. – Calycine

The info on the stored proc being executed with NULL parameters is gold! I had a missing table when porting my app using VS to a new environment, causing the stored proc to silently fail when EF tried to call it to get the column values to build the complex type. Your answer led me to drop to a new query window in SSMS and execute the stored proc with a null input parameter, duplicating hat EF does, thereby discovering my silly error that EF was silently swallowing. – Degas

Answer 3

In completeness and simplifying @benshabatnoam's answer, just put the following code at the beginning:

IF (1=2)
    SET FMTONLY OFF

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

Autograft

Answer 4

If you are using temporary table, the Entity (EDMX) cant understand what is going on.

So return empty result with the columns name, comment out all your stored procedure and execute in the sql manager, then get the complex type in visual studio. After saving, return your stored procedure to it's original state (uncommented that is).

good luck/

Desai

Answer 5

You're having this problem due to the temp table.

All you need to do is:

1. Alter your stored procedure to return the select statement without the temp table.
2. Go to the function import and get the column information.
3. Alter your stored procedure back to the original.

Stopcock

Answer 6

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 [dbo].[MyStoredProc] 
AS
BEGIN

SET NOCOUNT ON;

IF (1=0) -- it never gets executed, but the EF deducts the structure from it
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).

Calycine

This method worked for me while the ordinary select statement did not. My issue was actually that EF was converting non-nullable bit columns to bool?. I added not null & default constraints to my table variable and it worked. I also had to delete the old complex type entity before it would actually re-generate the new one. – Pyszka

Answer 7

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.

Benthos

Answer 8

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

Batiste

Answer 9

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.

Heddle

Answer 10

This is the only correct answer and can be found from here

https://mcmap.net/q/276777/-stored-procedure-returns-incorrect-scalar-value-of-1-instead-of-return-value

Basically, EF knows that it's always going to return the number of rows or -1 if NO COUNT is on, or anything returned from the SQL stored procedure that's called by return <some integer>. For that reason, no matter what stored procedure you import, the type will always be nullable<int>. You can only return an integer from an SQL stored procedure. So, EF gives you a way to edit your function. I would imagine that if you edited it manually you would overwrite it on refresh, but I can't confirm that. Either way, this is the facility provided by EF to deal with this issue.

Click on your .emdx file. It has to be the one you selected in the Solution Explorer. Select Model Browser (Right beside Solution Explorer tab, above Properties). Expand Function Imports, locate your stored procedure, right click, click Edit. Select your variable type. It can either be a primitive type or you can click Get Complex Type. Click Get Column Information. I have confirmed this survives a model refresh.

Why can you only return an integer from a stored procedure? I don't really know, but this return definition explains that you can only return an integer: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15

Hosiery