There is an entry for table "r", but it cannot be referenced from this part of the query.

[roji]

Originally opened by @StasPerekrestov as npgsql/efcore.pg#1276.

Hello,
one of my colleagues has found a pretty interesting case when EF with npgsql provider
produces invalid SQL.
I decided to post it here, because this error may indicate a bug.

Thanks.

Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: invalid reference to FROM-clause entry for table "r"
          Hint: There is an entry for table "r", but it cannot be referenced from this part of the query.
          Position: 339
          File: parse_relation.c
          Line: 3234
          Routine: errorMissingRTE
Npgsql.PostgresException (0x80004005): 42P01: invalid reference to FROM-clause entry for table "r"

ef_invalid_sql_query.linq.txt

async Task Main()
{
	var loggerFactory = LoggerFactory.Create(builder => builder.SetMinimumLevel(LogLevel.Trace).AddConsole());
	var ob = new DbContextOptionsBuilder<PolicyDbContext>()
						//.UseInMemoryDatabase("testDb")
						.UseNpgsql("...")
						.UseSnakeCaseNamingConvention()
						.UseLoggerFactory(loggerFactory);
	
	using var ctx = new PolicyDbContext(ob.Options);
	ctx.User
	.SelectMany(user => user.UserRoles.DefaultIfEmpty()
	.SelectMany(ur => ur.Role.RolePermissions.DefaultIfEmpty()
	.Select(rp => new { User = user.UserName, Role = ur.Role.Name, Permission = rp.Permission.Name })))
	.Dump();
}

public class PolicyDbContext : DbContext
{
	public PolicyDbContext(DbContextOptions<PolicyDbContext> options)
		: base(options) { }

	protected PolicyDbContext() { }

	public virtual DbSet<User> User { get; set; }
	public virtual DbSet<Role> Role { get; set; }
	public virtual DbSet<Permission> Permission { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)

	{
		modelBuilder.Entity<RolePermission>(entity =>
		{
			entity.HasKey(d => new { d.RoleId, d.PermissionId });
			entity.Property(e => e.RoleId);
			entity.Property(e => e.PermissionId);
			entity.HasOne(d => d.Role)
				  .WithMany(p => p.RolePermissions)
				  .HasForeignKey(d => d.RoleId);

			entity.HasOne(d => d.Permission)
				  .WithMany(p => p.RolePermissions)
				  .HasForeignKey(d => d.PermissionId);
		});

		modelBuilder.Entity<UserRole>(entity =>
			{
				entity.HasKey(d => new { d.UserId, d.RoleId });

				entity.Property(e => e.UserId);
				entity.Property(e => e.RoleId);

				entity.HasOne(d => d.User)
					  .WithMany(p => p.UserRoles)
					  .HasForeignKey(d => d.UserId);

				entity.HasOne(d => d.Role)
					  .WithMany(p => p.UserRoles)
					  .HasForeignKey(d => d.RoleId);
			});
	}
}

public class Permission
{
	public Permission()
	{
		RolePermissions = new HashSet<RolePermission>();
	}

	public int Id { get; set; }
	public string Name { get; set; }
	public virtual ICollection<RolePermission> RolePermissions { get; set; }
}
public class Role
{
	public Role()
	{
		RolePermissions = new HashSet<RolePermission>();
		UserRoles = new HashSet<UserRole>();
	}

	public int Id { get; set; }
	public string Name { get; set; }
	public virtual ICollection<RolePermission> RolePermissions { get; set; }
	public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class RolePermission
{
	public int RoleId { get; set; }	
	public int PermissionId { get; set; }
	public virtual Role Role { get; set; }
	public virtual Permission Permission { get; set; }
}
public class User
{
	public User()
	{
		UserRoles = new HashSet<UserRole>();
	}

	public int Id { get; set; }
	public string Token { get; set; }
	public string UserName { get; set; }

	public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class UserRole
{
	public int UserId { get; set; }
	public int RoleId { get; set; }

	public virtual User User { get; set; }
	public virtual Role Role { get; set; }
}
// Define other methods, classes and namespaces here

dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT t0.user_name AS "User", t0.name AS "Role", t0.name0 AS "Permission"
      FROM "user" AS u
      LEFT JOIN LATERAL (
          SELECT t.user_name, t.name, t.name0, u0.user_id, u0.role_id, t.role_id AS role_id0, t.permission_id
          FROM user_role AS u0
          LEFT JOIN role AS r ON u0.role_id = r.id
          INNER JOIN (
              SELECT u.user_name, r.name, p.name AS name0, r0.role_id, r0.permission_id
              FROM role_permission AS r0
              LEFT JOIN permission AS p ON r0.permission_id = p.id
          ) AS t ON r.id = t.role_id
          WHERE u.id = u0.user_id
      ) AS t0 ON TRUE
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (179ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT t0.user_name AS "User", t0.name AS "Role", t0.name0 AS "Permission"
      FROM "user" AS u
      LEFT JOIN LATERAL (
          SELECT t.user_name, t.name, t.name0, u0.user_id, u0.role_id, t.role_id AS role_id0, t.permission_id
          FROM user_role AS u0
          LEFT JOIN role AS r ON u0.role_id = r.id
          INNER JOIN (
              SELECT u.user_name, r.name, p.name AS name0, r0.role_id, r0.permission_id
              FROM role_permission AS r0
              LEFT JOIN permission AS p ON r0.permission_id = p.id
          ) AS t ON r.id = t.role_id
          WHERE u.id = u0.user_id
      ) AS t0 ON TRUE
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'policy' on server 'tcp://127.0.0.1:5432'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'policy' on server 'tcp://127.0.0.1:5432'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'UserQuery+PolicyDbContext'.
      Npgsql.PostgresException (0x80004005): 42P01: invalid reference to FROM-clause entry for table "r"

[smitpatel]

Assigning to @maumar - #19968 would fix this too. Requires regression test probably.

[maumar]

After #19825 was fixed this scenario exposed another issue, already tracked by #19095 - closing as duplicate