Multiple LeftJoins (GroupJoins) lead to GroupJoin Exception when the same where is used twice

[Mr-Pearce]

File a bug

The code below (doesntwork) throws

The LINQ expression 'DbSet<Message>()
    .GroupJoin(
        inner: DbSet<Recipient>(), 
        outerKeySelector: message => message.Id, 
        innerKeySelector: recipient => recipient.Message.Id, 
        resultSelector: (message, recipientGrouping) => new { 
            message = message, 
            recipientGrouping = recipientGrouping
         })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

but the other two are working fine.

The Generated SQL is from npgsql Core but the same exception happens with the InMemoryDatabase in the example project.

Include your code

BrokenLeftJoin.zip
Example is in the Program.cs

Guid userId = Guid.Parse("a74fe6ee-11ae-4cc2-bdbe-6e9e880db745");

var works = await (
	from message in dbcontext.Messages
	join recipient in dbcontext.Recipient
		on message.Id equals recipient.Message.Id into recipientGrouping
	from recipientg in recipientGrouping.Where(x => x.UserId == Guid.Parse("a74fe6ee-11ae-4cc2-bdbe-6e9e880db745")).DefaultIfEmpty()
	join reads in dbcontext.ReadBy
		on message.Id equals reads.Message.Id into readsGrouping
	from readsg in readsGrouping.Where(x => x.UserId == Guid.Parse("a74fe6ee-11ae-4cc2-bdbe-6e9e880db745")).DefaultIfEmpty()
	select new { message, recipientg, readsg }
		 ).ToListAsync();

//works  creates
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT m."Id", t."Id", t."MessageId", t."UserId", t0."Id", t0."MessageId", t0."UserId"
FROM dbo."Message" AS m
LEFT JOIN (
  SELECT r."Id", r."MessageId", r."UserId", m0."Id" AS "Id0"
  FROM dbo."Recipient" AS r
  INNER JOIN dbo."Message" AS m0 ON r."MessageId" = m0."Id"
  WHERE r."UserId" = 'a74fe6ee-11ae-4cc2-bdbe-6e9e880db745'
) AS t ON m."Id" = t."Id0"
LEFT JOIN (
  SELECT r0."Id", r0."MessageId", r0."UserId", m1."Id" AS "Id0"
  FROM dbo."ReadBy" AS r0
  INNER JOIN dbo."Message" AS m1 ON r0."MessageId" = m1."Id"
  WHERE r0."UserId" = 'a74fe6ee-11ae-4cc2-bdbe-6e9e880db745'
) AS t0 ON m."Id" = t0."Id0"

var workstoo = await (
	from message in dbcontext.Messages
	join recipient in dbcontext.Recipient
		on message.Id equals recipient.Message.Id into recipientGrouping
	from recipientg in recipientGrouping.Where(x => x.UserId == Guid.Parse("a74fe6ee-11ae-4cc2-bdbe-6e9e880db745")).DefaultIfEmpty()
	join reads in dbcontext.ReadBy
		on message.Id equals reads.Message.Id into readsGrouping
	from readsg in readsGrouping.Where(x => x.UserId == userId).DefaultIfEmpty()
	select new { message, recipientg, readsg }
 ).ToListAsync();

//workstoo creates
Executing DbCommand [Parameters=[@__userId_0='a74fe6ee-11ae-4cc2-bdbe-6e9e880db745'], CommandType='Text', CommandTimeout='30']
SELECT m."Id", t."Id", t."MessageId", t."UserId", t0."Id", t0."MessageId", t0."UserId"
FROM dbo."Message" AS m
LEFT JOIN (
  SELECT r."Id", r."MessageId", r."UserId", m0."Id" AS "Id0"
  FROM dbo."Recipient" AS r
  INNER JOIN dbo."Message" AS m0 ON r."MessageId" = m0."Id"
  WHERE r."UserId" = 'a74fe6ee-11ae-4cc2-bdbe-6e9e880db745'
) AS t ON m."Id" = t."Id0"
LEFT JOIN LATERAL (
  SELECT r0."Id", r0."MessageId", r0."UserId"
  FROM dbo."ReadBy" AS r0
  INNER JOIN dbo."Message" AS m1 ON r0."MessageId" = m1."Id"
  WHERE m."Id" = m1."Id" AND r0."UserId" = @__userId_0
) AS t0 ON TRUE

