SQL generated not optimal when an entity contains owned types

[Vake93]

When querying data from an entity with owned types the SQL generated is with a self join. Since the data is in the same table couldn't this join be eliminated?

For example with the following models:

    [Owned]
    public class ReferralSubState
    {
        public ReferralSubStatus SubStatus { get; set; }

        public Guid? UpdateUserId { get; set; }

        public User UpdateUser { get; set; }

        public DateTime? UpdateDate { get; set; }
    }

    public class Referral
    {
        public Guid Id { get; set; }

        public Guid ReferredUserId { get; set; }

        public User ReferredUser { get; set; }

        public ReferralStatus Status { get; set; }

        public ReferralSubState Review { get; set; }

        public ReferralSubState Application { get; set; }

        public ReferralSubState Compliance { get; set; }
    }

If I use a simple LINQ as:

   var referrals = context.Referrals.FirstOrDefault();

The generated SQL is:

SELECT r."Id", r."ReferredUserId", r."Status", t."Id", t."Application_SubStatus", t."Application_UpdateDate", t."Application_UpdateUserId", t0."Id", t0."Compliance_SubStatus", t0."Compliance_UpdateDate", t0."Compliance_UpdateUserId", t1."Id", t1."Review_SubStatus", t1."Review_UpdateDate", t1."Review_UpdateUserId"
FROM "Referrals" AS r
LEFT JOIN (
    SELECT r0."Id", r0."Application_SubStatus", r0."Application_UpdateDate", r0."Application_UpdateUserId"
    FROM "Referrals" AS r0
    WHERE (r0."Application_SubStatus" IS NOT NULL)
) AS t ON r."Id" = t."Id"
LEFT JOIN (
    SELECT r1."Id", r1."Compliance_SubStatus", r1."Compliance_UpdateDate", r1."Compliance_UpdateUserId"
    FROM "Referrals" AS r1
    WHERE (r1."Compliance_SubStatus" IS NOT NULL)
) AS t0 ON r."Id" = t0."Id"
LEFT JOIN (
    SELECT r2."Id", r2."Review_SubStatus", r2."Review_UpdateDate", r2."Review_UpdateUserId"
    FROM "Referrals" AS r2
    WHERE (r2."Review_SubStatus" IS NOT NULL)
) AS t1 ON r."Id" = t1."Id"
LIMIT 1

Since the columns are in the same table couldn't the SQL be simplified select without using the self joins?

Also I have attached the zip file with a small project with this issue. You can check the debug window to see the SQL generated.

Test.zip

[Vake93]

Searching a bit more about this I found this GitHub issue. dotnet/efcore#18299

Its seems to be a EF core problem and not a specific problem for Npgsql.EntityFrameworkCore.PostgreSQL and wont be fixed untill .NET 5.

[YohDeadfall]

In that case am closing the issue.