Left Join Performance

Reproduce the LINQ query using EF Core 2.1.4 and the SQL-CE 2.1.2.1 provider, then compare the generated LEFT JOIN with and without OfType(). Investigate whether the type predicate can be placed in the JOIN condition rather than a derived-table subquery; done means the resulting SQL avoids the reported SQL-CE performance regression.

Scenario: consider a query with two tables involved. Table-A and Table-B.

• Table-B is left joined to Table-A
• Table-B is set up to be a Table-Per-Hierarchy
• Table-B is a large table (high volume of records), as is Table-A

So a query such as

var results = from recA in context.Set<Table-A>()
          join recB in context.Set<Table-B>().OfType<Some-Sub-Type>()
          on recA.Id equals recB.RecAId into recBJoined
          from theRecB in recBJoined.DefaultIfEmpty()
          select recA;

translates to something like

SELECT [recA].[Id], [recA].[Created], [recA].[Name]
      FROM [Table-A] AS [recA]
      LEFT JOIN (
          SELECT [w].*
          FROM [Table-B] AS [w]
          WHERE [w].[Type] IN (18, 21, 29, 41, 39, 17, 19, 66, 20, 22, 85, 9)
      ) AS [t] ON [recA].[Id] = [t].[recAId]
      

With SQL-CE this has a huge performance impact. I would like to mention that in some situations, the join can only be pulled off on a sub-class and not the base class within the TPH. As an example

 join recB in context.Set<Table-B>().OfType<Some-Sub-Type>()
          on recA.Id equals recB.RecAId into recBJoined

the join "recA.Id equals recB.RecAId" may only be possible (and required) on "Some-Sub-Type" and not possible either on the root Table-B or "Some-Other-Sub-Type".

If the query were to be constructed without the sub query, something like...

SELECT [recA].[Id], [recA].[Created], [recA].[Name]
      FROM [Table-A] AS [recA]
      LEFT JOIN [Table-B] AS [w] on [recA].[Id] = [w].[recAId] and [w].[Type] IN (18, 21, 29, 41, 39, 17, 19, 66, 20, 22, 85, 9) 

the performance is far superior and problem free.

I have run this query on MS-SQL too and find a similar query being generated. The performance (purely from an execution time POV) seems ok. Off-hand I'm not sure if SQL optimises such a pattern.

This certainly is an issue with SQL-CE. As a work around, I have tried adding an additional join to Table-A like below. While this does not help Readability/Comprehension/Maintenance it offers marginal improvement. As the volume of data being returned goes up, the performance degrades quickly.

var tableBFilter = from recA in context.Set<Table-A>()
         join recB in context.Set<Table-B>().OfType<Some-Sub-Type>()
         on recA.Id equals recB.RecAId 
         select recB;

var results = from recA in context.Set<Table-A>()
         join recB in tableBFilter on recA.Id equals recB.RecAId into recBJoined
         from theRecB in recBJoined.DefaultIfEmpty()
         select recA;

What techniques can help circumvent such an issue ?
Will appreciate advice/guidance.
Can we influence the query generation in any way ?

Thanks,
\A

Further technical details

EF Core version: 2.1.4
Database Provider: Sql-CE 2.1.2.1
Operating system: Win10
IDE: (e.g. Visual Studio 2017 18.4?)