Invalid SQL generation using OwnsOne properties with .ToTable()

[AlexKulbako]

Hi, everyone.
I have the following model schema:

    public class User
    {
        protected User()
        {
        }

        public User(int id, string name, Address address, ContactInfo contactInfo)
        {
            Id = id;
            Name = name;
            Address = address;
            ContactInfo = contactInfo;
        }

        public int Id { get; }
        public string Name { get; }
        public Address Address { get; }
        public ContactInfo ContactInfo { get; }
    }

    public class Address
    {
        public Address(string line1, string line2)
        {
            Line1 = line1;
            Line2 = line2;
        }

        public string Line1 { get; }
        public string Line2 { get; }
    }

    public class ContactInfo
    {
        public ContactInfo(string email, string webUrl)
        {
            Email = email;
            WebUrl = webUrl;
        }

        public string Email { get; }
        public string WebUrl { get; }
    }

My OnModelCreating method:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.OwnsOne(e => e.Address, p =>
                {
                    p.Property(pp => pp.Line1)
                        .HasColumnName("AddressLine1")
                        .HasMaxLength(2000);
                    p.Property(pp => pp.Line2)
                        .HasColumnName("AddressLine2")
                        .HasMaxLength(2000);
                });
                entity.OwnsOne(e => e.ContactInfo, p =>
                {
                    p.Property(pp => pp.Email)
                        .HasColumnName(nameof(User.ContactInfo.Email))
                        .HasMaxLength(50);
                    p.Property(pp => pp.WebUrl)
                        .HasColumnName(nameof(User.ContactInfo.WebUrl));

                    p.ToTable(nameof(User.ContactInfo));
                });
            });
        }

As you can see, I'm using OwnsOne properties differently.
Address belongs to the same table.
ContactInfo belongs to the different table ContactInfo

When I run a query

var users = context.Users.ToList();

it works perfectly and generates a correct query from the debug mode:

SELECT u."Id", u."AddressLine1", u."AddressLine2", c."UserId", c."Email", c."WebUrl"
FROM "Users" AS u
LEFT JOIN "ContactInfo" AS c ON u."Id" = c."UserId"

When I run
var users = context.Users ​.Take(10).ToList();
then an exception is thrown:

Npgsql.PostgresException (0x80004005): 42P01: missing FROM-clause entry for table "u"

POSITION: 16
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at CT.HotelProfile.Host.TestHostedService.ExecuteAsync(CancellationToken stoppingToken) in /Users/oleksii.kulbako/Desktop/clicktrip/hotel-profile/src/Host/TestHostedService.cs:line 28
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: missing FROM-clause entry for table "u"
    Position: 16
    File: parse_relation.c
    Line: 3460
    Routine: errorMissingRTE

Debug query:

 SELECT t."Id", u."Id", u."AddressLine1", u."AddressLine2", c."UserId", c."Email", c."WebUrl"
      FROM (
          SELECT u."Id"
          FROM "Users" AS u
          LIMIT @__p_0
      ) AS t
      LEFT JOIN "ContactInfo" AS c ON t."Id" = c."UserId"

I've noticed another 'nice' thing. If I change my query to
var users = context.Users.Take(10).OrderBy(e => e.Id).ToList();
then it works perfectly and generates the following query:

      SELECT t."Id", t."AddressLine1", t."AddressLine2", c."UserId", c."Email", c."WebUrl"
      FROM (
          SELECT u."Id", u."AddressLine1", u."AddressLine2"
          FROM "Users" AS u
          LIMIT @__p_0
      ) AS t
      LEFT JOIN "ContactInfo" AS c ON t."Id" = c."UserId"
      ORDER BY t."Id"

My references:

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0" />
 <PackageReference Include="Npgsql" Version="6.0.2" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="6.0.2" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0">
            <PrivateAssets>all</PrivateAssets>
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
  </PackageReference>

[roji]

Note: the same repros on SQL Server:

SELECT [t].[Id], [u].[Id], [u].[AddressLine1], [u].[AddressLine2], [c].[UserId], [c].[Email], [c].[WebUrl]
FROM (
    SELECT TOP(@__p_0) [u].[Id]
    FROM [Users] AS [u]
) AS [t]
LEFT JOIN [ContactInfo] AS [c] ON [t].[Id] = [c].[UserId]

Error:

The multi-part identifier "u.Id" could not be bound.
The multi-part identifier "u.AddressLine1" could not be bound.
The multi-part identifier "u.AddressLine2" could not be bound.

[smitpatel]

Possible duplicate of #26592

[ajcvickers]

/cc @roji

[tuggernuts]

I reported similar on #26592 here just before it closed. I was hoping it was fixed in 6.0.2. Is that the case, and when can we expect 6.0.2 to ship?

[roji]

Confirmed that with the latest daily build (7.0.0-alpha.1.22060.12), the error does not repro. So this indeed looks like a dup of #26592, which was indeed fixed for 6.0.2 (to be released in February).

[roji]

Duplicate of #26592