Transform a List of objects to a string of unique values for use in an SQL query

I have a List of Results. My goal is to extract the Result property Method from all elements of List, make extracted elements unique and prepare a string for SQL statements like WHERE method IN ('A','B','C').

Here is the Result class and a List example. The solution is in the ListTransformation() method.

Result.cs

public class Result
{
    public int Id { get; set; }
    public string Method { get; set; }
}

Program.cs

A method value can be one method ("A") or more methods separated by a colon ("A:B:C").

static void ListTransformationTest()
{
    const string separator = ":";

    List results = new List
                { new Result { Id = 1, Method = null },
                  new Result { Id = 1, Method = "" },
                  new Result { Id = 2, Method = "A:B" },
                  new Result { Id = 3, Method = "B:C" },
                  new Result { Id = 4, Method = "A:C:B" }
                };

    string methods = string.Join(separator,
                                 results.Where(x => x.Method?.Length > 0)
                                        .Select(r => r.Method).ToArray());
    var uniqueMethods = methods.Split(separator[0]).Distinct();
    string uniqueMethodsSqlFormat =
            string.Join(",", uniqueMethods.Select(m => string.Format($"'{m}'")).ToArray());
}

I guess there are a lot of operations. Do you have any suggestions on improving the code or any alternative code?

Creating query (alternative)

Creating SQL like that is vulnerable to SQL Injection. You should use parameters instead.

Here's an example:

using (var cmd = new SqlCommand())
{
    var methods = results
        .SelectMany(x => x.Method?.Split(
            new[] { ':' }, 
            StringSplitOptions.RemoveEmptyEntries) ?? new string[0]
        ).Distinct();

    var methodParameterNames = new List();
    foreach (var method in methods)
    {
        methodParameterNames.Add($"@Method{methodParameterNames.Count + 1}");
        cmd.Parameters.Add($"@Method{methodParameterNames.Count}", SqlDbType.NVarChar).Value = result;
    }
    var methodParameterString = string.Join(", ", methodParameterNames);

    // build the rest of the query
}

Extracting methods

To extract all methods use the SelectMany extension with the coalescing operator ?? so that you don't have to check for null later. You use an empty collection. The loop simply won't execute. Finally you get unique methods with Distinct.

Creating SQLParameters

In order to create a query for each result you create a SqlCommand. You add each method to a list with parameter names and at the same time add the parameters and the values to the command. The parameters have counters. The first counter needs to be +1 because it is just going to be added and it has to match the counter in the parameter line. At the end you build a string with parameter names that you can use to build the final query.

Example methodParameterString:

@Method1, @Method2, @Method3

The query to create:

var sql = $".. WHERE [Method] IN ({methodParameterString}) ..";

Review

string.Join(",", [...].ToArray());

There's no need to .ToArray() the collection for string.Join because it expects an IEnumerable. Calling ToArray you loop over the collection twice.

const string separator = ":";
separator[0]

You can make the separator a char and drop the [0] which is very confusing.

.Where(x => x.Method?.Length > 0)

It's easier to do:

.Where(x => !string.IsNullOrEmpty(x.Method))

StackExchange Code Review Q#150155, answer score: 3