How to use parameters in Entity Framework in a "in" clause?

Question

I am using Entity Framework 4.0 and I want to use the following query:

To do that I do the following:

strSQLQuery = "select * from MyTable where IDData IN (@IDs)";
lstParameters.Clear();
myParameter = new SqlParameter("@IDs", strIDs);
lstParameters.Add(myParameter);

myContext.MyTable.SqlQuery(strSQLQuery, lstParameters.ToArray<object>()).ToList<MyTable>();

But I get an exception that say that it is not possible to convert nvarchar to bigint.

That is because the parameter is the type string, and the IDs in the table are bigint.

I try to create a list of long and add some IDs, but I get other error.

How can I use a list o IDs as parameter in a query?

I would like to use parameters, if that is possible.

Thanks.

Answer 1

There are a few problems with your code.

First off, to fix your data type error, you'd have to convert strIDs to integers before doing anything else. This should work

var ids = strIDs.Select(s => long.Parse(s));

Now, since you're using entity framework already, why not use Linq instead of creating a SQL query?

var results =
    from r in myContext.MyTable
    where ids.Contains(r.IDData)
    select r;

Or in fluent syntax

var results = myContext.MyTable.Where(r => strIDs.Contains(r.IDData));

But if you really want to use SQL, the IN operator does not support parameters like that. You'd have to write it like this:

strSQLQuery = "select * from MyTable where IDData IN(@ID1, @ID2, @ID3, ...)";

So to do this without too much effort, you can generate your query from ids like this:

strSQLQuery = "select * from MyTable where IDData IN(" + String.Join(", ", ids.Select((s, i) => "@ID" + i)) + ")";
foreach(var parameter in ids.Select((s, i) => new SqlParameter("@ID" + i, s)))
{
    lstParametros.Add(parameter);
}