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?
1
-
possible duplicate of ExecuteStoreQuery with TVP parametersLucero– Lucero2011-07-28 15:13:01 +00:00Commented Jul 28, 2011 at 15:13
Add a comment
|
3 Answers
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, '|'))
5 Comments
Anand
Thank you. Will it ever be possible to pass tables to Entity Framework?
Dismissile
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 :)
Anand
I do have one question though. How did Colin Desmond here stackoverflow.com/questions/5979706/… manage to add a user defined table type to his EF model?
Dan Waterbly
I'm thinking this could be subject to a SQL injection attack, dynamic SQL dependent on parameter inputs always scares me.
Triynko
Actually, it is possible with user defined types.
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.
1 Comment
Dan Waterbly
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.
Is this what you were looking for?
2 Comments
Lucero
How does this address passing in arrays/lists of items as parameter?
Adam Oakley
Answers should contain more information than just a link to another site.
