CODEWITHSUNDEEP
c#executequery
akdora
akdora

Reputation: 903

C# ExecuteQuery: How to use IN operator?

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?

Upvotes: 0

Views: 552

Answers (2)

smoksnes
smoksnes

Reputation: 10851

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)

Upvotes: 2

Mohnkuchenzentrale
Mohnkuchenzentrale

Reputation: 5885

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

Upvotes: 0

Related Questions