{"action":"created","issue":{"url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/39","repository_url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus","labels_url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/39/labels{/name}","comments_url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/39/comments","events_url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/39/events","html_url":"https://github.com/zzzprojects/EntityFramework-Plus/issues/39","id":176415597,"number":39,"title":"BatchUpdate and BatchDelete errors if an .OrderBy(..) was included in query","user":{"login":"terryaney","id":6237247,"avatar_url":"https://avatars.githubusercontent.com/u/6237247?v=3","gravatar_id":"","url":"https://api.github.com/users/terryaney","html_url":"https://github.com/terryaney","followers_url":"https://api.github.com/users/terryaney/followers","following_url":"https://api.github.com/users/terryaney/following{/other_user}","gists_url":"https://api.github.com/users/terryaney/gists{/gist_id}","starred_url":"https://api.github.com/users/terryaney/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/terryaney/subscriptions","organizations_url":"https://api.github.com/users/terryaney/orgs","repos_url":"https://api.github.com/users/terryaney/repos","events_url":"https://api.github.com/users/terryaney/events{/privacy}","received_events_url":"https://api.github.com/users/terryaney/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":{"login":"zzzprojects","id":11062426,"avatar_url":"https://avatars.githubusercontent.com/u/11062426?v=3","gravatar_id":"","url":"https://api.github.com/users/zzzprojects","html_url":"https://github.com/zzzprojects","followers_url":"https://api.github.com/users/zzzprojects/followers","following_url":"https://api.github.com/users/zzzprojects/following{/other_user}","gists_url":"https://api.github.com/users/zzzprojects/gists{/gist_id}","starred_url":"https://api.github.com/users/zzzprojects/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zzzprojects/subscriptions","organizations_url":"https://api.github.com/users/zzzprojects/orgs","repos_url":"https://api.github.com/users/zzzprojects/repos","events_url":"https://api.github.com/users/zzzprojects/events{/privacy}","received_events_url":"https://api.github.com/users/zzzprojects/received_events","type":"User","site_admin":false},"assignees":[{"login":"zzzprojects","id":11062426,"avatar_url":"https://avatars.githubusercontent.com/u/11062426?v=3","gravatar_id":"","url":"https://api.github.com/users/zzzprojects","html_url":"https://github.com/zzzprojects","followers_url":"https://api.github.com/users/zzzprojects/followers","following_url":"https://api.github.com/users/zzzprojects/following{/other_user}","gists_url":"https://api.github.com/users/zzzprojects/gists{/gist_id}","starred_url":"https://api.github.com/users/zzzprojects/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zzzprojects/subscriptions","organizations_url":"https://api.github.com/users/zzzprojects/orgs","repos_url":"https://api.github.com/users/zzzprojects/repos","events_url":"https://api.github.com/users/zzzprojects/events{/privacy}","received_events_url":"https://api.github.com/users/zzzprojects/received_events","type":"User","site_admin":false}],"milestone":null,"comments":8,"created_at":"2016-09-12T16:04:51Z","updated_at":"2016-10-12T18:55:52Z","closed_at":"2016-10-02T17:46:30Z","body":"Sometimes, you might have a IQueryable that has an OrderBy specified in it.  As a simple example, given something like:\r\n\r\n```\r\nctx.Jobs.Where( j => j.DateCreated > EntityFunctions.AddDays( DateTime.Today, -1 ) && j.Package != \"null\" )\r\n\t\t.OrderBy( j => j.DateCreated )\r\n\t\t.Delete();\r\n```\r\n\r\nWhen attempted to run, you get the exception:\r\n\r\n> The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\n\r\nI've written a similar set of extensions as this library for Linq to Sql and to get around problem for me, I did some string searching/parsing.  Not sure if that is best solution, but something like the following would work in the BatchDelete.CreateCommand.\r\n\r\n```\r\nvar querySelect = query.ToTraceString();\r\n\r\nvar queryParts = querySelect.Split( '\\r' );\r\nvar selectClause = queryParts[ 0 ];\r\nvar needsTopClause = selectClause.IndexOf( \" TOP \" ) < 0 && querySelect.IndexOf( \" ORDER BY \" ) > 0;\r\n\r\nif ( needsTopClause )\r\n{\r\n\tquerySelect =\r\n\t\tstring.Join(\r\n\t\t\t\"\\r\",\r\n\t\t\tnew[] { selectClause.Replace( \"SELECT \", \"SELECT TOP 100 PERCENT \" ) }.Concat( queryParts.Skip( 1 ) )\r\n\t\t);\r\n}\r\n\r\n```\r\nI'm not sure if there is a more clever way to do it via Expression tree parsing or not.  Also, I'm not sure of the different variants the command text might be for different database providers.  Let me know if you want me to implement ( and something similar in BatchUpdate) and issue a pull request or if you think you'll have a 'better' way of fixing.\r\n\r\nThanks.\r\n"},"comment":{"url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/comments/253305053","html_url":"https://github.com/zzzprojects/EntityFramework-Plus/issues/39#issuecomment-253305053","issue_url":"https://api.github.com/repos/zzzprojects/EntityFramework-Plus/issues/39","id":253305053,"user":{"login":"terryaney","id":6237247,"avatar_url":"https://avatars.githubusercontent.com/u/6237247?v=3","gravatar_id":"","url":"https://api.github.com/users/terryaney","html_url":"https://github.com/terryaney","followers_url":"https://api.github.com/users/terryaney/followers","following_url":"https://api.github.com/users/terryaney/following{/other_user}","gists_url":"https://api.github.com/users/terryaney/gists{/gist_id}","starred_url":"https://api.github.com/users/terryaney/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/terryaney/subscriptions","organizations_url":"https://api.github.com/users/terryaney/orgs","repos_url":"https://api.github.com/users/terryaney/repos","events_url":"https://api.github.com/users/terryaney/events{/privacy}","received_events_url":"https://api.github.com/users/terryaney/received_events","type":"User","site_admin":false},"created_at":"2016-10-12T18:55:52Z","updated_at":"2016-10-12T18:55:52Z","body":"Sorry for the late response.  This does appear to work.  Interesting tho\r\n\r\n1) Shouldn't your if (visitor.HasOrderBy) check be if (visitor.HasOrderBy && !visitor.HasTake)?  Because if I have a Take, I think things would work as is.\r\n\r\n2) I see the Take( int.MaxValue )...how come I don't see anything about that in the generated SQL?  \r\n\r\nHere is the LINQ statement:\r\n```\r\n\tctx.Jobs.Where( j => j.DateCreated > DbFunctions.AddDays( DateTime.Today, -1 ) && j.Package != \"null\" )\r\n\t\t\t.OrderBy( j => j.DateCreated ).Where( j => j.Group != \"null\" )\r\n\t\t\t.Delete();\r\n```\r\n\r\nHere is the SQL generated:\r\n```\r\nDECLARE @rowAffected INT\r\nDECLARE @totalRowAffected INT\r\n\r\nSET @totalRowAffected = 0\r\n\r\nWHILE @rowAffected IS NULL\r\n    OR @rowAffected > 0\r\n    BEGIN\r\n        DELETE TOP (4000)\r\n        FROM    A\r\n        FROM    [dbo].[Job] AS A\r\n                INNER JOIN ( SELECT TOP (2147483647) \r\n    [Project1].[Key] AS [Key]\r\n    FROM ( SELECT \r\n        [Extent1].[Key] AS [Key], \r\n        [Extent1].[DateCreated] AS [DateCreated]\r\n        FROM [dbo].[Job] AS [Extent1]\r\n        WHERE ([Extent1].[DateCreated] > (DATEADD (day, -1, @p__linq__0))) AND (N'null' <> [Extent1].[Package]) AND (N'null' <> [Extent1].[Group])\r\n    )  AS [Project1]\r\n    ORDER BY [Project1].[DateCreated] ASC\r\n                           ) AS B ON A.[Key] = B.[Key]\r\n\r\n        SET @rowAffected = @@ROWCOUNT\r\n        SET @totalRowAffected = @totalRowAffected + @rowAffected\r\n    END\r\n\r\nSELECT  @totalRowAffected\r\n\r\n```"}}
{"actor":{"display_login":"terryaney"}}