如何用Linq匹配数量可变的AND条件？

我有一个存储规则的数据库，需要查询其中匹配的规则。简化后的表包含5列：4个模式字符串列（命名为And1至And4）和1个动作列（ToDo），数据示例如下：

A  B  C  D  Todo1
A  -  -  -  Todo2
A  C  -  -  Todo3
C  B  -  -  Todo4

现在我有一组最多4个AND条件的输入模式，预期匹配结果示例如下：

(A) -> Todo2
(A,C) -> Todo3, Todo2    // 两条规则均匹配输入
(A,B,C) -> Todo3, Todo2
(B,C) -> Todo4 //输入顺序不影响匹配    
(A,B,C,D) -> Todo1, Todo2 

2022年12月29日更新：原实现代码 #

以下是可正常运行的原实现示例代码：

public List<CombinationRule> GetCombinationRules(string firstElement, List<string> andConditions)
{
    using (var context = _dbContextProvider.GetDbContext())
    {
        var result = new List<CombinationRule>();
        var query = context.CombinationTable
            .Where(x => Match(firstElement, x.ColumnA))

        result.AddRange(SelectItems(query, new List<string> { andConditions[0] }));
        if (andConditions.Count > 1)
        {
            result.AddRange(SelectItems(query, new List<string> { andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[0] }));
        }
        if (andConditions.Count > 2)
        {
            result.AddRange(SelectItems(query, new List<string> { andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[1], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[2], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[0], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[2], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[0], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[1], andConditions[0] }));
        }
        if (andConditions.Count > 3)
        {
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[1], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[3], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[2], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[0], andConditions[3], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[0], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[3], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[2], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[1], andConditions[3], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[0], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[3], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[1], andConditions[3] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[2], andConditions[3], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[0], andConditions[1] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[1], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[0], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[2], andConditions[0] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[1], andConditions[2] }));
            result.AddRange(SelectItems(query, new List<string> { andConditions[3], andConditions[2], andConditions[1] }));
        }

        return result.Distinct().ToList();
    }
}

辅助函数实现 #

该方法依赖两个辅助函数：Match和SelectItems：

• Match：基于特定业务逻辑的字符串匹配函数
• SelectItems：用于筛选符合1、2或3个条件的规则，实现如下：

private List<CombinationRule> SelectItems(IQueryable<CombinationTable> query,
                                                         List<string> andOrderNumbers)
{
    switch(andOrderNumbers.Count)
    {
        case 1:
            query = query.Where(x => string.IsNullOrEmpty(x.And2OrderNumber) &&
                                 string.IsNullOrEmpty(x.And3OrderNumber))
                     .Where(x => Match(andOrderNumbers[0], x.And1OrderNumber));
            break;
        case 2:
            query = query.Where(x => string.IsNullOrEmpty(x.And3OrderNumber))
                                         .Where(x => Match(andOrderNumbers[0], x.And1OrderNumber) &&
                                                     Match(andOrderNumbers[1], x.And2OrderNumber));
            break;
        case 3:
            query = query.Where(x => Match(andOrderNumbers[0], x.And1OrderNumber) &&
                                   Match(andOrderNumbers[1], x.And2OrderNumber) &&
                                   Match(andOrderNumbers[2], x.And3OrderNumber));
            break;
        default:
            throw new NotImplementedException();
    }
    var result = query.Select(x => new CombinationRule(x.PrimaryOrderNumber,                                                
                                    new List<string> { x.And1OrderNumber, x.And2OrderNumber, x.And3OrderNumber },
                                    x.ToDo))
            .Distinct()
            .ToList();

    return result;
}

问题核心与优化思路 #

原实现通过手动枚举4个条件元素的所有可能变体完成查询，代码冗余且扩展性差。问题的核心是生成所有可能的变体：即从输入的条件集合中，选取k个元素（k从1到输入条件总数）的所有排列组合（元素顺序不同视为不同变体）。

比如从{A B C}中选2个的变体结果为：

{A B}, {A C}, {B A}, {B C}, {C A}, {C B}

可以通过编写通用的变体生成方法，替代原有的手动枚举逻辑，将生成的变体传入SelectItems函数完成查询，以此简化代码、提升扩展性。

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