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
– smoksnes, Commented Aug 15, 2016 at 6: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
– Damien_The_Unbeliever, Commented Aug 15, 2016 at 6:55
@smoksnes db is System.Data.Linq.DataContext. I might be wrong using the ExecuteQuery function. — akdora
– akdora, Commented Aug 15, 2016 at 9:42

smoksnes · Accepted Answer · 2016-08-15 09:51:50Z

2

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)

answered Aug 15, 2016 at 9:51

smoksnes

10.9k5 gold badges60 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akdora Over a year ago

i know it is not the best solution, but it worked. thank you

jasmin Over a year ago

Yes don't let sql provider handle List<string> 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...'

Mohnkuchenzentrale · Accepted Answer · 2016-08-15 07:25:44Z

0

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

answered Aug 15, 2016 at 7:25

Mohnkuchenzentrale

5,9154 gold badges32 silver badges42 bronze badges

1 Comment

akdora Over a year ago

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.

Collectives™ on Stack Overflow

C# ExecuteQuery: How to use IN operator?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related