Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
473 views
in Technique[技术] by (71.8m points)

c# - How to use parameters in Entity Framework in a "in" clause?

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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);
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...