Query: Include on derived entity with composite key produced duplicated Discriminator column predicates

[maumar]

Introduced by fix to #9395 - before the join predicates on derived types were not sargable. However after the fix, SQL we generate for derived entities with composite keys has some redundant terms.

We should add optimization to remove the duplicated terms if all expressions are connected with AndAlso operator.

[maumar]

Example:

Include_reference_on_derived_type_using_lambda_with_tracking produces:

SELECT [l].[Name], [l].[Discriminator], [l].[LocustHordeId], [l].[ThreatLevel], [l].[DefeatedByNickname], [l].[DefeatedBySquadId], [l].[HighCommandId], [t].[Nickname], [t].[SquadId], [t].[AssignedCityName], [t].[CityOrBirthName], [t].[Discriminator], [t].[FullName], [t].[HasSoulPatch], [t].[LeaderNickname], [t].[LeaderSquadId], [t].[Rank]
FROM [LocustLeaders] AS [l]
LEFT JOIN (
    SELECT [l.DefeatedBy].*
    FROM [Gears] AS [l.DefeatedBy]
    WHERE [l.DefeatedBy].[Discriminator] IN (N'Officer', N'Gear')
) AS [t] ON (([l].[Discriminator] = N'LocustCommander') AND ([l].[DefeatedByNickname] = [t].[Nickname])) AND (([l].[Discriminator] = N'LocustCommander') AND ([l].[DefeatedBySquadId] = [t].[SquadId]))
WHERE [l].[Discriminator] IN (N'LocustCommander', N'LocustLeader')

[DibyodyutiMondal]

Is there any way to remove the dicriminator predicate for a query?
For example, if I have a query that only involves properties of the base class, but not of the derived class, ef core still adds an extra where clause with an in. Is there any way to opt-out of this behaviour, individually, or globally?

[ajcvickers]

@DibyodyutiMondal I don't believe there is currently a way to opt out of this behavior.

[smitpatel]

Generated SQL in new pipeline

SELECT [l].[Name], [l].[Discriminator], [l].[LocustHordeId], [l].[ThreatLevel], [l].[ThreatLevelByte], [l].[ThreatLevelNullableByte], [l].[DefeatedByNickname], [l].[DefeatedBySquadId], [l].[HighCommandId], [t].[Nickname], [t].[SquadId], [t].[AssignedCityName], [t].[CityOfBirthName], [t].[Discriminator], [t].[FullName], [t].[HasSoulPatch], [t].[LeaderNickname], [t].[LeaderSquadId], [t].[Rank]
FROM [LocustLeaders] AS [l]
LEFT JOIN (
    SELECT [g].[Nickname], [g].[SquadId], [g].[AssignedCityName], [g].[CityOfBirthName], [g].[Discriminator], [g].[FullName], [g].[HasSoulPatch], [g].[LeaderNickname], [g].[LeaderSquadId], [g].[Rank]
    FROM [Gears] AS [g]
    WHERE [g].[Discriminator] IN (N'Gear', N'Officer')
) AS [t] ON ([l].[DefeatedByNickname] = [t].[Nickname]) AND ([l].[DefeatedBySquadId] = [t].[SquadId])
WHERE [l].[Discriminator] IN (N'LocustLeader', N'LocustCommander')

After discriminator mapping being complete

SELECT [l].[Name], [l].[Discriminator], [l].[LocustHordeId], [l].[ThreatLevel], [l].[ThreatLevelByte], [l].[ThreatLevelNullableByte], [l].[DefeatedByNickname], [l].[DefeatedBySquadId], [l].[HighCommandId], [g].[Nickname], [g].[SquadId], [g].[AssignedCityName], [g].[CityOfBirthName], [g].[Discriminator], [g].[FullName], [g].[HasSoulPatch], [g].[LeaderNickname], [g].[LeaderSquadId], [g].[Rank]
FROM [LocustLeaders] AS [l]
LEFT JOIN [Gears] AS [g] ON ([l].[DefeatedByNickname] = [g].[Nickname]) AND ([l].[DefeatedBySquadId] = [g].[SquadId])

[szszoke]

@ajcvickers

Are there any plans to optimize the discriminator predicate in cases where only the base class is queried?

I have a DbSet for an abstract base class and two more more specialized DbSets for classes that inherit the base class.

I have a boolean discriminator field that cannot be null.

The discriminator predicate can be omitted entirely for queries against the base DbSet but EF still generates this: IN (FALSE, TRUE).

[ajcvickers]

@szszoke See #18106