LINQ Subquery Where Clause could not be translated

Question

I am converting this query to linq in EF Core 2.2 but I could not find any correct way:

select r.*,v.* from Planning.RefineryUnitMonthDatas r
join Planning.RefineryUnitMonthDataValues v on r.Id = v.EntityId
    join(
        select EntityId, max(v.CreatedAt) CreatedAt from Planning.RefineryUnitMonthDatas r
        join Planning.RefineryUnitMonthDataValues v on r.Id = v.EntityId
        where RefineryUnitId = @refinery and Date / 100 = @date
        group by EntityId
    ) t on r.Id = t.EntityId
    where t.CreatedAt = v.CreatedAt

This is my linq:

 from s in db.RefineryUnitMonthDatas
 join v in db.RefineryUnitMonthDataValues on s.Id equals v.EntityId
 join r in (
      from r in db.RefineryUnitMonthDatas
      join v in db.RefineryUnitMonthDataValues on r.Id equals v.EntityId
      where r.RefineryUnitId == refinery  && r.Date / 100 == date
      group v by v.EntityId into g
      select new { g.Key, CreatedAt = g.Max(s => s.CreatedAt) }
 ) on s.Id equals r.Key
 where r.CreatedAt == v.CreatedAt
 select new { s, v }

This works correctly but has performance issue and the last Where clause is not translated to sql. It warns:

The LINQ expression 'where ([r].CreatedAt == [v].CreatedAt)' could not be translated and will be evaluated locally

Does Core 2.2 support where clause for subquery or I made a mistake somewhere?

Answer 1

Seems to be one of the (many) EFC 2.2 query translator bugs/defects/shortcomings. Works as expected in EFC 3.1.

From all the "query patterns" (as they call them), the only one working I was able to found is to push the predicate into join clause, e.g. replace

) on s.Id equals r.Key
where r.CreatedAt == v.CreatedAt

with something like

) on new { Key = s.Id, v.CreatedAt } equals new { r.Key, r.CreatedAt }

Of course the workaround works only for == comparisons which can be turned into equi-join.