{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6703","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6703/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6703/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6703/events","html_url":"https://github.com/aspnet/EntityFramework/issues/6703","id":181543357,"number":6703,"title":"Query: Alias subquery if being used in order by clause","user":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"labels":[{"id":76481854,"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/type-enhancement","name":"type-enhancement","color":"bfe5bf","default":false}],"state":"open","locked":false,"assignee":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"assignees":[{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/24","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.2.0","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/24/labels","id":2044943,"number":24,"title":"1.2.0","description":null,"creator":{"login":"rowanmiller","id":4741851,"avatar_url":"https://avatars.githubusercontent.com/u/4741851?v=3","gravatar_id":"","url":"https://api.github.com/users/rowanmiller","html_url":"https://github.com/rowanmiller","followers_url":"https://api.github.com/users/rowanmiller/followers","following_url":"https://api.github.com/users/rowanmiller/following{/other_user}","gists_url":"https://api.github.com/users/rowanmiller/gists{/gist_id}","starred_url":"https://api.github.com/users/rowanmiller/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rowanmiller/subscriptions","organizations_url":"https://api.github.com/users/rowanmiller/orgs","repos_url":"https://api.github.com/users/rowanmiller/repos","events_url":"https://api.github.com/users/rowanmiller/events{/privacy}","received_events_url":"https://api.github.com/users/rowanmiller/received_events","type":"User","site_admin":false},"open_issues":149,"closed_issues":56,"state":"open","created_at":"2016-10-03T18:11:14Z","updated_at":"2016-12-05T23:33:31Z","due_on":null,"closed_at":null},"comments":2,"created_at":"2016-10-06T22:40:12Z","updated_at":"2016-12-06T01:11:09Z","closed_at":null,"body":"For query like this\n\n``` C#\nselection = db.Nodes.Include(x => x.NodeRecords)\n        .Select(s => new HighNode()\n        {\n            Name = s.Name,\n            Change = s.NodeRecords.OrderByDescending(o => o.NodeRecordId).Skip(days).FirstOrDefault().Change\n        }).OrderByDescending(t => t.Change).Take(100).ToList();\n```\n\nWe generate Sql like this\n\n``` SQL\nSELECT TOP(@__p_1) [x].[Name], (\n    SELECT [o0].[Change]\n    FROM [NodeRecords] AS [o0]\n    WHERE [x].[Name] = [o0].[Node_Name]\n    ORDER BY [o0].[NodeRecordId] DESC\n    OFFSET @__days_0 ROWS FETCH NEXT 1 ROWS ONLY\n)\nFROM [Nodes] AS [x]\nORDER BY (\n    SELECT [o].[Change]\n    FROM [NodeRecords] AS [o]\n    WHERE [x].[Name] = [o].[Node_Name]\n    ORDER BY [o].[NodeRecordId] DESC\n    OFFSET @__days_0 ROWS FETCH NEXT 1 ROWS ONLY\n) DESC\n```\n\nWe can just alias the subquery in projection and use alias in order by clause as enhancement.\n\nFor model and more info see #6257 \n"},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/comments/265030538","html_url":"https://github.com/aspnet/EntityFramework/issues/6703#issuecomment-265030538","issue_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6703","id":265030538,"user":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"created_at":"2016-12-06T01:11:09Z","updated_at":"2016-12-06T01:11:09Z","body":"This is a necessity for the case of coalesce when it is inside a subquery because we need to copy order by on outer query but the columns for coalesce are not projected out hence generates invalid query like this.\r\n```\r\nexec sp_executesql N'SELECT [t].*\r\nFROM (\r\n    SELECT TOP(@__p_0) [c].[CustomerID], [c].[CompanyName], COALESCE([c].[Region], N''ZZ'') AS [c0]\r\n    FROM [Customers] AS [c]\r\n    ORDER BY COALESCE([c].[Region], N''ZZ'')\r\n) AS [t]\r\nORDER BY COALESCE([t].[Region], N''ZZ'')\r\nOFFSET @__p_1 ROWS',N'@__p_0 int,@__p_1 int',@__p_0=10,@__p_1=5\r\n```"}}
{"actor":{"display_login":"smitpatel"}}