Single-record subquery translates to a second SQL query.

[JakenVeina]

Using Microsoft.EntityFrameworkCore (2.1.0) with Npgsql.EntityFrameworkCore.PostgreSQL (2.1.0)

For EF query...

public class InfractionEntity
{
    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Required]
    public InfractionType Type { get; set; }

    [Required, ForeignKey(nameof(Subject))]
    public long SubjectId { get; set; }

    [Required]
    public DiscordUserEntity Subject { get; set; }

    public virtual ICollection<ModerationActionEntity> ModerationActions { get; set; }

    public TimeSpan? Duration { get; set; }
}

public class ModerationActionEntity
{
    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Required]
    public ModerationActionType Type { get; set; }
        
    [ForeignKey(nameof(Infraction))]
    public long? InfractionId { get; set; }

    public virtual InfractionEntity Infraction { get; set; }

    [Required]
    public DateTimeOffset Created { get; set; }

    [Required, ForeignKey(nameof(CreatedBy))]
    public long CreatedById { get; set; }

    [Required]
    public virtual DiscordUserEntity CreatedBy { get; set; }

    [Required]
    public string Reason { get; set; }
}

var results = await context.Infractions.AsNoTracking()
    .Select(x => new
    {
        Infraction = x,
        Created = x.ModerationActions.FirstOrDefault(y => y.Type == ModerationActionType.InfractionCreated).Created
    })
    .ToArrayAsync();

...I would expect this to generate...

SELECT x."Id", x."Duration", x."SubjectId", x."Type",
    (SELECT y."Created"
    FROM "ModerationActions" AS y
    WHERE (y."Type" = 0) AND (x."Id" = y."InfractionId")) AS "Created"
FROM "Infractions" AS x

... or equivalent. However, I'm getting...

SELECT x."Id", x."Duration", x."SubjectId", x."Type"
FROM "Infractions" AS x

SELECT y."Id", y."Created", y."CreatedById", y."InfractionId", y."Reason", y."Type"
FROM "ModerationActions" AS y
WHERE (y."Type" = 0) AND (@_outer_Id = y."InfractionId")
LIMIT 1

...Similarly, I would expect...

var results = await context.Infractions.AsNoTracking()
    .Select(x => new
    {
        Infraction = x,
        CreateAction = x.ModerationActions.FirstOrDefault(y => y.Type == ModerationActionType.InfractionCreated)
    })
    .ToArrayAsync();

...to translate to something like...

SELECT x."Id", x."Duration", x."SubjectId", x."Type", y."Id", y."Created", y."CreatedById", y."InfractionId", y."Reason", y."Type"
FROM "Infractions" AS x
LEFT JOIN "ModerationActions" AS y
    ON y."InfractionId" = x."Id" and y."Type" = 0

Is this related to #11677 and #11186, or do I just not know what I'm doing? Is this just something that hasn't yet been implemented in Core?

[maumar]

This is a duplicate of #11186 - when projecting Created property by itself (without the Infraction entity) we correctly produce a subquery in this case. However presence of the entity blocks the translation.

The last transformation is tracked by #10001

[Looooooka]

Ok where is this issue tracked currently.
I keep seing "closed" and linked to a duplicate and then ending up on the same issues.
The fact is queries in EF Core are producing duplicate unnecessary queries when trying to fetch related data.
doing joins and left joins with defaultifempty into a group so you end up with 5000 lines of code only to realize it throws other errors out.
When is one going to be able to select (m from x where m=blabla select new { m.y, m.relatedRecords.Select(cr=>cr.SomeData).FirstOrDefault()).Skip(10).Take(10)
in a single query.
It's like I went back to the stoneage since switching to EF Core. Ridiculous.