Left Join with where not working

[janfokke]

I'm trying to perform a left join, 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 the following error

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

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

[roji]

Which version of the Npgsql.EntityFrameworkCore.PostgreSQL provider are you using? If you're using 2.2, can you please give 3.0.0-preview8 a try, as things have significantly changed there?

[roji]

That said, the query you're trying to translate seems particularly complex, so it may not be translatable (at least not at the moment). There may be ways to rewrite it in a simpler manner.

[janfokke]

I updated to 3.0.0-preview8 and it seems like GroupJoin is not implemented yet

[janfokke]

I know the query is complex, but I want the query to run in one go

normally I would have written something like this

context.Accounts.Where(account => account.Id == 6).SelectMany(account =>
                    account.Streams.Select(stream => new
                    {
                        Stream = stream,
                        Subsribers = context.Subscriptions.Where(subscription =>
                            subscription.Date > stream.StreamStart && subscription.Date < stream.StreamEnd)
                    })).ToList();

But this results in a query that will be evaluated locally.
I also tested it in 3.0.0-preview8 and it seems like SelectMany is not implemented yet

[janfokke]

The error also ocurs with the SqlServer provider, so it isn't specific to PostgreSQL.

new Issue dotnet/efcore#17382