为什么实体框架不能看到我存储过程的列信息?

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:

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

9 个解决方案

#1

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

尝试将这一行添加到存储过程的开头:

SET FMTONLY OFF

You can remove this after you have finished importing.

您可以在完成导入后删除它。

#2

Whats happening here behind the scenes?

幕后发生了什么?

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).

当执行函数导入->得到列信息…Visual Studio使用所有的param值作为NULL来执行存储的proc(您可以通过MS SQL Profiler进行交叉检查)。
Doing step 1, the stored proc's resulting columns are returned with its data type and length info.

执行步骤1，存储的proc的结果列将以其数据类型和长度信息返回。
Once the column info is fetched, clicking on 'Create New Complex Type' button creates the Complex type of the SP in contention.

一旦获取了列信息，单击“创建新的复杂类型”按钮，就会创建复杂类型的SP争用。

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

在您的情况下，存储的proc params不是空的，因此Visual Studio调用失败，并返回无列。

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/

参考:http://mysoftwarenotes.wordpress.com/2011/11/04/entity -框架- 4% - e2%80%93 - -选择-存储-过程-返回-没有列2/——的一部分

#3

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

在完整性和制作简单的@benshabatnoam的回答中，刚开始的时候放下面的代码:

IF (1=2)
    SET FMTONLY OFF

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

注意:它在EF 6.1.3和Visual Studio 2015更新3中工作。

#4

You'r having this problem due to the temp table. All you need to do is: 1. Alter your stored procedure to return the select statemant without the temp table. 2. Go to the function import and get the column information. 3. Alter your stored procedure back to original.

由于临时表的原因，您有这个问题。你需要做的就是:1。修改您的存储过程，以在没有临时表的情况下返回select statemant。2。转到函数导入并获取列信息。3所示。将存储过程更改为原来的。

#5

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.

作为一种快速而又不干净的方法，可以让EF找到这些列，注释掉存储proc中的where子句(可能添加一个TOP 1来阻止它返回所有内容)，将proc添加到EF并创建复杂类型，然后再次取消where子句。

#6

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

如果使用临时表，则实体(EDMX)无法理解发生了什么。

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).

因此，使用列名称返回空结果，注释掉所有存储过程并在sql manager中执行，然后在visual studio中获取复杂类型。保存后，将存储过程返回到它的原始状态(未注释掉)。

good luck/

祝你好运/

#7

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.

我想给Sudhanshu Singh的回答加点东西:它工作得很好，但是如果你有更复杂的结构，就把它和一个表声明结合起来。

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.

注意，1=0保证它永远不会被执行，但是EF从它中扣除了结构。

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".

保存了存储过程之后，在Visual Studio中打开EDMX文件，刷新数据模型，转到实体框架模型浏览器。在模型浏览器中，定位你的存储过程，打开“编辑功能导入”对话框，选择“返回一个……”然后点击“获取列信息”按钮。

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").

它应该显示上面定义的结构。如果是这样，单击“创建新的复杂类型”，它将创建一个具有存储过程名称的名称。(附加“MyStoredProc_Result _Result”)。

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

现在你可以在combobox中选择它了。在同一个对话框中。

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).

当您需要更新某些东西时，请先更新SP，然后您可以回到编辑功能导入对话框，点击“更新”按钮(您不需要从头创建所有内容)。

#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

#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.

只需在没有引用的情况下添加select语句，执行存储的proc，去更新模型，编辑函数导入，获取列信息。这将填充新列。更新结果集，返回到存储的proc，并删除刚才添加的选择列表。然后执行存储的proc。这样，您的列就会在结果集中被填充。

    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 = '

设置@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

选择ID, VendorName, ItemName，类型从#T。

I hope this helps someone out there.

我希望这能帮助别人。

#1