“where x in”-SQL query with LINQ

Writing SQL queries for an “in” selection can be tedious for lists in .NET… but it does not have to be!

We switch off: List<CarType> largeTypeList

into: SELECT * FROM CARS WHERE type in ('Audi', 'Ford', 'BMW', 'Nissan', ...)

Thanks to LINQ, you can easily turn a list into an “in” query.

example

Let’s imagine that we have a list of model types for cars. In this list, we fill countless model types, which we would then select from the database. Without LINQ the whole thing would look something like this:

List<CarType> largeTypeList = new List<CarType>() { /* fill with many objects */ };
string queryString = "SELECT * FROM CARS WHERE type in (";
for(var CAR_TYPE in largeTypeList)
{
  queryString += "'" + CAR_TYPE.Name + "',"; // add name in string formatted and append comma
}
if(largeTypeList.Count > 0)
  queryString.Remove(queryString.Length -1, 1); // remove last comma if list is finished

queryString += ")";

It would look something like this with a loop and a string. Sure, you could make it a bit more performant with a StringBuilder if the list is REALLY big, but mostly it doesn’t matter.

more beautiful and tidier is the whole thing with LINQ:

List<CarType> largeTypeList = new List<CarType>() 
{ /* fill with many objects */ };

string whereQuery = string.Join(",", largeTypeList
  .Select(T => $"'{T.Name}"'").ToArray());

string queryString = $"SELECT * FROM CARS WHERE type in({whereQuery})";

the explanation

What happens here now? Very simple: first we create a simple List<string> by extracting the names of the respective types with the LINQ function Select(). At the same time we put an apostrophe before and after the name. So the result would be the same as if we would write the following:

List<string> modelNameList = new List<string>()
{
  "'Audi'", "'Ford'", "'BMW'", "'Nissan'"
};

With the string.Join() method we can now easily “join” this list. The first parameter specifies the separator (in our case a single comma), the second expects an array. Since we still have a List<> here, we have to say .ToArray() at the end, so that the whole thing fits as a parameter.

With this method, we also save ourselves the trouble of removing the last comma, since string.Join() always places the separator between the entries.