EF Core 生成反向查询

Question

有4个表：

上传日期

Id 
Description

上传类型

Id 
Description

上传状态

Id 
Description

上传详情

Id
UploadDateId (FK)
UploadTypeId (FK)
UploadStatusId (FK)
OtherFields..

上传日期 (data)

1    Aug-2018
2    Sep-2018
3    Oct-2018
4    Nov-2018
5    Dec-2018
6    Jan-2019

上传类型 (data)

1    Partner
2    Retail
3    Customer

上传状态 (数据)

1    Uploaded
2    Processing
3    Successful

UplodeDetail (数据)

Id    UploadDateId     UploadTypeId    UploadStatusId    other fields
1     1                1               3                 ...
2     1                2               3                 ...
3     2                2               3                 ...
4     2                1               3                 ...
5     1                3               3                 ...
6     2                3               2                 ...
7     3                2               1                 ...
8     4                2               1                 ...
9     4                2               3                 ...

我想要做的是获取所有上传类型成功上传的月份。

查询语句：

var list = await _iContext.UploadDate.Where(e => e.UploadDetails.All(o => o.UploadStatusId == (byte)EnumType.UploadStats.Successful)).Distinct().ToListAsync();

所以，从 UploadDate 中我得到了所有 UploadDetails 条目都成功的地方。它应该给我 Aug-2018。但是它给出了 Dec-2018 和 Jan-2019。

我在 SQL Profiler 中检查过，它生成了以下查询...

SELECT DISTINCT [e].[Id], [e].[Description]
FROM [UploadDate] AS [e]
WHERE NOT EXISTS (
    SELECT 1
    FROM [UploadDetail] AS [o]
    WHERE ([e].[Id] = [o].[UploadDateId]) AND ([o].[UploadStatusId] <> CAST(3 AS tinyint)))

基本上是要过滤掉所有未成功的内容，这技术上与我所需生成的相反，类似于...

SELECT DISTINCT [e].[Id], [e].[Description]
FROM [UploadDate] AS [e]
WHERE EXISTS (
    SELECT 1
    FROM [UploadDetail] AS [o]
    WHERE ([e].[Id] = [o].[UploadDateId]) AND ([o].[UploadStatusId] = CAST(3 AS tinyint))).

另外，如果我运行上面的查询（就在上面，而不是 EF Core 生成的查询），我会得到预期结果 Aug-2018。

那么，为什么 EF Core 生成的查询与我打算编写的相反呢？或者我已经编写了完全错误的查询语句吗？

- Razort4x

---

EF Core生成的查询对于All运算符是正确的（您的等价于Any并将匹配[1,2,4]）。它给出了[1,5,6]，不是吗？问题在于包含了5,6吗？ - Ivan Stoev

Answer 1

两个查询都返回了错误的结果。

EF Core 生成的 SQL 查询返回了 [1, 5, 6]。

手写的 SQL 查询（相当于使用 Any 而不是 All，如果你这样做，EF Core 也会生成它）返回了 [1, 2, 4]。

所需的结果是 [1]。

首先，众所周知，

All(condition)

是等同于（相当于）

!Any(!condition)

第二个事实是（很容易看出来），当序列为空（没有元素）时，这两个表达式都返回true。这在技术上是正确的——所有（在这种情况下为零）元素都符合条件。或者说没有不符合条件的元素。

但在您的情况下不起作用，因为您实际上想要的是“获取存在上传并且所有上传类型都成功的月份”，这可以表示为：“获取存在上传并且所有上传类型都成功的月份”。

.Where(e => e.UploadDetails.Any()
    && e.UploadDetails.All(o => o.UploadStatusId == 3))

或者“存在成功上传并且不存在失败上传”，表示为：

.Where(e => e.UploadDetails.Any(o => o.UploadStatusId == 3)
    && !e.UploadDetails.Any(o => o.UploadStatusId != 3))

这两种条件都可以产生所需的行为。然而，它们将生成 2 个相关子查询以执行检查。

如果您想使用一个相关子查询执行检查（这并不保证查询会更快 - 需要测量），您可以使用以下技巧：

.Where(e => e.UploadDetails.Min(o => o.UploadStatusId == 3 ? 1 : (int?)0) == 1)

它利用了Min<int?>函数在序列没有元素时返回null的事实。加上内部的条件逻辑确保只有在存在与条件匹配的元素且没有不匹配条件的元素时才会返回1，这正是我们需要的。

- Ivan Stoev