Fixes issue ThatRendle#250

ThatRendle · ThatRendle · commit 98e303e0f453 · 2013-05-21T12:27:11.000+01:00
diff --git a/Simple.Data.SqlServer/SqlQueryPager.cs b/Simple.Data.SqlServer/SqlQueryPager.cs
@@ -11,16 +11,17 @@ namespace Simple.Data.SqlServer
     [Export(typeof(IQueryPager))]
     public class SqlQueryPager : IQueryPager
     {
-        private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
+        private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(\sFROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
         private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*(DISTINCT)?", RegexOptions.IgnoreCase);
 
         public IEnumerable<string> ApplyLimit(string sql, int take)
         {
             yield return SelectMatch.Replace(sql, match => match.Value + " TOP " + take + " ");
         }
 
-        public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
-        {
+        public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
+        {
+            sql = sql.Replace(Environment.NewLine, " ");
             var builder = new StringBuilder("WITH __Data AS (SELECT ");
 
             var match = ColumnExtract.Match(sql);
diff --git a/Simple.Data.SqlTest/SqlQueryPagerTest.cs b/Simple.Data.SqlTest/SqlQueryPagerTest.cs
@@ -77,6 +77,21 @@ public void ShouldCopeWithAliasedColumns()
             var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
 
             Assert.AreEqual(expected[0], modified[0]);
+        }
+
+        [Test]
+        public void ShouldCopeWithColumnsThatEndInFrom()
+        {
+            const string sql = @"SELECT [dbo].[PromoPosts].[Id],[dbo].[PromoPosts].[ActiveFrom],[dbo].[PromoPosts].[ActiveTo],[dbo].[PromoPosts].[Created],[dbo].[PromoPosts].[Updated] 
+    from [dbo].[PromoPosts] 
+    ORDER BY [dbo].[PromoPosts].[ActiveFrom]";
+
+            var expected = @"WITH __Data AS (SELECT [dbo].[PromoPosts].[Id],[dbo].[PromoPosts].[ActiveFrom],[dbo].[PromoPosts].[ActiveTo],[dbo].[PromoPosts].[Created],[dbo].[PromoPosts].[Updated],ROW_NUMBER() OVER(ORDER BY [dbo].[PromoPosts].[ActiveFrom]) AS [_#_] from [dbo].[PromoPosts]) SELECT [Id],[ActiveFrom],[ActiveTo],[Created],[Updated] FROM __Data WHERE [_#_] BETWEEN 1 AND 25";
+            expected = expected.ToLowerInvariant();
+
+            var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[PromoPosts].[Id]"}, 0, 25).Single();
+            var modified = Normalize.Replace(pagedSql, " ").ToLowerInvariant();
+            Assert.AreEqual(expected, modified);
         }
     }
 }

Original file line number	Diff line number	Diff line change
`@@ -11,16 +11,17 @@ namespace Simple.Data.SqlServer`
`11`	`11`	`[Export(typeof(IQueryPager))]`
`12`	`12`	`public class SqlQueryPager : IQueryPager`
`13`	`13`	`{`
`14`		`- private static readonly Regex ColumnExtract = new Regex(@"SELECT\s(.)\s(FROM.)", RegexOptions.Multiline \| RegexOptions.IgnoreCase);`
	`14`	`+ private static readonly Regex ColumnExtract = new Regex(@"SELECT\s(.)\s(\sFROM.)", RegexOptions.Multiline \| RegexOptions.IgnoreCase);`
`15`	`15`	`private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*(DISTINCT)?", RegexOptions.IgnoreCase);`
`16`	`16`
`17`	`17`	`public IEnumerable<string> ApplyLimit(string sql, int take)`
`18`	`18`	`{`
`19`	`19`	`yield return SelectMatch.Replace(sql, match => match.Value + " TOP " + take + " ");`
`20`	`20`	`}`
`21`	`21`
`22`		`- public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)`
`23`		`- {`
	`22`	`+ public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)`
	`23`	`+ {`
	`24`	`+ sql = sql.Replace(Environment.NewLine, " ");`
`24`	`25`	`var builder = new StringBuilder("WITH __Data AS (SELECT ");`
`25`	`26`
`26`	`27`	`var match = ColumnExtract.Match(sql);`