Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Left Join with Where before DefaultIfEmpty not working #17382

Closed
janfokke opened this issue Aug 22, 2019 · 4 comments
Closed

Left Join with Where before DefaultIfEmpty not working #17382

janfokke opened this issue Aug 22, 2019 · 4 comments

Comments

@janfokke
Copy link

I'm trying to perform a left join, but it creates a subquery in the join that doesn't work.
I'm using Npgsql.EntityFrameworkCore.PostgreSQL 2.2.4, but the error also ocurs with the SqlServer provider, so it's not Npgsql provider specific.

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

    fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT account."Id", account."Name", steam."Id", steam."AccountId", steam."StreamEnd", steam."StreamStart", steam."Title", t."SourceAccountId", t."TargetAccountId", t."Date"
      FROM "Accounts" AS account
      INNER JOIN "Streams" AS steam ON account."Id" = steam."AccountId"
      LEFT JOIN (
          SELECT subscription."SourceAccountId", subscription."TargetAccountId", subscription."Date"
          FROM "Subscriptions" AS subscription
          WHERE (subscription."Date" > steam."StreamStart") AND (subscription."Date" < steam."StreamEnd")
      ) AS t ON account."Id" = t."TargetAccountId"
      WHERE account."Id" = 6
Npgsql.PostgresException (0x80004005): 42P01: invalid reference to FROM-clause entry for table "steam"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1218
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
@smitpatel
Copy link
Contributor

Duplicate of #17112

@smitpatel smitpatel marked this as a duplicate of #17112 Aug 22, 2019
@smitpatel
Copy link
Contributor

    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};

That is not left join. In order for it to be left join, you cannot reference any parameter from outer scope inside select many. SelectMany collectionSelector needs to be fully in terms of grouping element only.

@janfokke
Copy link
Author

janfokke commented Aug 22, 2019

Will #17112 also solve this scenario in the new release?

@smitpatel
Copy link
Contributor

The scenario you write it Outer Apply operation in SqlServer. It would probably be Left Join lateral in Postgre. It is not left join.

@ajcvickers ajcvickers reopened this Oct 16, 2022
@ajcvickers ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants