EF Core генерирует обратный запрос

Question

Есть 4 таблицы:

Дата загрузки

Id 
Description

UploadType

Id 
Description

UploadStatus

Id 
Description

UploadDetail

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

UplodeDate (данные)

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

UplodeType (данные)

1    Partner
2    Retail
3    Customer

UplodeStatus (данные)

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)))

В основном отфильтровывает все, что есть NOT успешно, что технически является reverse из того, что я хочу это генерировать, что-то вроде...

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 Генерация запроса в обратном направлении, что я собираюсь написать? или я сам написал совершенно неправильный запрос?

Answer 1

Оба запроса возвращают неверные результаты.

SQL-запрос, сгенерированный EF Core, возвращает [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 только когда есть элементы, соответствующие условию, и нет элементов, не соответствующих ему. Что именно то, что нам нужно.