c# - 如何过滤相同类型的嵌套实体？

Question

以下是我的实际情况的简化版。假设我有这个 Person实体:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    // etc.

    // Some navigation properties
    public virtual ICollection<Thing> Things { get; set; }
    // etc.
}

我编写了一个扩展方法来根据一个或多个属性进行过滤:

public static IQueryable<Person> Filter(this IQueryable<Person> query,
                                        string name = null, int? thingId = null, 
                                        Foo etc = null)
{
    if (!string.IsNullOrEmpty(name))
        query = query.Where(p => p.Name.ToLower().Contains(name.ToLower()));

    if (thingId.HasValue)
        query = query.Where(p => p.Things.Count > 0 && 
                                 p.Things.Any(t => t.Id == thingId.Value));
    // etc.

    return query;
}

..我可以这样使用:

var query = context.People.Filter(name, thingId);
var filteredPeople = query.Include(p => p.Things).Include(__).OrderBy(__).ToList();

我想做 Person嵌套实体(即，每个人都有一个人的集合)。所以，我添加了以下属性:

public virtual ICollection<Person> Children { get; set; }
[ForeignKey("Parent")]
public int? ParentId { get; set; }
public virtual Person Parent { get; set; }

现在我正在努力实现过滤逻辑。我需要的是:

一位家长 Person如果它与过滤器匹配或其后代之一匹配，则将被包括在内。

一个 child Person仅在满足上述条件时才包括在内。

对于第二个问题，我可能会尝试 this answer中的解决方案但我需要先解决第一个问题。我试图创建一个递归表达式，如下所示:

private static IQueryable<Person> FilterByName(this IQueryable<Person> query, string name)
{
    if (string.IsNullOrEmpty(name)) return query;

    Expression<Func<Person, bool>> selector = (p) => 
        p.Name.ToLower().Contains(name.ToLower()) 
        || p.Children.AsQueryable().FilterByName(name).Any();

    return query.Where(selector);
}

..但我得到一个异常(exception)，说它“无法翻译成商店表达”。
我能想到的唯一其他解决方案是递归迭代子树并尝试手动构建列表，这是低效的，因为它将需要太多查询。
如何有效过滤Person的集合和他们的后代？

Answer 1

我们需要创建一个查询来实现以下目标:

如果父 Person 与过滤器匹配或其后代之一匹配，则将包括在内。

仅当满足上述标准时才包括子人。

如果我们颠倒我们的思维方式，并意识到没有父实体或子实体，只有人这一事实，我们可以重写我们的标准，如下所示:

如果一个人与过滤器匹配，我们希望包括该人及其所有祖先。

这大大简化了我们需要执行的查询并给出相同的结果。
现在，正如一些评论所指出的那样，就层次查询而言， Entity Framework 并没有内置很多内容。这并不意味着我们不能使用 EF，但我们确实需要编写和执行原始 SQL 查询。
如果要将实体保存到数据库，EF 仍会处理对象映射和更改跟踪。
这没有被 Linq 进一步扩展的能力，例如 .Where()或 .Include()因为它是一个不可堆肥的查询。

public static IEnumerable<Person> FilterPeople(this DbSet<Person> people, string name)
{
    return people.FromSqlRaw(
        "WITH child AS (" +
        "    SELECT * FROM People" +
        "    WHERE Name LIKE {0}" +
        "    UNION ALL" +
        "    SELECT parent.* FROM People parent" +
        "    INNER JOIN child ON parent.Id = child.ParentId" +
        ") SELECT * FROM child",
        $"%{name}%")
        .AsEnumerable();
}

这转化为参数化查询:

exec sp_executesql N'WITH child AS (SELECT * FROM People WHERE Name LIKE @p0 UNION ALL SELECT parent.* FROM People parent INNER JOIN child ON parent.Id = child.ParentId) SELECT * FROM child',N'@p0 nvarchar(4000)',
@p0=N'%Dick%'