Skip to content

Commit b507bec

Browse files
ThatRendleThatRendle
committed
1 parent 7203b26 commit b507bec

File tree

2 files changed

+103
-83
lines changed

2 files changed

+103
-83
lines changed

‎Simple.Data.SqlServer/SqlQueryPager.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ public class SqlQueryPager : IQueryPager
1313
{
1414
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(\sFROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
1515
private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*(DISTINCT)?", RegexOptions.IgnoreCase);
16+
private static readonly Regex LeftJoinMatch = new Regex(@"\sLEFT JOIN .*? ON \(.*?\)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
1617

1718
public IEnumerable<string> ApplyLimit(string sql, int take)
1819
{
@@ -34,7 +35,7 @@ public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int
3435

3536
builder.AppendFormat(", ROW_NUMBER() OVER({0}) AS [_#_]", orderBy);
3637
builder.AppendLine();
37-
builder.Append(fromEtc);
38+
builder.Append(LeftJoinMatch.Replace(fromEtc, ""));
3839
builder.AppendLine(")");
3940
builder.AppendFormat("SELECT {0} FROM __Data ", columns);
4041
builder.AppendFormat("JOIN {0} ON ",

‎Simple.Data.SqlTest/SqlQueryPagerTest.cs‎

Lines changed: 101 additions & 82 deletions
Original file line numberDiff line numberDiff line change
@@ -1,82 +1,82 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.Linq;
4-
using System.Text;
5-
using System.Text.RegularExpressions;
6-
using NUnit.Framework;
7-
using Simple.Data.SqlServer;
8-
9-
namespace Simple.Data.SqlTest
10-
{
11-
[TestFixture]
12-
public class SqlQueryPagerTest
13-
{
14-
static readonly Regex Normalize = new Regex(@"\s+", RegexOptions.Multiline);
15-
16-
[Test]
17-
public void ShouldApplyLimitUsingTop()
18-
{
19-
var sql = "select a,b,c from d where a = 1 order by c";
20-
var expected = new[] { "select top 5 a,b,c from d where a = 1 order by c" };
21-
22-
var pagedSql = new SqlQueryPager().ApplyLimit(sql, 5);
23-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
24-
25-
Assert.IsTrue(expected.SequenceEqual(modified));
26-
}
27-
28-
[Test]
29-
public void ShouldApplyLimitUsingTopWithDistinct()
30-
{
31-
var sql = "select distinct a,b,c from d where a = 1 order by c";
32-
var expected = new[] { "select distinct top 5 a,b,c from d where a = 1 order by c" };
33-
34-
var pagedSql = new SqlQueryPager().ApplyLimit(sql, 5);
35-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
36-
37-
Assert.IsTrue(expected.SequenceEqual(modified));
38-
}
39-
40-
[Test]
41-
public void ShouldApplyPagingUsingOrderBy()
42-
{
43-
var sql = "select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1 order by [dbo].[d].[c]";
44-
var expected = new[]{
45-
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[c]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
46-
+ " select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 6 and 15"};
47-
48-
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[d].[a]"}, 5, 10);
49-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
50-
51-
Assert.AreEqual(expected[0], modified[0]);
52-
}
53-
54-
[Test]
55-
public void ShouldApplyPagingUsingOrderByKeysIfNotAlreadyOrdered()
56-
{
57-
var sql = "select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
58-
var expected = new[]{
59-
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
60-
+ " select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 11 and 30"};
61-
62-
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[d].[a]"}, 10, 20);
63-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
64-
65-
Assert.AreEqual(expected[0], modified[0]);
66-
}
67-
68-
[Test]
69-
public void ShouldCopeWithAliasedColumns()
70-
{
71-
var sql = "select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
72-
var expected =new[]{
73-
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
74-
+ " select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 21 and 25"};
75-
76-
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[]{"[dbo].[d].[a]"}, 20, 5);
77-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
78-
79-
Assert.AreEqual(expected[0], modified[0]);
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
using System.Text.RegularExpressions;
6+
using NUnit.Framework;
7+
using Simple.Data.SqlServer;
8+
9+
namespace Simple.Data.SqlTest
10+
{
11+
[TestFixture]
12+
public class SqlQueryPagerTest
13+
{
14+
static readonly Regex Normalize = new Regex(@"\s+", RegexOptions.Multiline);
15+
16+
[Test]
17+
public void ShouldApplyLimitUsingTop()
18+
{
19+
var sql = "select a,b,c from d where a = 1 order by c";
20+
var expected = new[] { "select top 5 a,b,c from d where a = 1 order by c" };
21+
22+
var pagedSql = new SqlQueryPager().ApplyLimit(sql, 5);
23+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
24+
25+
Assert.IsTrue(expected.SequenceEqual(modified));
26+
}
27+
28+
[Test]
29+
public void ShouldApplyLimitUsingTopWithDistinct()
30+
{
31+
var sql = "select distinct a,b,c from d where a = 1 order by c";
32+
var expected = new[] { "select distinct top 5 a,b,c from d where a = 1 order by c" };
33+
34+
var pagedSql = new SqlQueryPager().ApplyLimit(sql, 5);
35+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
36+
37+
Assert.IsTrue(expected.SequenceEqual(modified));
38+
}
39+
40+
[Test]
41+
public void ShouldApplyPagingUsingOrderBy()
42+
{
43+
var sql = "select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1 order by [dbo].[d].[c]";
44+
var expected = new[]{
45+
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[c]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
46+
+ " select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 6 and 15"};
47+
48+
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[d].[a]"}, 5, 10);
49+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
50+
51+
Assert.AreEqual(expected[0], modified[0]);
52+
}
53+
54+
[Test]
55+
public void ShouldApplyPagingUsingOrderByKeysIfNotAlreadyOrdered()
56+
{
57+
var sql = "select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
58+
var expected = new[]{
59+
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
60+
+ " select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 11 and 30"};
61+
62+
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[d].[a]"}, 10, 20);
63+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
64+
65+
Assert.AreEqual(expected[0], modified[0]);
66+
}
67+
68+
[Test]
69+
public void ShouldCopeWithAliasedColumns()
70+
{
71+
var sql = "select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
72+
var expected =new[]{
73+
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
74+
+ " select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 21 and 25"};
75+
76+
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[]{"[dbo].[d].[a]"}, 20, 5);
77+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
78+
79+
Assert.AreEqual(expected[0], modified[0]);
8080
}
8181

8282
[Test]
@@ -92,6 +92,25 @@ from [dbo].[PromoPosts]
9292
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[PromoPosts].[Id]"}, 0, 25).Single();
9393
var modified = Normalize.Replace(pagedSql, " ").ToLowerInvariant();
9494
Assert.AreEqual(expected, modified);
95-
}
96-
}
97-
}
95+
}
96+
97+
[Test]
98+
public void ShouldExcludeLeftJoinedTablesFromSubSelect()
99+
{
100+
const string sql = @"SELECT [dbo].[MainClass].[ID],
101+
[dbo].[MainClass].[SomeProperty],
102+
[dbo].[MainClass].[SomeProperty2],
103+
[dbo].[MainClass].[SomeProperty3],
104+
[dbo].[MainClass].[SomeProperty4],
105+
[dbo].[ChildClass].[ID] AS [__withn__ChildClass__ID],
106+
[dbo].[ChildClass].[SomeProperty] AS [__withn__ChildClass__SomeProperty],
107+
[dbo].[ChildClass].[SomeProperty2] AS [__withn__ChildClass__SomeProperty2] FROM [dbo].[MainClass] LEFT JOIN [dbo].[JoinTable] ON ([dbo].[MainClass].[ID] = [dbo].[JoinTable].[MainClassID]) LEFT JOIN [dbo].[ChildClass] ON ([dbo].[ChildClass].[ID] = [dbo].[JoinTable].[ChildClassID]) WHERE ([dbo].[MainClass].[SomeProperty] > @p1 AND [dbo].[MainClass].[SomeProperty] <= @p2)";
108+
109+
const string expected = @"with __data as (select [dbo].[promoposts].[id], row_number() over(order by [dbo].[promoposts].[id]) as [_#_] from [dbo].[mainclass] where ([dbo].[mainclass].[someproperty] > @p1 and [dbo].[mainclass].[someproperty] <= @p2)) select [dbo].[mainclass].[id], [dbo].[mainclass].[someproperty], [dbo].[mainclass].[someproperty2], [dbo].[mainclass].[someproperty3], [dbo].[mainclass].[someproperty4], [dbo].[childclass].[id] as [__withn__childclass__id], [dbo].[childclass].[someproperty] as [__withn__childclass__someproperty], [dbo].[childclass].[someproperty2] as [__withn__childclass__someproperty2] from __data join [dbo].[promoposts] on [dbo].[promoposts].[id] = __data.[id]from [dbo].[mainclass] left join [dbo].[jointable] on ([dbo].[mainclass].[id] = [dbo].[jointable].[mainclassid]) left join [dbo].[childclass] on ([dbo].[childclass].[id] = [dbo].[jointable].[childclassid]) where ([dbo].[mainclass].[someproperty] > @p1 and [dbo].[mainclass].[someproperty] <= @p2) and [_#_] between 1 and 25";
110+
111+
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[PromoPosts].[Id]"}, 0, 25).Single();
112+
var modified = Normalize.Replace(pagedSql, " ").ToLowerInvariant();
113+
Assert.AreEqual(expected, modified);
114+
}
115+
}
116+
}

0 commit comments

Comments
 (0)