我有一个数据库,其中包含一组规则,应该查询匹配的规则。
简化的表格包含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方法是一件很容易的事情。
1条答案
按热度按时间taor4pac1#
下面是一个函数,它可以通过选择n个元素来构建k个元素的变量:
记住这一点,GetCombinationRules方法可以重写得更小更短:
我敢肯定,这不是最短和最优雅的实现,但它具有良好的可读性和易于扩展。
顺便说一下,即使在第一个实现中,我也做了一个小的重新安排(包括数据格式的小的重新定义),以提高性能。您还记得,我需要4个元素的变体,但是实现包含一个主元素和3个元素的变体。这极大地提高了性能,因为对主元素的第一个查询(必须始终是类型A,将初始结果从大约10k个元素减少到10个以下,同时也减少了其余变量的数量。这样,性能大致可以接受。