I have the following EF Core 3.1 statement:
var orders = await _dbContext.Orders.Include(o => o.OrderReceivers)
.Where(o => o.BooleanFlag1 && o.OrderState == OrderState.SomeState && o.OrderReceivers.Any(o => o.BooleanFlag2))
.ToListAsync(cancellationToken);
and this is the generated SQL Server query:
SELECT /*All column names here*/
FROM [Schema].[Orders] AS [o]
LEFT JOIN [Schema].[OrderReceivers] AS [o0] ON [o].[Id] = [o0].[OrderId]
WHERE (([o].[ShouldSendBlackList] = CAST(1 AS bit)) AND ([o].[OrderState] = 2)) AND EXISTS (
SELECT 1
FROM [Schema].[OrderReceivers] AS [o1]
WHERE ([o].[Id] = [o1].[OrderId]) AND ([o1].[BooleanFlag2] = '1'))
ORDER BY [o].[Id], [o0].[Id]
The problem with the generated SQL Query is this part:
([o1].[BooleanFlag2] = '1')
Because this causes the second where clause to be always false! Any ideas on what is wrong here? Thanks in advance.