Parameter table in stored procedure and Entity Framework 4.0

Question

I have a stored procedure in SQL Server 2008 named "GetPrices" with a table parameter called "StoreIDs".

This is the type created for this TVP:

CREATE TYPE integer_list_tbltype AS TABLE (n int) 

I would like to call SP from my Entity Framework. But when I try to add a stored procedure to EDM, I get the following error:

The GetPrices function has a StoreIDs parameter in index 2, which has a table type data type that is not supported. The function has been excluded.

Is there a workaround? Any thoughts?

Fabio

Answer 1

Since you cannot use the table parameter, try passing to the CSV sting and store it in a line break.

There are many ways to split a string in SQL Server. This article discusses PRO and CON for almost every method:

"Arrays and Lists in SQL Server 2005 and Beyond when Table Parameters Don't Shorten" Erland Sommarskog

You need to create a split function. Here's how to use the split function:

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

I prefer the number table approach to split strings in TSQL , but there are many ways to split strings in SQL Server, see the previous link that explains the PRO and CON of each of them.

For the Numbers Table method to work, you need to perform this setting of a single time table that will create a Numbers table that contains rows from 1 to 10000:

 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 configured, create this split function:

 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 

Now you can easily split the CSV row into a table and join or use it, but you need to:

 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 

Answer 2

You can use the ObjectContext.Connection property to use ADO.NET to create and use your table parameters. This may not be acceptable, but if you want to use this great feature of SQL Server 2008 and EF, it seems you only choose.

Then you can choose to expand the partially generated context of the object using the method to take care of all the low-level ADO.NET elements. Like this:

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

Answer 3

I agree that the best solution in this case is to transfer to the CSV sting. I would suggest an easier way to split the csv string without creating tables and functions using CTE:

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

Answer 4

You might want to vote this when connecting to Microsoft .

Update: MS no longer uses Connect for features. They use it only for error reporting. To vote for the EF feature, you need to switch to EF User Voice .

To vote for this issue in User Voice, go here .