Linq匹配可变数量的AND条件

我有一个数据库，其中包含一组规则，应该查询匹配的规则。
简化的表格包含5列，其中4列包含模式字符串（我们称之为And1..And4），1列用于操作（ToDo）：

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

现在我有了一个最多4个条件的输入模式，这些条件应该是AND条件。
预期结果应为s.th.，如：

(A) -> Todo2
(A,C) -> Todo3, Todo2    // Both rules match the input
(A,B,C) -> Todo3, Todo2
(B,C) -> Todo4 //order of the input should be irrelevant    
(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个条件的规则，SelectItems的实现如下：

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个条件元素的所有可能的变体。它可以归结为构建所有可能的变体。我在CodeProject上找到了一篇很棒的文章，它解释了变体、排列和组合之间的区别（Variations, Combinations, Permutations）
{A B C}的变化选择2，预期结果：

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

在找到问题的关键点之后，如何摆脱丑陋的代码这个问题就很容易回答了。通过方法创建变体，然后将它们放入SelectItems方法是一件很容易的事情。

下面是一个函数，它可以通过选择n个元素来构建k个元素的变量：

static void GenerateVariationsNoRepetitions(int[] kArray, int[] nArray, int index, List<List<int>> result)
{
    if (index >= kArray.Length)
    {
        result.Add(kArray.ToList());
        return;
    }
    for (int i = index; i < nArray.Length; i++)
    {
        kArray[index] = nArray[i];
        Swap(ref nArray[i], ref nArray[index]);
        GenerateVariationsNoRepetitions(kArray, nArray, index + 1, result);
        Swap(ref nArray[i], ref nArray[index]);
    }
}
private static void Swap<T>(ref T v1, ref T v2)
{
    T old = v1;
    v1 = v2;
    v2 = old;
}

记住这一点，GetCombinationRules方法可以重写得更小更短：

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

        for (int i=0; i < 3; i++)
        {
            int[] kArr = new int[i+1];
            int[] nArr = Enumerable.Range(0, andConditions.Count).ToArray();
            var combinations = new List<List<int>>();

            GenerateVariationsNoRepetitions(kArr, nArr, 0, combinations);
            foreach (var combi in combinations)
            {
                var list = new List<string>();
                foreach (int c in combi)
                {
                    list.Add(andConditions[c]);
                }
                result.AddRange(SelectItems(query, list));
            }
        }

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

我敢肯定，这不是最短和最优雅的实现，但它具有良好的可读性和易于扩展。
顺便说一下，即使在第一个实现中，我也做了一个小的重新安排（包括数据格式的小的重新定义），以提高性能。您还记得，我需要4个元素的变体，但是实现包含一个主元素和3个元素的变体。这极大地提高了性能，因为对主元素的第一个查询（必须始终是类型A，将初始结果从大约10k个元素减少到10个以下，同时也减少了其余变量的数量。这样，性能大致可以接受。

Linq匹配可变数量的AND条件

1条答案

相关问题

热门标签

最新问答