11

I am using Entity Framework with C# to make a Silverlight application. I have written some stored procedures which perform database intensive operations and I need to call them from Entity Framework after passing some parameters. In one of the steps, the users select a list of items for which they would need more details. This list of items (in the form of an array of integer IDs) need to be passed to the stored procedure to retrieve more information about those IDs. How do I pass this parameter to the stored procedure?

Anand
  • 729
  • 2
  • 10
  • 26
  • possible duplicate of [ExecuteStoreQuery with TVP parameters](http://stackoverflow.com/questions/5979706/executestorequery-with-tvp-parameters) – Lucero Jul 28 '11 at 15:13

3 Answers3

13

You can't pass table-valued parameters to SQL with the Entity Framework.

What you can do is create a delimited string like "1|2|3|4" and create a Split function in SQL that will return a table.

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
Id int identity(1,1),
Data nvarchar(100)
) 
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Then if you need to do something like select all items from a table based on what is in the delimited string passed to your proc:

SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM dbo.Split(@DelStr, '|'))
Dismissile
  • 32,564
  • 38
  • 174
  • 263
  • 2
    Thank you. Will it ever be possible to pass tables to Entity Framework? – Anand Jul 28 '11 at 15:16
  • I hope so. It seems that the SQL team creates a bunch of really neat features that I can never use because teams like Entity Framework and Linq to Sql never allow them to be used...and I'll be damned if I ever touch straight ADO.net again :) – Dismissile Jul 28 '11 at 15:20
  • I do have one question though. How did Colin Desmond here http://stackoverflow.com/questions/5979706/executestorequery-with-tvp-parameters manage to add a user defined table type to his EF model? – Anand Jul 28 '11 at 15:30
  • I'm thinking this could be subject to a SQL injection attack, dynamic SQL dependent on parameter inputs always scares me. – Dan Waterbly Sep 30 '13 at 21:20
  • Actually, it is possible with user defined types. – Triynko Jan 24 '18 at 17:26
4

If you are using SQL Server, which I assume is the case, you can create use a table valued parameter to do what you wish. Using a table valued parameter prevents you from parsing an input parameter within the stored procedure and removes the threat of a SQL injection attack by eliminating the use of dynamic SQL.

Here is a great blog article that covers how to do what you wish to do.

Using Table-Valued Parameters in SQL Server 2008 and C#

Dan Waterbly
  • 850
  • 7
  • 15
  • 1
    According to Dismissle table valued parameters may not be an option with Entity Framework. Be very careful if you choose to parse a varchar for input. – Dan Waterbly Jul 28 '11 at 15:01
-2

Is this what you were looking for?

Jay
  • 6,224
  • 4
  • 20
  • 23