CODEWITHSUNDEEP
c#sqllinq
hal9000
hal9000

Reputation: 852

List<int> to a sql "in" query

I need to take a list and Linq it such that I come out with something suitable for a sql "in". This is what I am using now. Is there a cleaner Linq way to get this "(333,333,54445,56667)"

List<int> vendorIds;
string snipet = "";
for (int i = 0; i < vendorIds.Count; i++)
{
    snipet += (i > 0) ? "," + vendorIds[i].ToString() : vendorIds[i].ToString();
}

Just wondering.

Upvotes: 5

Views: 2603

Answers (2)

Chris Halcrow
Chris Halcrow

Reputation: 31950

You can pass this using a parameterised query with something like the following:

List<int> vendorIds = new List<int> {1,2,3};

string vendors = string.Join(",", vendorIds);

string sql = @"SELECT * FROM Vendors WHERE VendorId IN (SELECT convert(int, value) FROM string_split(@VendorIds, ','))";

SqlParameter[] parameters = {
    new SqlParameter { ParameterName = "@VendorIds", SqlDbType = SqlDbType.VarChar, Value = vendors},
};

Upvotes: -1

usr
usr

Reputation: 171178

In .NET <= 3.5

string.Join(",", vendorIds.Select(x => x.ToString()).ToArray())

In .NET >= 4

string.Join(",", vendorIds)

I prefer to write myself a method for that like:

string Concat(this IEnumerable<string> items) { ... }

Upvotes: 8

Related Questions