LINQ passing Array for a parameter

[Chuck P]

I have a web page where a user can select multiple items for use in a select
statement. SELECT columns FROM table WHERE ID in (1,2,3). Since its a web
application I use a stored procedure to get the results.

Currently my sproc GetResults (@EventIdList varChar(@256))
uses a table value function that parses the item list and returns a table.
I use this method because I don't do dynamic SQL in sprocs and sprocs can't
take arrays.

Select columns from MyTable
INNER JOIN dbo.ParseIntToTable(@EventIdList,',') evn on MyTable.EventTypeID
= evn.ColumnValue


ALTER FUNCTION [dbo].[ParseIntToTable]
(
@StringList varchar(8000),
@Delimiter char(1)
)
RETURNS
@TempTable table
(
ColumnValue int
)
AS
BEGIN

DECLARE @StringValue varchar(1000) -- value taken off list
DECLARE @Pos int -- position of delimiter

SET @StringList = LTRIM(RTRIM(@StringList))
IF RIGHT(@StringList,1) <> @Delimiter
SET @StringList = @StringList + @Delimiter

SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)

IF REPLACE(@StringList, @Delimiter, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @StringValue = LTRIM(RTRIM(LEFT(@StringList, @Pos - 1)))
IF @StringValue <> ''
BEGIN
INSERT INTO @TempTable (ColumnValue)
VALUES (CAST(@StringValue AS int))
END
SET @StringList = RIGHT(@StringList, LEN(@StringList) - @Pos)
SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)

END
END
RETURN


In LINQ how do you write a query that takes an array for a parameter. (
select columns from table where ID IN (1,2,3) ).
The query result will be used in a GridView and needs to support paging and
sorting.

thanks,

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,

In Linq, "Contains" extension method could be converts to the TSQL "IN"
clause. I suggest you may check out the following article.

http://linqinaction.net/blogs/jwooley/archive/2007/08/06/use-the-new-linq-qu
ot-contains-quot-extension-method-for-the-sql-quot-in-quot-clause.aspx
[Use the new LINQ "Contains" extension method for the SQL "IN" clause ]

Hope this helps. Let me know if you have any more concern. We are glad to
assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,

I'm indeed sorry for delay. Due to some tool issue, my response failed to
send out when I replied you in yesterday.

In Linq, "Contains" extension method could be converts to the TSQL "IN"
clause, just as in the above reply.
Please feel free to reply me, if you have any more concern or there is
anything unclear. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Chuck P]

Thanks,
Interesting article. I saw the generated SQL which makes a bunch of
parameters for the IN clause. I think I will keep doing it the old way which
uses a Join. Should be much faster. I'll keep it in mind for case where
there aren't to many parameters though.

 

[Wen Yuan Wang [MSFT]]

Thanks for your reply. Chuck.
You are welcome.
If there is anything else we can help with, please feel free to let me know.
It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.