EF Core generating reverse query

Question

There are 4 tables:

UploadDate

Id 
Description

UploadType

Id 
Description

UploadStatus

Id 
Description

UploadDetail

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

UplodeDate (data)

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

UplodeType (data)

1    Partner
2    Retail
3    Customer

UplodeStatus (data)

1    Uploaded
2    Processing
3    Successful

UplodeDetail (data)

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

What I am trying to do it get the months for which upload is successful for all upload types

The query

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

So, from UploadDate I am getting where all the entries in UploadDetails are successful. It should give me Aug-2018. But it is giving Dec-2018 and Jan-2019

I checked in SQL Profiler, and it is generating the following query...

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

Basically filtering out everything that is NOT successful, which technically is the reverse of what I want it to generate, something like...

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

Also, if I ran the above query (just above, not the one generated by EF Core, I do get Aug-2018, which is the intended result.

So, why is EF Core generating the query in reverse of what I am intending to write? or have I written a totally wrong query itself?

Answer 1

Both queries return incorrect results.

The EF Core generated SQL query returns [1, 5, 6].

The hand written SQL query (which is the equivalent of using Any instead of All and would have been also generated by EF Core if you do so) is returning [1, 2, 4].

And the desired result is [1].

First, it's a well known fact that

All(condition)

is the same as (equivalent of)

!Any(!condition)

Second fact is (and it can easily be seen) that both expressions return true when the sequence is empty (has no elements). Which is technically correct - all (zero in this case) elements match the condition. Or there is no element not matching the condition.

But doesn't work in your case because what you actually want is to "get the months for which upload exists and upload is successful for all upload types", which is expressed as:

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

or "exists successful upload and does not exist unsuccessful upload", expressed as:

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

Both these conditions will produce the desired behavior. However, they will generate 2 correlated subqueries to perform the check.

If you want to perform the check with just one correlated subquery (which doesn't guarantee the query will be faster - needs to be measured), you can use the following trick:

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

It utilizes the fact that Min<int?> function returns null when the sequence has no elements. This, plus the condition logic inside ensures that it will return 1 only when there are elements matching the condition and no elements not matching it. Which is exactly what we need.