Entity SQL IN/Multiset issue

Question

I am using the following query in entity sql

 Select n.Name as NodeNameN  from Nodes  as n where n.Name IN MultiSet (@Test)

The value is substitued at runtime using the below code :

var reader = model.CreateQuery<System.Data.Common.DbDataRecord>(
                Statement,
                Parameters.ToArray());

When this is executed, the corresponding statment generated in sql server is as below :

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Nodes] AS [Extent1]
WHERE [Extent1].[Name] = @Test',N'@Test nvarchar(4000)',@Test=N'''BMA'',''Area A'','

Instead of IN Clause the = clause is substituted.

Am I doing any thing wrong here?

score 1 · Accepted Answer · answered Jan 04 '12 at 14:08

You have to parameterize your multiset with more than one SQL parameter - your current query is interpreted as a multiset with one element, so the SQL mapping done by Entity Framework is correct (an IN clause with one element is equivalent to a WHERE clause testing for equality with that element) - just pass more parameters:

Select n.Name as NodeNameN  from Nodes  as n where n.Name IN MultiSet (@Test1, @Test2)

Entity SQL IN/Multiset issue

1 Answers1