{"action":"created","issue":{"url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/365","labels_url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/365/labels{/name}","comments_url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/365/comments","events_url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/365/events","html_url":"https://github.com/igor-tkachev/bltoolkit/issues/365","id":82666327,"number":365,"title":"OFFSET used by mistake for SQL Server 2012.","user":{"login":"maxim-boligatov","id":4306334,"avatar_url":"https://avatars.githubusercontent.com/u/4306334?v=3","gravatar_id":"","url":"https://api.github.com/users/maxim-boligatov","html_url":"https://github.com/maxim-boligatov","followers_url":"https://api.github.com/users/maxim-boligatov/followers","following_url":"https://api.github.com/users/maxim-boligatov/following{/other_user}","gists_url":"https://api.github.com/users/maxim-boligatov/gists{/gist_id}","starred_url":"https://api.github.com/users/maxim-boligatov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxim-boligatov/subscriptions","organizations_url":"https://api.github.com/users/maxim-boligatov/orgs","repos_url":"https://api.github.com/users/maxim-boligatov/repos","events_url":"https://api.github.com/users/maxim-boligatov/events{/privacy}","received_events_url":"https://api.github.com/users/maxim-boligatov/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-05-30T02:15:43Z","updated_at":"2015-06-22T11:44:53Z","closed_at":null,"body":"The following SQL query is not valid from point of view of syntax:\r\n```\r\nSELECT DISTINCT ColumnA, ColumnB, ColumnC\r\nFROM SomeTable\r\nORDER BY ColumnD, ColumnC\r\nOFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY \r\n```\r\nbecause ColumnD is not in distinct select list.\r\n\r\nBut such pagination (Skip and Take) and ordering (OrderBy) are valid and possible.\r\nThis issue is addressed only to OFFSET syntax available in SQL Server 2012 (and some others).\r\n\r\nMsSql2008SqlProvider works fine because BuildAlternativeSql property is set to true.\r\nTrue for that property will force using of AlternativeBuildSql method.\r\nSo this method is used to create another version of select query with row number.\r\n\"ROW_NUMBER() OVER\" syntax allows ordering with DISTINCT queries and pagination works fine.\r\nOf course row number is slower than offset. So we need to create some solution to consider both methods.\r\n\r\nPotential way to do it is overriding of the following method for MsSql2012SqlProvider:\r\n```\r\nprotected override void BuildSql(StringBuilder sb)\r\n\t\t{\r\n\t\t\tif (BuildAlternativeSql)\r\n\t\t\t\tAlternativeBuildSql(sb, true, base.BuildSql);\r\n\t\t\telse\r\n\t\t\t\tbase.BuildSql(sb);\r\n\t\t}\r\n```\r\nThere we need to detect distinct query and compare columns in select and in order by.\r\nBut I am not sure how it is better to do... project structure. Maybe somebody who is more experienced in the project structure can suggest detection logic or refactoring of BuildAlternativeSql property.\r\n\r\nFast temp fix is related to disabling of OFFSET syntax. There are 2 ways:\r\n1) Return \"true\" instead of \"false\":\r\n```\r\nprotected override bool   BuildAlternativeSql { get { return false;             } }\r\n```\r\n2) Change database compatibility version to 100 (SQL Server 2008):\r\n```\r\nALTER DATABASE [My Database] SET COMPATIBILITY_LEVEL = 100\r\n```\r\nIn 2nd case SqlDataProviderVersionResolver will instantiate 2008 version of sql provider.\r\n"},"comment":{"url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/comments/114075928","html_url":"https://github.com/igor-tkachev/bltoolkit/issues/365#issuecomment-114075928","issue_url":"https://api.github.com/repos/igor-tkachev/bltoolkit/issues/365","id":114075928,"user":{"login":"maxim-boligatov","id":4306334,"avatar_url":"https://avatars.githubusercontent.com/u/4306334?v=3","gravatar_id":"","url":"https://api.github.com/users/maxim-boligatov","html_url":"https://github.com/maxim-boligatov","followers_url":"https://api.github.com/users/maxim-boligatov/followers","following_url":"https://api.github.com/users/maxim-boligatov/following{/other_user}","gists_url":"https://api.github.com/users/maxim-boligatov/gists{/gist_id}","starred_url":"https://api.github.com/users/maxim-boligatov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxim-boligatov/subscriptions","organizations_url":"https://api.github.com/users/maxim-boligatov/orgs","repos_url":"https://api.github.com/users/maxim-boligatov/repos","events_url":"https://api.github.com/users/maxim-boligatov/events{/privacy}","received_events_url":"https://api.github.com/users/maxim-boligatov/received_events","type":"User","site_admin":false},"created_at":"2015-06-22T11:44:53Z","updated_at":"2015-06-22T11:44:53Z","body":"SQL Server demands to include all columns from ORDER BY to SELECT DISTINCT clause. But LINQ expressions can be made in the way when we do not need to include it. In my example we need to sort items by ColumnD and after that perform distinct selection of top 50 rows using columns not related to ColumnD. From point of view of Linq experssion it is possible and it works if ROW_NUMBER syntax is used. I guess we need to switch to ROW_NUMBER instead of OFFSET **only** for queries with DISTINCT, ORDER BY and pagination at the same time and **only** in case when select and order by columns are different in expression. In all other cases we can use OFFSET because it works a bit faster."}}