EntityFrameworkCore PostgreSQL Left Join with where not working

I'm trying to perform a left join using Entity Framework Core, but it creates a subquery in the join that doesn't work.

I created a sample project to illustrate the problem

This is my Linq query which selects all streams from an account and the subscribers during that stream

Linq query syntax

    from account in context.Accounts
    where account.Id == 6
    join steam in context.Streams on account.Id equals steam.AccountId
    join subscription in context.Subscriptions on account.Id equals subscription.TargetAccountId into
        groupJoin
    from subscription in groupJoin.Where(subscription => subscription.Date > steam.StreamStart && subscription.Date < steam.StreamEnd).DefaultIfEmpty()
    select new {account, steam, subscription};

Linq extention method syntax

    context.Accounts.Where(account => account.Id == 6)
        .Join(context.Streams, outer => outer.Id, inner => inner.AccountId,
        (account, stream) => new { Account = account, Stream = stream })
        .GroupJoin(context.Subscriptions, outer => outer.Account.Id, inner => inner.TargetAccountId,
        (outer, subscriptions) => new
        { Account = outer.Account, Stream = outer.Stream, Subscriptions = subscriptions })
        .SelectMany(x =>
            x.Subscriptions.Where(subscription =>
                    subscription.Date > x.Stream.StreamStart && subscription.Date < x.Stream.StreamEnd)
                .DefaultIfEmpty(),
        (x, subscription) => new { x.Account, x.Stream, Subscription = subscription }).ToList();

This is the query that gets generated

    SELECT account."Id", account."Name", stream."Id", stream."AccountId", stream."StreamEnd", stream."StreamStart", stream."Title", t."SourceAccountId", t."TargetAccountId", t."Date"
          FROM "Accounts" AS account
          INNER JOIN "Streams" AS stream ON account."Id" = stream."AccountId"
          LEFT JOIN (
              SELECT "inner"."SourceAccountId", "inner"."TargetAccountId", "inner"."Date"
              FROM "Subscriptions" AS "inner"
              WHERE ("inner"."Date" > stream."StreamStart") AND ("inner"."Date" < stream."StreamEnd")
          ) AS t ON account."Id" = t."TargetAccountId"
          WHERE account."Id" = 6

Which gives me the following error

Npgsql.PostgresException (0x80004005): 42P01: invalid reference to FROM-clause entry for table "stream"

Is my Linq query wrong or is this a bug/limitation in Entity Framework?

Definitely EF Core query translation bug - reproduces with the latest EF Core 2.2.6 and also SqlServer provider, so it's not Npgsql provider specific.

Since 3.0 will use totally rewritten query translator, and in the current preview many things don't function properly, it can't be said if it would be fixed there or not.

The workaround is to move the correlated filter outside the join (with additional null check to account for left outer join introduced by DefaultIfEmpty()):

var result = context.Accounts
    .Where(account => account.Id == 6)
    .Join(context.Streams, outer => outer.Id, inner => inner.AccountId,
        (account, stream) => new { Account = account, Stream = stream })
    .GroupJoin(context.Subscriptions, outer => outer.Account.Id, inner => inner.TargetAccountId,
        (outer, subscriptions) => new
        { Account = outer.Account, Stream = outer.Stream, Subscriptions = subscriptions })
    .SelectMany(x => x.Subscriptions.DefaultIfEmpty(),
        (x, subscription) => new { x.Account, x.Stream, Subscription = subscription })
    .Where(x => x.Subscription == null || (
        x.Subscription.Date > x.Stream.StreamStart &&
        x.Subscription.Date < x.Stream.StreamEnd))
    .ToList();