Bring back navigation.Principal key to foreign key optimization

[roji]

In the old query pipeline, when doing entity equality on a dependent whose principal was already traversed, we generated a comparison on the principal's foreign key column(s) instead of on the dependent's key column(s), saving an additional join.

However, if global filters were defined on the dependent, this caused the filters to be ignored. As a result the optimization was removed in 3.0.

Consider bringing this optimization back when no global filters are defined on the dependent.
/cc @smitpatel

[smitpatel]

Related #11691

[GSPP]

Here is another example. The following queries should generate the same SQL (without a join):

                ctx.Orders.Count(o => o.Customer == customer);
                ctx.Orders.Count(o => o.CustomerId == customer.Id);
                ctx.Orders.Count(o => o.Customer.Id == customer.Id);

This executes:

exec sp_executesql N'SELECT COUNT(*)
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerID] = [c].[ID]
WHERE [c].[ID] = @__entity_equality_customer_0_Id',N'@__entity_equality_customer_0_Id int',@__entity_equality_customer_0_Id=12
go
exec sp_executesql N'SELECT COUNT(*)
FROM [Orders] AS [o]
WHERE [o].[CustomerID] = @__customer_Id_0',N'@__customer_Id_0 int',@__customer_Id_0=12
go
exec sp_executesql N'SELECT COUNT(*)
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerID] = [c].[ID]
WHERE [c].[ID] = @__customer_Id_0',N'@__customer_Id_0 int',@__customer_Id_0=12
go

Rewriting the query is possible as a workaround, but it would be nice to be able to express queries the way you want it semantically.

The form o.Customer == customer is convenient because customer can be null and it just works. When writing o.CustomerId == customer.Id there is a null reference issue.