C# ExecuteQuery: How to use IN operator?

Question

I searched this problem but i think anyone has faced it. I am trying to use IN operation with 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();

The problem is string.Join(",", companyIdList) returns '61,70'. Then it tries to convert it to integer. Why? What do I suppose to do?

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)

Any suggesstions? Or Can you show me a IN operator usage with ExecuteQuery?

I may be missing something obvious, but what's your `db` object in this case, and what `ExecuteQuery`-method do you use? It's not a part of `SqlCommand` or `DbContext`. — smoksnes, Aug 15 '16 at 06:55
In common with most languages, SQL interprets a *single* string that happens to *contain* digits and commas differently than *multiple* parameters. — Damien_The_Unbeliever, Aug 15 '16 at 06:55
@smoksnes db is System.Data.Linq.DataContext. I might be wrong using the ExecuteQuery function. — akdora, Aug 15 '16 at 09:42

score 2 · Answer 1 · answered Aug 15 '16 at 09:51

The thing here is that they are parameterized, which usually is a good thing.

I'm not saying it's the best of solutions, but you should be able to do something like this:

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

What it actually does is that it adds the companyIds to the sql string instead of letting ExecuteQuery parameterize it. Just beware about sql injections and make sure that you only got int's in your companyId array.

The sql-variable will be:

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

i know it is not the best solution, but it worked. thank you — akdora, Aug 15 '16 at 10:09
Yes don't let sql provider handle List as a parameter, do it by yourself. Otherwise it format to in strange form: `-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [1447036022260393,1447297322263966,1447017522260133,1447022022260214,1...'` — jasmin, Jan 21 '21 at 15:44

score 0 · Answer 2 · answered Aug 15 '16 at 07:25

The statement you are building leads to :

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

You need to interprete each value separately so your output is like :

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

this line

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

In combination with leading and ending ' should do the trick.

The better way would be dynamic created SqlParameters though

it was a good try, but it did not work. sorry. Error: Conversion failed when converting the nvarchar value '61','70' to data type int. — akdora, Aug 15 '16 at 09:41

C# ExecuteQuery: How to use IN operator?

2 Answers2