//Throws Groupjoin Exception
var dosntwork = await (from message in dbcontext.Messages
					   join recipient in dbcontext.Recipient
							on message.Id equals recipient.Message.Id into recipientGrouping
					   from recipientg in recipientGrouping.Where(x => x.UserId == userId).DefaultIfEmpty()
					   join reads in dbcontext.ReadBy
							on message.Id equals reads.Message.Id into readsGrouping
					   from readsg in readsGrouping.Where(x => x.UserId == userId).DefaultIfEmpty()
					   select new { message, recipientg, readsg }
						 ).ToListAsync();

Include stack traces

Include verbose output

Please include --verbose output when filing bugs about the dotnet ef or Package Manager Console tools.

Use triple-tick fences for tool output. For example:

PS C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin> dotnet ef dbcontext list --verbose
Using project 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj'.
Using startup project 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj'.
Writing 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\obj\BrokenLeftJoin.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata "/property:EFProjectMetadataFile=C:\Users\Me\AppData\Local\Temp\tmpFCE2.tmp" /verbosity:quiet /nologo C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj
Writing 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\obj\BrokenLeftJoin.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata "/property:EFProjectMetadataFile=C:\Users\Me\AppData\Local\Temp\tmpFF64.tmp" /verbosity:quiet /nologo C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj
Build started...
dotnet build C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj /verbosity:quiet /nologo
C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\Program.cs(106,30): warning CS0168: Die Variable "ex" ist deklariert, wird aber nie verwendet. [C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj]

Der Buildvorgang wurde erfolgreich ausgeführt.

C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\Program.cs(106,30): warning CS0168: Die Variable "ex" ist deklariert, wird aber nie verwendet. [C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj]
    1 Warnung(en)
    0 Fehler

Verstrichene Zeit 00:00:01.76
Build succeeded.
dotnet exec --depsfile C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\bin\Debug\net7.0\BrokenLeftJoin.deps.json --additionalprobingpath "C:\Users\Me\.nuget\packages" --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\bin\Debug\net7.0\BrokenLeftJoin.runtimeconfig.json "C:\Users\Me\.dotnet\tools\.store\dotnet-ef\7.0.4\dotnet-ef\7.0.4\tools\net6.0\any\tools\netcoreapp2.0\any\ef.dll" dbcontext list --assembly C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\bin\Debug\net7.0\BrokenLeftJoin.dll --project C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj --startup-assembly C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\bin\Debug\net7.0\BrokenLeftJoin.dll --startup-project C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\BrokenLeftJoin.csproj --project-dir C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\ --root-namespace BrokenLeftJoin --language C# --framework net7.0 --nullable --working-dir C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin --verbose
Using assembly 'BrokenLeftJoin'.
Using startup assembly 'BrokenLeftJoin'.
Using application base 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\bin\Debug\net7.0'.
Using working directory 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin'.
Using root namespace 'BrokenLeftJoin'.
Using project directory 'C:\SVN\test\BrokenLeftJoin\BrokenLeftJoin\'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'BrokenLeftJoin'...
Finding Microsoft.Extensions.Hosting service provider...
Using environment 'Development'.
Using application service provider from Microsoft.Extensions.Hosting.
Found DbContext 'TestDbContext'.
Finding DbContext classes in the project...
BrokenLeftJoin.TestDbContext

Include provider and version information

EF Core version:
Database providers:
Microsoft.EntityFrameworkCore.InMemory 7.0.4
Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4
Target framework: (e.g. .NET 7.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.5.2)

[ajcvickers]

/cc @maumar

[maumar]

problem is in QueryableMethodNormalizingExpressionVisitor and specifically part where we convert from GroupJoin-SelectMany-DefaultIfEmpty into left join (SelectManyVerifyingExpressionVisitor). We check if the collection selector is correlated, and we do that by looking at parameters in that lambda. Problem is that the affected queries reference outside variable that gets parameterized and that breaks the correlation finding logic.

[Mr-Pearce]

I Just saw that you added the 8.0.0 Milestone.
Since my Problem is with 7.0.4,
will this also be fixed in future 7.0.x and 6.0.x(LTS) versions or only in 8?

[ajcvickers]

@Mr-Pearce Doesn't look like it meets the bar for servicing, so currently we don't plan to backport this to 6 or 7.

[maumar]

reopen for potential patching - fix is much simpler/safer than we initially thought