如何在"in"中使用实体框架中的参数.条款? [英] How to use parameters in Entity Framework in a "in" clause?

问题描述

我正在使用Entity Framework 4.0，并且要使用以下查询:

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

但是我得到一个例外，说不可能将nvarchar转换为bigint.

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

这是因为参数是类型字符串，并且表中的ID是bigint.

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

我尝试创建一个long列表并添加一些ID，但是出现其他错误.

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

如何在查询中使用列表或ID作为参数?

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

如果可能的话，我想使用参数.

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

谢谢.

推荐答案

您的代码存在一些问题.

There are a few problems with your code.

首先，要解决数据类型错误，必须先将strIDs转换为整数，然后再执行其他操作.这应该工作

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

现在，由于您已经在使用实体框架，为什么不使用Linq而不是创建SQL查询呢?

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;

或者使用流利的语法

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

但是，如果您真的想使用SQL，则IN运算符不支持这样的参数.您必须这样写:

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, ...)";

因此，无需花费太多精力即可执行此操作，您可以像这样从ids生成查询:

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

这篇关于如何在"in"中使用实体框架中的参数.条款?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！