Aggregate on optional dependent lacks condition

[AndriySvyryd]

See GraphUpdatesSqlServerTest.Owned.Required_one_to_one_are_cascade_deleted

context.Set<Root>().Select(r => r.RequiredSingle).Count(e => e.Id == removedId)

SELECT COUNT(*)
FROM [Root] AS [r]
WHERE [r].[Id] = @__removedId_0

[smitpatel]

Would the count be different though?
Is RequiredSingle optional? 😆

[AndriySvyryd]

Yes, despite the name

[AndriySvyryd]

In this case left join isn't actually needed, but a condition on the nullable RequiredSingle_Bool column is.

This query works correctly:

context.Set<Root>().Any(r => r.RequiredSingle != null)

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Root] AS [r]
        WHERE [r].[RequiredSingle_Bool] IS NOT NULL) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

But these also lack the condition:

context.Set<Root>().Select(r => r.RequiredSingle).Select(e => e.Id == removedId)

SELECT CASE
    WHEN [r].[Id] = @__removedId_0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END
FROM [Root] AS [r]

context.Set<Root>().Select(r => r.RequiredSingle).AsNoTracking()

SELECT [r].[Id], [r].[RequiredSingle_Bool], [r].[RequiredSingle_Single_Bool]
FROM [Root] AS [r]

[smitpatel]

This would redefine the concept of property access on owned entities. Basically, "e.Id" in the query cannot be accessed by just using the column from the database. And we need to add checks for principal (whole chain) to verify existence of the instance everytime any of the shared columns are used (except in final materialization). Do we want to go down that path?
Somewhat related, for TPH scenario while accessing derived type with casting which could be sharing column with a sibling, we decided to ignore it could give incorrect results and just use the column. #20335

[AndriySvyryd]

Do we want to go down that path?

If we don't then we need to throw in this case as

context.Set<Root>().Select(r => r.RequiredSingle).Select(e => e.Id == removedId);

is different from

context.Set<Root>().Select(e => e.Id == removedId)

Somewhat related, for TPH scenario while accessing derived type with casting which could be sharing column with a sibling, we decided to ignore it could give incorrect results and just use the column. #20335

That's different because there's no other way of getting the shared behavior and non-shared behavior is available without casting.

[smitpatel]

Design, we should generate conditional in SQL to access property conditionally.
We should also simplify case blocks if we are not doing already.
a ? b : null == c => a AND b == c