存储过程和实体框架4.0中的表值参数

I have a stored procedure in SQL Server 2008 called 'GetPrices' with a Table-Valued Parameter called 'StoreIDs'.

我在SQL Server 2008中有一个名为“GetPrices”的存储过程，其中有一个名为“StoreIDs”的表值参数。

This is the type i created for this TVP:

这是我为这个TVP创建的类型：

CREATE TYPE integer_list_tbltype AS TABLE (n int)

I would like to call the SP from my Entity Framework. But when I try to add the Stored Procedure to the EDM, i get the following error:

我想从我的实体框架中调用SP。但是当我尝试将存储过程添加到EDM时，我收到以下错误：

The function 'GetPrices' has a parameter 'StoreIDs' at parameter index 2 that has a data type 'table type' which is not supported. The function was excluded.

函数'GetPrices'在参数索引2处有一个参数'StoreIDs'，它具有不受支持的数据类型'表类型'。该功能被排除在外。

Is there any workaround this? Any thoughts?

这有什么解决方法吗？有什么想法吗？

Fabio

法比奥

4 个解决方案

#1

Since you can't use a table parameter, try passing in a CSV sting and have the stored procedure split it into rows for you.

由于您不能使用表参数，请尝试传入CSV sting并让存储过程将其拆分为行。

There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

有许多方法可以在SQL Server中拆分字符串。本文涵盖几乎所有方法的PRO和CON：

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

“表格值参数不会削减它时，SQL Server 2005及更高版本中的数组和列表”作者：Erland Sommarskog

You need to create a split function. This is how a split function can be used:

您需要创建拆分功能。这是分割函数的使用方式：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

我更喜欢使用数字表方法在TSQL中拆分字符串，但是有很多方法可以在SQL Server中拆分字符串，请参阅前面的链接，它解释了每个链接的PRO和CON。

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

要使Numbers Table方法起作用，您需要执行一次性表设置，这将创建一个包含1到10,000行的表号：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

设置Numbers表后，创建此拆分功能：

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(   ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO

You can now easily split a CSV string into a table and join on it or use it however you need:

您现在可以轻松地将CSV字符串拆分为表格并加入其中，或者根据需要使用它：

CREATE PROCEDURE YourProcedure
(
    @CSV_Param   varchar(1000)
)
AS

--just an example of what you can do
UPDATE t
    SET Col1=...
    FROM dbo.FN_ListToTable(',',@CSV_Param) dt
        INNER JOIN TBL_USERS                 t ON  CAST(dt.value AS INT)=t.id

GO

#2

You can use the ObjectContext.Connection property to use ADO.NET to create and use your table-valued parameters. This might not be acceptable, but if you want to use this awesome SQL Server 2008 feature and the EF, this seems to be you're only choise.

您可以使用ObjectContext.Connection属性来使用ADO.NET来创建和使用表值参数。这可能是不可接受的，但如果你想使用这个令人敬畏的SQL Server 2008功能和EF，这似乎只是你选择。

You can then choose to extent the partially generated object context with the method to take care of all the low level ADO.NET stuff. Like this:

然后，您可以选择使用该方法扩展部分生成的对象上下文，以处理所有低级ADO.NET内容。喜欢这个：

public partial class FriendsOnBoardEntities : ObjectContext
{
    public IList<int> GetPrices(int n)
    {
        // 'low-level' ado.net stuff here.
        // Use SqlParameters, SqlCommand and what not...
    }
}

#3

I agree that passing in a CSV sting is the best solution in this case. I would like to propose simpler way to split csv string, without creating tables and functions, by using CTE:

我同意在这种情况下传递CSV sting是最好的解决方案。我想通过使用CTE提出更简单的方法来分割csv字符串，而无需创建表和函数：

declare @separator char(1);
set @separator = ',';

;with baseCte as
(select left(@ValueList, charindex(@separator, @ValueList) - 1) as Value,
substring(@ValueList, charindex(@separator, @ValueList) + 1, len(@ValueList)) 
as rest
union all
select left(rest, charindex(@separator, rest) - 1) as Value, 
substring(rest, charindex(@separator, rest) + 1, len(rest)) from baseCte
where len(rest) > 1
)
select Value from baseCte
OPTION (MAXRECURSION 0);

#4

You might want to vote on this at microsoft connect

您可能想在microsoft connect上对此进行投票

Update: MS does not use Connect for features any more. They only use it for bug reports. To vote for an EF feature you need to go the EF User Voice site.

更新：MS不再使用Connect for features。他们只将它用于bug报告。要投票支持EF功能，您需要访问EF用户语音网站。

To vote for this specific issue on User Voice go here.

要在用户语音上投票支持此特定问题，请转到此处。

#1