c# ExecuteQuery:如何使用IN操作符

本文关键字：IN 操作符 何使用 ExecuteQuery | 更新日期: 2023-09-27 18:02:20

我搜索了这个问题，但我认为任何人都面临过它。我正在尝试使用IN操作与ExecuteQuery.

IEnumerable<TAssets> results = db.ExecuteQuery<TAssets>
("SELECT * FROM TAssets " +
 " WHERE AssetId not in (select MatchedAssetId from TMatches where LabelId = {0})" +
 "       and CompanyId in ({1}) ",
labelid,
string.Join(",", companyIdList)
);
return results.ToList();

问题是字符串。Join("，"， companyIdList)返回'61,70'。然后尝试将其转换为整数。为什么?我该怎么办呢?

ERROR:Conversion failed when converting the nvarchar value '61,70' to data type int.
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '61,70' to data type int.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
  at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
  at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
  at System.Data.SqlClient.SqlDataReader.Read()
  at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.Read()
  at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
  at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
  at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

suggesstions吗?或者你能告诉我一个IN操作符使用ExecuteQuery吗?

c# ExecuteQuery:如何使用IN操作符

这里的问题是它们是参数化的，这通常是一件好事。

我不是说这是最好的解决方案，但你应该能够做这样的事情:

// Note that the first {} is escaped.
var sql = string.Format(
                    "SELECT * FROM TAssets WHERE AssetId not in (select MatchedAssetId from TMatches where LabelId = {{0}}) and CompanyId in ({0})",
                    string.Join(",", companyIdList));
IEnumerable<TAssets> results = db.ExecuteQuery<TAssets>(sql, labelid);
return results.ToList();

它实际做的是将companyid添加到sql字符串中，而不是让ExecuteQuery参数化它。只是要小心sql注入，并确保在companyId数组中只得到int。

sql -变量将是:

SELECT * FROM TAssets WHERE AssetId not in (select MatchedAssetId from TMatches where LabelId = {0}) and CompanyId in (61,70)

您正在构建的语句导致:

"SELECT * FROM TAssets WHERE AssetId not in (select MatchedAssetId from TMatches where LabelId = 1) and CompanyId in ('61,70')"

您需要分别解释每个值，因此您的输出如下:

"SELECT * FROM TAssets WHERE AssetId not in (select MatchedAssetId from TMatches where LabelId = 1) and CompanyId in ('61','70')"
这条线

string.Join("','", companyIdList)

结合开头和结尾'应该可以达到目的。

更好的方法是通过

动态创建SqlParameters。
相关文章：