使用多个OR进行快速查询，并使用Entity Framework Core对联接的表进行过滤 [英] Express query with multiple OR and filter on joined table with Entity Framework Core

问题描述

这是我的模特

class Parent 
{ 
   int Id; 
   string Name; 
   List<Child> Childs; 
} // name is unique

class Child 
{ 
    int Id; 
    int ParentId; 
    string Name; 
    Parent Parent; 
} // couple (id, name) is unique

使用给定的夫妻名单(父母姓名，孩子的名字)，我想得到夫妻(父母子女)，如果具有给定名称的父级存在，但子级不存在.SQL查询如下所示:

With a given list of couples (parent name, child name) I'd like to get the couples (parent, child) where child can be null if the parent with the given name exists but not the child. The SQL query would look like this:

SELECT * 
FROM parents p
LEFT JOIN childs c ON c.parent_id = p.id
WHERE p.name = 'parent1' AND (c.name IS NULL OR c.name = 'child1')
   OR p.name = 'parent2' AND (c.name IS NULL OR c.name = 'child2')
   OR p.name = 'parent3' AND (c.name IS NULL OR c.name = 'child3')
   OR p.name = 'parent4' AND (c.name IS NULL OR c.name = 'child4');

我尝试使用 PredicateBuilder 来使用Entity Framework Core表示此查询.或与False方法

I've tried expressing this query with Entity Framework Core using PredicateBuilder for the Or and False methods

var predicate = PredicateBuilder.False<Parent>()
    .Or(p => p.Name == "parent1" && p.Childs.Any(c => c.Name == "child1"))
    .Or(p => p.Name == "parent2" && p.Childs.Any(c => c.Name == "child2"))
    .Or(p => p.Name == "parent3" && p.Childs.Any(c => c.Name == "child3"))
    .Or(p => p.Name == "parent4" && p.Childs.Any(c => c.Name == "child4"));

var p = await _db.Parents
    .Include(p => p.Childs)
    .Where(predicate)
    .ToArrayAsync();

这是我能得到的最接近的结果，但这没有得到预期的结果:

This is the closest I could get but this doesn't get the expected the result:

• 如果子代不存在，则父代不出现在结果集中
• Parent.Childs 包含父级的所有子级，而不只是所需的子级

• if the child doesn't exist the parent is not present in the result set
• Parent.Childs contains all children of the parent instead of only the wanted one

我的查询可以用Entity Framework Core表示吗?

Is my query expressible with Entity Framework Core?

推荐答案

根据您的评论，现在的要求是:给我所有按名称指定的父母，并且如果有的话，每个父母只有一个特定的孩子.也就是说:结果中将显示有 other 个孩子的父母，但没有孩子.

As per your comment, the requirement now is: give me all parents, specified by name, and only one specific child per parent, if present. That is: parents having other children will appear in the result, but without children.

这听起来很琐碎，但事实并非如此.需要注意的是，它需要两个过滤器，一个在父级上，一个在子级上，其中子级过滤器甚至是特定于父级的.SQL查询如下所示:

That sounds rather trivial, but it isn't. The gotcha is that it requires two filters, one on parents and one on children, in which the child filter is even parent-specific. A SQL query would look like this:

SELECT * 
FROM parents p1
LEFT JOIN 
(
    SELECT ch.*
    FROM children ch
    JOIN parents p2 ON ch.parentid = p2.id
    WHERE (p2.name = 'parent1' AND ch.name = 'child1')
       OR (p2.name = 'parent2' AND ch.name = 'child2')
       OR (p2.name = 'parent3' AND ch.name = 'child3')
       OR (p2.name = 'parent4' AND ch.name = 'child4') -- filter 2
) fc ON fc.parentid = p1.id
WHERE p1.name IN ('parent1','parent2','parent3','parent4') -- filter 1

对于EF LINQ查询，父谓词可以是简单的 Contains ，但是您希望使用谓词生成器来构建谓词.在这里，出于稍后的原因，我使用 LINQkit.core .

For an EF LINQ query the parent predicate can be a simple Contains, but you'd want to build the predicate using a predicate builder. Here, for reason following later, I use LINQkit.core.

为了能够从一个来源构建谓词，我使用了一个临时结构(但我想您已经有类似的东西了):

To be able to build the predicates from one source I use a temporary structure (but I guess you already have something similar):

var filters = new[]
{
    new { ParentName = "parent1", ChildName = "child1" },
    new { ParentName = "parent2", ChildName = "child2" },
    new { ParentName = "parent3", ChildName = "child3" },
    new { ParentName = "parent4", ChildName = "child5" },
};

并准备谓词:

using LinqKit;
...
var parentNames = filters.Select(f => f.ParentName).ToList();
var childPredicateStarter = PredicateBuilder.New<Child>();
foreach (var filter in filters)
{
    childPredicateStarter = childPredicateStarter
        .Or(c => c.Parent.Name == filter.ParentName && c.Name == filter.ChildName);
}

现在，理想情况下，LINQ查询应如下所示( db 是上下文)，以解决 Include 中缺少过滤的问题:

Now, ideally, the LINQ query would look like this (db is a context), working around the lack of filtering in Include:

var p = db.Parents
    .Where(p => parentNames.Contains(p.Name))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(childPredicateStarter)
    })
    .AsEnumerable()
    .Select(p => p.Parent);

但这不会运行，因为 p.Children 是 IEnumerable ，所以 childPredicateStarter 隐式转换为 Func 而不是必需的 Expression< Func>> .有关详细说明，请参见此处.

But that doesn't run because p.Children is IEnumerable, so childPredicateStarter implicitly converts to a Func instead of the required Expression<Func>>. See here for an in-depth explanation.

实际的工作版本是:

// Convert to expression:
Expression<Func<Child, bool>> childPredicate = childPredicateStarter;

var p = db.Parents.AsExpandable() // <-- LINQKit's AsExpandable()
    .Where(p => parentNames.Contains(p.Name))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => childPredicate.Invoke(c))
    })
    .AsEnumerable()
    .Select(p => p.Parent);

AsExpandable 调用将 Invoke 转换回适当的表达式树，EF可以将其转换为SQL.

The AsExpandable call converts the Invoke back into a proper expression tree that EF can translate into SQL.

这篇关于使用多个OR进行快速查询，并使用Entity Framework Core对联接的表进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！