如何优化SQL WHERE子句动态替换逻辑？适配多AND场景

Great question! Your current approach works for 2 placeholders but doesn't scale, and those nested loops get messy fast. Let's fix this with a more flexible, scalable solution that handles any number of [CustomValueX] placeholders and improves efficiency.

Key Improvements & Approach #

• Lookup Speed: Convert your Tag list into a dictionary for O(1) lookups instead of relying on index-based access.
• Dynamic Placeholder Handling: Extract all placeholders from the WHERE clause automatically, so you don't have to hardcode a customValue list.
• Cartesian Product Generation: Use a dynamic way to generate all combinations of tag values (instead of rigid nested loops) — this works for any number of placeholders.
• Efficient String Replacement: Minimize redundant string operations by reusing the base clause and only replacing placeholders once per combination.

Optimized Code #

First, let's adjust the setup and core logic:

public class Tag { public string Name; public List<string> Values; }

// Sample data (add as many tags as needed!)
var tagList = new List<Tag>
{
    new Tag { Name = "CustomValue1", Values = new List<string> { "1", "2", "3", "4", "5", "6", "7" } },
    new Tag { Name = "CustomValue2", Values = new List<string> { "8", "9", "10" } },
    new Tag { Name = "CustomValue3", Values = new List<string> { "A", "B" } }
};

// Base WHERE clause (fixed quote syntax for valid SQL)
var baseClause = "Column1 = '[CustomValue1]' AND Column2 = '[CustomValue2]' AND Column3 = '[CustomValue3]'";

// Step 1: Convert Tag list to a dictionary for fast lookups
var tagDictionary = tagList.ToDictionary(t => t.Name, t => t.Values);

// Step 2: Extract all placeholders from the base clause using regex
var placeholderRegex = new Regex(@"\[(\w+)\]");
var placeholders = placeholderRegex.Matches(baseClause)
    .Cast<Match>()
    .Select(m => m.Groups[1].Value)
    .Distinct()
    .ToList();

// Step 3: Generate Cartesian product of all tag value combinations
// Start with an empty combination as the base case
var combinations = new List<Dictionary<string, string>> { new Dictionary<string, string>() };
foreach (var placeholder in placeholders)
{
    if (!tagDictionary.TryGetValue(placeholder, out var values))
        throw new InvalidOperationException($"No tag found for placeholder: {placeholder}");
    
    // Combine existing combinations with each value of the current placeholder
    combinations = combinations
        .SelectMany(comb => values.Select(val => 
        {
            var newComb = new Dictionary<string, string>(comb);
            newComb[placeholder] = val;
            return newComb;
        }))
        .ToList();
}

// Step 4: Replace placeholders and execute queries
var data = new Dictionary<string, List<string>>();
foreach (var combination in combinations)
{
    var sb = new StringBuilder(baseClause);
    foreach (var (placeholder, value) in combination)
    {
        sb.Replace($"[{placeholder}]", value);
    }
    var finalClause = sb.ToString();
    
    // Merge results using your existing Merge extension method
    data = data.Merge(QueryStatement(finalClause));
}

Explanation #

1. Tag Dictionary: Converting tagList to a dictionary lets us quickly find values for any placeholder without looping through the list every time, cutting down on unnecessary overhead.
2. Placeholder Extraction: The regex automatically detects all [CustomValueX] patterns in your clause — no more manually updating a separate list when you add new placeholders.
3. Dynamic Cartesian Product: The loop builds every possible combination of tag values. For each placeholder, it takes existing combinations and pairs them with every value of the current tag, creating new valid combinations. This scales seamlessly to 2, 3, or 10+ placeholders.
4. StringBuilder Replacement: Using StringBuilder instead of repeated string.Replace reduces immutable string copies, which is much more efficient for large clauses or high volumes of combinations.

内容的提问来源于stack exchange，提问作者Vince Orio