通过复杂的属性组合找到匹配项

Question

在我的数据库中，我有项目，它们反映了用户正在填写的文档中的属性。文档中给出的每个值，例如您为某个字段选择某个选项或选中一个复选框，都将成为“我的表”中的一个项/属性。

这些属性可以是:吸烟者，不吸烟者，区域(欧洲，美国，.)，头发颜色

在表中，这大致如下所示：

Document
ID | Name
1  | doc-1
2  | doc-2
3  | doc-3

Attribute
ID | Name
1  | Smoker
2  | Non-Smoker
3  | Region-Europe
4  | Region-USA
5  | Hair-Brown
6  | Hair-Blond

Item
ID | Document | Attribute
1  | 1        | 1
2  | 1        | 4
3  | 2        | 2
4  | 2        | 3
5  | 2        | 5
6  | 3        | 2
7  | 3        | 6

为了提供搜索的可能性，应该允许用户构建通用查询。例如，我希望找到具有以下属性的文档：

(Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond)

(将导致第1号文件被找到)

我如何以最有效的方式执行这样的查询，并可能使用EF-core和linq- to -sql将其推到SQL中？我如何才能以最有效的方式在plan SQL中查询这一点呢？

我可以很容易地在内存中做到这一点，但是由于我的数据库包含100k+项，这可能很快就会变慢。

谢谢你在这方面的帮助！

更新：关于SO的相关问题

• Database Design for Tagging

Answer 1

更多的研究向我展示了我已经预料到的:可以使用使用SQL语句的解决方案，并且实际上正在很好地将查询工作分派给服务器，但在大量标记上可能没有那么有效。

幸运的是，用户不会定期执行非常复杂的查询，并且会接受一些复杂查询的等待时间，所以在我的情况下，我可以忽略这一点。

将这些发言的来源联系起来：

• https://stackoverflow.com/a/48480/8504288
• https://dba.stackexchange.com/a/176816

现在粗略描述最后的解决方案，下面是一些代码：

通过使用IN语句，在子查询中，我可以过滤所有文档，这些文档都应用了某个属性。通过使用AND/OR组合这些IN语句，我可以构建我想要的表达式。

SELECT i.Document
FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID
WHERE
    i.Document IN (
       SELECT ii.Document 
       FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID
       WHERE ai.Name = "Smoker"
    )
    AND
    i.Document IN (
       SELECT ii.Document 
       FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID
       WHERE ai.Name = "Region-USA"
    )
    OR
    i.Document IN (
       SELECT ii.Document 
       FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID
       WHERE ai.Name = "Non-Smoker"
    )
    AND
    i.Document IN (
       SELECT ii.Document 
       FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID
       WHERE ai.Name = "Region-Europe"
    )
    AND
    i.Document IN (
       SELECT ii.Document 
       FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID
       WHERE ai.Name = "Hair-Blond"
    )

性能改进

为了限制子查询中所需的联接量，可以首先选择所需属性的ID。

SELECT ID, Name FROM Attribute WHERE Name in ('Smoker', 'Non-Smoker', ...)

使用这些ID，子查询看起来要容易得多，因为我们可以跳过联接：

SELECT i.Document
FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID
WHERE
    i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 1) -- Smoker
    AND
    i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 4) -- Region-USA
    OR
    ...

更新

两种方法的测量时间()

我确实在SQL Server上执行了与上面提到的查询类似的查询：(1和2) OR (3、4和4)，具有合理大小的文档(130)、项(4122)和属性(~400)。在我的机器上可以测量以下时间：

• 第一种方法，加入IN：~12秒的子查询
• 第二种方法，先查找属性的ID：~3.5秒

Answer 2

下面是一个LINQ扩展类，用于帮助构建查询。我把解析表达式和构建正确的查询作为读者的练习:)。

首先，以下是我们将建立的基础：

public class DocItemJoin {
    public Documents d { get; set; }
    public IEnumerable<int> ig { get; set; }
}

var DocItems = Document.GroupJoin(Item, d => d.ID, i => i.Document, (d, ig) => new DocItemJoin { d = d, ig = ig.Select(i => i.Attribute) });

// (Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond)    
var ans = DocItems.Where(dig => (dig.ig.Contains(1) && dig.ig.Contains(4)) || (dig.ig.Contains(2) && dig.ig.Contains(3) && dig.ig.Contains(6)))
                  .Select(dig => dig.d);

使用DocItems作为基础，我们可以使用Contains查询每个属性。

使用扩展库，我们可以动态地构建相同的查询：

var whereLeft = 1.HasAttrib().qAnd(4.HasAttrib());
var whereRight = 2.HasAttrib().qAnd(3.HasAttrib()).qAnd(6.HasAttrib());
var whereBody = whereLeft.qOr(whereRight);
var ans = DocItems.Query(whereBody);

最后，下面是构建Expression树的扩展类：

public static class QueryBuilder {
    private static MethodInfo containsMethod = typeof(Enumerable).GetMethods().Single(mi => mi.Name == "Contains" && mi.GetParameters().Length == 2).MakeGenericMethod(typeof(int));

    public static MethodCallExpression qContains(this Expression p, int attrib) => Expression.Call(containsMethod, p, Expression.Constant(attrib));
    public static BinaryExpression qAnd(this Expression l, Expression r) => Expression.AndAlso(l, r);
    public static BinaryExpression qOr(this Expression l, Expression r) => Expression.OrElse(l, r);

    static ParameterExpression digParm = Expression.Parameter(typeof(DocItemJoin), "dig");
    static MemberExpression digParmig = Expression.Property(digParm, "ig");

    public static MethodCallExpression HasAttrib(this int attrib) => digParmig.qContains(attrib);

    static Expression<Func<DocItemJoin, Documents>> selectLambda = Expression.Lambda<Func<DocItemJoin, Documents>>(Expression.Property(digParm, "d"), digParm);

    public static IQueryable<Documents> Query(this IQueryable<DocItemJoin> src, Expression whereBody)
        => src.Where(Expression.Lambda<Func<DocItemJoin, bool>>(whereBody, digParm)).Select(selectLambda);
}