将带有All的linq转换为SQL

Question

我有A、B和C三个表。

B与A的关系是一对多。

我想找到所有满足C是B的完整子集的A。

下面的LINQ查询（伪代码）表示我想要的结果。

class A
{
    public B[] Bs { get; set; }
}

class B
{
    public string Text { get; set; }
}

class C
{
    public string Text { get; set; }
}

private static IEnumerable<A> Search(C[] C, A[] A)
{
    return from a in A
            where C.All(c => a.Bs.Any(b => SomeCondition(b, c)))
            select a;
}

private static bool SomeCondition(B b, C c)
{
    //There we comparing B and C
    throw new NotImplementedException();
}

我有以下表结构

A ( a_ID INT PRIMARY KEY )
B ( b_ID INT PRIMARY KEY, a_ID INT, Text NVARCHAR(MAX) )
C ( c_ID INT PRIMARY KEY, Text NVARCHAR(MAX) )

我需要获取所有A，其中B匹配所有C。

例子：

A | a_ID |
  | 1    |
  | 2    |
  | 3    |

B | b_ID | a_ID | Text  |
  | 1    | 1    | Cat   |
  | 2    | 1    | Dog   |
  | 3    | 1    | Rabbit| 
  | 4    | 2    | Cat   | 
  | 5    | 2    | Cat   | -- B can contain duplicates
  | 6    | 2    | Rabbit|

C | c_ID | Text |
  | 1    | Cat  |
  | 2    | Dog  |

输出

| a_ID |
| 1    |

- hazzik

---

@tmack 这是伪代码。 - hazzik

这并不足以理解问题。如果它能编译，那么可能就可以了。 - Trey Mack

你能提供表的DDL和期望输出吗？ - peterm

你使用的是哪个 Linq 提供程序？Linq 2 Sql？EF？ - andri

@andri 我没有使用linq提供程序，这只是我需要在SQL中编写的伪代码。 - hazzik

Answer 1

更新3：计算唯一的c_id数量

SELECT b.a_id
  FROM b
  JOIN c ON b.Text = c.Text
GROUP BY b.a_id
HAVING COUNT(distinct c.c_id) = (SELECT COUNT(*) FROM C)

输出：

| A_ID |
--------
|    1 |

这是一个sqlfiddle示例。

- peterm

---

它将选择C中的任何B，而不是全部，来匹配A。 - hazzik

谢谢@peterm，有没有可能避免使用count(*)？ - hazzik

这里实际上不需要与A连接。 - hazzik

公平地说，与A的JOIN是多余的。已更新答案。在我的查询版本中，你不能避免使用COUNT。但它只执行一次且速度很快。顺便提一下，如果C.Text可以为NULL，那么你需要使用SELECT COUNT(Text) FROM C。 - peterm

通过COUNT(distinct c.c_id)进行修复 - hazzik

好的，这很有道理。但是如果它是一个主键，子查询中的COUNT(c.c_id)就没有意义。由于我们通过“Text”进行连接，我认为你应该使用COUNT(*)或COUNT(Text)。在C中有一个具有c_id中的NULL的条目是非常不可能的。 - peterm

Answer 2

好的，我想出了如何做到。

假设 x=>x.All(e => predicate(e)) 可以转换为 x=>!x.Any(e => !predicate(e))

我们可以将原始表达式转换为以下表达式

from a in A
where !C.Any(c => !a.Bs.Any(b => SomeCondition(b, c)))
select a;

任何人都可以使用“exists”关键字将其转换为SQL

我假设“SomeCondition”只是相等的

首先将“!a.Bs.Any(b => SomeCondition(b, c))”转换为SQL

NOT EXISTS (
    SELECT 1 FROM B b
        WHERE a.a_id = b.a_id -- a will be added later
          AND c.Text = b.Text -- c will be added later
)

接下来将转换 !C.Any(c => !a.Bs.Any(b => SomeCondition(b, c)))

NOT EXISTS (
    SELECT 1 FROM C c
    WHERE NOT EXISTS (
        SELECT 1 FROM B b
        WHERE a.a_id = b.a_id -- a will be added later
          AND c.Text = b.Text
    )
)

最后是整个表达式

SELECT * FROM A a
WHERE NOT EXISTS (
    SELECT 1 FROM C c
    WHERE NOT EXISTS (
        SELECT 1 FROM B b
        WHERE a.a_id = b.a_id
          AND c.Text = b.Text
    )
)

现在我们可以进行一些优化

- hazzik