{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12602","repository_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore","labels_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12602/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12602/comments","events_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12602/events","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/12602","id":339120260,"node_id":"MDU6SXNzdWUzMzkxMjAyNjA=","number":12602,"title":"Single-record subquery translates to a second SQL query.","user":{"login":"JakenVeina","id":26645431,"node_id":"MDQ6VXNlcjI2NjQ1NDMx","avatar_url":"https://avatars2.githubusercontent.com/u/26645431?v=4","gravatar_id":"","url":"https://api.github.com/users/JakenVeina","html_url":"https://github.com/JakenVeina","followers_url":"https://api.github.com/users/JakenVeina/followers","following_url":"https://api.github.com/users/JakenVeina/following{/other_user}","gists_url":"https://api.github.com/users/JakenVeina/gists{/gist_id}","starred_url":"https://api.github.com/users/JakenVeina/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JakenVeina/subscriptions","organizations_url":"https://api.github.com/users/JakenVeina/orgs","repos_url":"https://api.github.com/users/JakenVeina/repos","events_url":"https://api.github.com/users/JakenVeina/events{/privacy}","received_events_url":"https://api.github.com/users/JakenVeina/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-07-07T06:00:01Z","updated_at":"2018-07-08T03:40:53Z","closed_at":"2018-07-08T03:40:53Z","author_association":"NONE","body":"Using Microsoft.EntityFrameworkCore (2.1.0) with Npgsql.EntityFrameworkCore.PostgreSQL (2.1.0)\r\n\r\nFor EF query...\r\n\r\n```csharp\r\npublic class InfractionEntity\r\n{\r\n    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]\r\n    public long Id { get; set; }\r\n\r\n    [Required]\r\n    public InfractionType Type { get; set; }\r\n\r\n    [Required, ForeignKey(nameof(Subject))]\r\n    public long SubjectId { get; set; }\r\n\r\n    [Required]\r\n    public DiscordUserEntity Subject { get; set; }\r\n\r\n    public virtual ICollection<ModerationActionEntity> ModerationActions { get; set; }\r\n\r\n    public TimeSpan? Duration { get; set; }\r\n}\r\n\r\npublic class ModerationActionEntity\r\n{\r\n    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]\r\n    public long Id { get; set; }\r\n\r\n    [Required]\r\n    public ModerationActionType Type { get; set; }\r\n        \r\n    [ForeignKey(nameof(Infraction))]\r\n    public long? InfractionId { get; set; }\r\n\r\n    public virtual InfractionEntity Infraction { get; set; }\r\n\r\n    [Required]\r\n    public DateTimeOffset Created { get; set; }\r\n\r\n    [Required, ForeignKey(nameof(CreatedBy))]\r\n    public long CreatedById { get; set; }\r\n\r\n    [Required]\r\n    public virtual DiscordUserEntity CreatedBy { get; set; }\r\n\r\n    [Required]\r\n    public string Reason { get; set; }\r\n}\r\n\r\nvar results = await context.Infractions.AsNoTracking()\r\n    .Select(x => new\r\n    {\r\n        Infraction = x,\r\n        Created = x.ModerationActions.FirstOrDefault(y => y.Type == ModerationActionType.InfractionCreated).Created\r\n    })\r\n    .ToArrayAsync();\r\n```\r\n\r\n...I would expect this to generate...\r\n\r\n```\r\nSELECT x.\"Id\", x.\"Duration\", x.\"SubjectId\", x.\"Type\",\r\n    (SELECT y.\"Created\"\r\n    FROM \"ModerationActions\" AS y\r\n    WHERE (y.\"Type\" = 0) AND (x.\"Id\" = y.\"InfractionId\")) AS \"Created\"\r\nFROM \"Infractions\" AS x\r\n```\r\n\r\n... or equivalent. However, I'm getting...\r\n\r\n```\r\nSELECT x.\"Id\", x.\"Duration\", x.\"SubjectId\", x.\"Type\"\r\nFROM \"Infractions\" AS x\r\n\r\nSELECT y.\"Id\", y.\"Created\", y.\"CreatedById\", y.\"InfractionId\", y.\"Reason\", y.\"Type\"\r\nFROM \"ModerationActions\" AS y\r\nWHERE (y.\"Type\" = 0) AND (@_outer_Id = y.\"InfractionId\")\r\nLIMIT 1\r\n```\r\n\r\n...Similarly, I would expect...\r\n\r\n```csharp\r\nvar results = await context.Infractions.AsNoTracking()\r\n    .Select(x => new\r\n    {\r\n        Infraction = x,\r\n        CreateAction = x.ModerationActions.FirstOrDefault(y => y.Type == ModerationActionType.InfractionCreated)\r\n    })\r\n    .ToArrayAsync();\r\n```\r\n\r\n...to translate to something like...\r\n\r\n```sql\r\nSELECT x.\"Id\", x.\"Duration\", x.\"SubjectId\", x.\"Type\", y.\"Id\", y.\"Created\", y.\"CreatedById\", y.\"InfractionId\", y.\"Reason\", y.\"Type\"\r\nFROM \"Infractions\" AS x\r\nLEFT JOIN \"ModerationActions\" AS y\r\n    ON y.\"InfractionId\" = x.\"Id\" and y.\"Type\" = 0\r\n```\r\n\r\nIs 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?"},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/comments/403259853","html_url":"https://github.com/aspnet/EntityFrameworkCore/issues/12602#issuecomment-403259853","issue_url":"https://api.github.com/repos/aspnet/EntityFrameworkCore/issues/12602","id":403259853,"node_id":"MDEyOklzc3VlQ29tbWVudDQwMzI1OTg1Mw==","user":{"login":"maumar","id":1591930,"node_id":"MDQ6VXNlcjE1OTE5MzA=","avatar_url":"https://avatars3.githubusercontent.com/u/1591930?v=4","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false},"created_at":"2018-07-08T03:40:53Z","updated_at":"2018-07-08T03:40:53Z","author_association":"CONTRIBUTOR","body":"This is a duplicate of https://github.com/aspnet/EntityFrameworkCore/issues/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.\r\n\r\nThe last transformation is tracked by https://github.com/aspnet/EntityFrameworkCore/issues/10001"}}
{"actor":{"display_login":"maumar"}}