Skip to content

Commit 7f789e4

Browse files
ThatRendleThatRendle
committed
Avoid SELECT N+1
1 parent 31f1f46 commit 7f789e4

7 files changed

Lines changed: 238 additions & 164 deletions

File tree

‎Simple.Data.Ado/Joiner.cs‎

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,8 @@ class Joiner
1414
private readonly DatabaseSchema _schema;
1515
private readonly ConcurrentDictionary<ObjectName, string> _done = new ConcurrentDictionary<ObjectName, string>();
1616

17-
public Joiner(DatabaseSchema schema) : this(JoinType.Outer, schema)
17+
public Joiner(DatabaseSchema schema)
18+
: this(JoinType.Outer, schema)
1819
{
1920
}
2021

@@ -89,19 +90,19 @@ private string JoinKeyword
8990
get { return _joinType == JoinType.Inner ? string.Empty : "LEFT"; }
9091
}
9192

92-
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(IEnumerable<ObjectReference> references, string schema)
93+
private static IEnumerable<Tuple<ObjectName, ObjectName>> GetTableNames(IEnumerable<ObjectReference> references, string schema)
9394
{
9495
return references.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))
9596
.TupleSelect((table1, table2) => Tuple.Create(new ObjectName(schema, table1), new ObjectName(schema, table2)))
9697
.Distinct();
9798
}
98-
99-
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(SimpleExpression expression, string schema)
99+
100+
private static IEnumerable<Tuple<ObjectName, ObjectName>> GetTableNames(SimpleExpression expression, string schema)
100101
{
101102
return expression == null ? Enumerable.Empty<Tuple<ObjectName, ObjectName>>() : GetTableNames(GetReferencesFromExpression(expression), schema);
102103
}
103104

104-
private static IEnumerable<Tuple<string,string>> DynamicReferenceToTuplePairs(ObjectReference reference, string schema)
105+
private static IEnumerable<Tuple<string, string>> DynamicReferenceToTuplePairs(ObjectReference reference, string schema)
105106
{
106107
return reference.GetAllObjectNames()
107108
.SkipWhile(s => s.Equals(schema, StringComparison.OrdinalIgnoreCase))
@@ -113,15 +114,15 @@ private static IEnumerable<ObjectReference> GetReferencesFromExpression(SimpleEx
113114
{
114115
if (expression.Type == SimpleExpressionType.And || expression.Type == SimpleExpressionType.Or)
115116
{
116-
return GetReferencesFromExpression((SimpleExpression) expression.LeftOperand)
117-
.Concat(GetReferencesFromExpression((SimpleExpression) expression.LeftOperand));
117+
return GetReferencesFromExpression((SimpleExpression)expression.LeftOperand)
118+
.Concat(GetReferencesFromExpression((SimpleExpression)expression.LeftOperand));
118119
}
119120

120121
var result = Enumerable.Empty<ObjectReference>();
121122

122123
if (expression.LeftOperand is ObjectReference)
123124
{
124-
result = result.Concat(new[] {(ObjectReference) expression.LeftOperand});
125+
result = result.Concat(new[] { (ObjectReference)expression.LeftOperand });
125126
}
126127
if (expression.RightOperand is ObjectReference)
127128
{
@@ -131,4 +132,4 @@ private static IEnumerable<ObjectReference> GetReferencesFromExpression(SimpleEx
131132
return result;
132133
}
133134
}
134-
}
135+
}

‎Simple.Data.BehaviourTest/QueryTest.cs‎

Lines changed: 170 additions & 155 deletions
Original file line numberDiff line numberDiff line change
@@ -1,155 +1,155 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.Linq;
4-
using System.Text;
5-
using NUnit.Framework;
6-
using Simple.Data.Mocking.Ado;
7-
8-
namespace Simple.Data.IntegrationTest
9-
{
10-
[TestFixture]
11-
public class QueryTest : DatabaseIntegrationContext
12-
{
13-
protected override void SetSchema(MockSchemaProvider schemaProvider)
14-
{
15-
schemaProvider.SetTables(new[] {"dbo", "Users", "BASE TABLE"},
16-
new[] {"dbo", "UserBio", "BASE TABLE"});
17-
18-
schemaProvider.SetColumns(new object[] {"dbo", "Users", "Id", true},
19-
new[] {"dbo", "Users", "Name"},
20-
new[] {"dbo", "Users", "Password"},
21-
new[] {"dbo", "Users", "Age"},
22-
new[] {"dbo", "UserBio", "UserId"},
23-
new[] {"dbo", "UserBio", "Text"});
24-
25-
schemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 });
26-
schemaProvider.SetForeignKeys(new object[] { "FK_Users_UserBio", "dbo", "UserBio", "UserId", "dbo", "Users", "Id", 0 });
27-
}
28-
29-
[Test]
30-
public void SpecifyingColumnsShouldRestrictSelect()
31-
{
32-
_db.Users.All()
33-
.Select(_db.Users.Name, _db.Users.Password)
34-
.ToList();
35-
GeneratedSqlIs("select [dbo].[users].[name],[dbo].[users].[password] from [dbo].[users]");
36-
}
37-
38-
[Test]
39-
public void SpecifyingColumnWithAliasShouldAddAsClause()
40-
{
41-
_db.Users.All()
42-
.Select(_db.Users.Name, _db.Users.Password.As("SuperSecretPassword"))
43-
.ToList();
44-
GeneratedSqlIs("select [dbo].[users].[name],[dbo].[users].[password] as [supersecretpassword] from [dbo].[users]");
45-
}
46-
47-
[Test]
48-
public void SpecifyingColumnsFromOtherTablesShouldAddJoin()
49-
{
50-
_db.Users.All()
51-
.Select(_db.Users.Name, _db.Users.Password, _db.Users.UserBio.Text)
52-
.ToList();
53-
GeneratedSqlIs(
54-
"select [dbo].[users].[name],[dbo].[users].[password],[dbo].[userbio].[text] from [dbo].[users]" +
55-
" join [dbo].[userbio] on ([dbo].[users].[id] = [dbo].[userbio].[userid])");
56-
}
57-
58-
[Test]
59-
public void SpecifyingCountShouldSelectCount()
60-
{
61-
try
62-
{
63-
_db.Users.All().Count();
64-
}
65-
catch (InvalidOperationException)
66-
{
67-
// This won't work on Mock provider, but the SQL should be generated OK
68-
}
69-
70-
GeneratedSqlIs("select count(*) from [dbo].[users]");
71-
}
72-
73-
[Test]
74-
public void SpecifyingExistsShouldSelectDistinct1()
75-
{
76-
try
77-
{
78-
_db.Users.All().Exists();
79-
}
80-
catch (InvalidOperationException)
81-
{
82-
// This won't work on Mock provider, but the SQL should be generated OK
83-
}
84-
85-
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
86-
}
87-
88-
[Test]
89-
public void SpecifyingAnyShouldSelectDistinct1()
90-
{
91-
try
92-
{
93-
_db.Users.All().Any();
94-
}
95-
catch (InvalidOperationException)
96-
{
97-
// This won't work on Mock provider, but the SQL should be generated OK
98-
}
99-
100-
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
101-
}
102-
103-
[Test]
104-
public void SpecifyingMinShouldSelectFunction()
105-
{
106-
try
107-
{
108-
_db.Users.All().Select(_db.Users.Age.Min()).ToScalar();
109-
}
110-
catch (InvalidOperationException)
111-
{
112-
// This won't work on Mock provider, but the SQL should be generated OK
113-
}
114-
catch (SimpleDataException)
115-
{
116-
// This won't work on Mock provider, but the SQL should be generated OK
117-
}
118-
119-
GeneratedSqlIs("select min([dbo].[users].[age]) from [dbo].[users]");
120-
}
121-
122-
[Test]
123-
public void SpecifyingNonAggregatedColumnAndMinShouldAddGroupBy()
124-
{
125-
try
126-
{
127-
_db.Users.All().Select(_db.Users.Name, _db.Users.Age.Min().As("Youngest")).ToList();
128-
}
129-
catch (InvalidOperationException)
130-
{
131-
// This won't work on Mock provider, but the SQL should be generated OK
132-
}
133-
134-
GeneratedSqlIs("select [dbo].[users].[name],min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by [dbo].[users].[name]");
135-
}
136-
137-
[Test]
138-
public void SpecifyingNonAggregateFunctionAndMinShouldAddGroupBy()
139-
{
140-
try
141-
{
142-
_db.Users.All().Select(_db.Users.Name.Length(), _db.Users.Age.Min().As("Youngest")).ToList();
143-
}
144-
catch (InvalidOperationException)
145-
{
146-
// This won't work on Mock provider, but the SQL should be generated OK
147-
}
148-
149-
GeneratedSqlIs("select len([dbo].[users].[name]),min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by [dbo].[users].[name]");
150-
}
151-
152-
[Test]
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
using NUnit.Framework;
6+
using Simple.Data.Mocking.Ado;
7+
8+
namespace Simple.Data.IntegrationTest
9+
{
10+
[TestFixture]
11+
public class QueryTest : DatabaseIntegrationContext
12+
{
13+
protected override void SetSchema(MockSchemaProvider schemaProvider)
14+
{
15+
schemaProvider.SetTables(new[] {"dbo", "Users", "BASE TABLE"},
16+
new[] {"dbo", "UserBio", "BASE TABLE"});
17+
18+
schemaProvider.SetColumns(new object[] {"dbo", "Users", "Id", true},
19+
new[] {"dbo", "Users", "Name"},
20+
new[] {"dbo", "Users", "Password"},
21+
new[] {"dbo", "Users", "Age"},
22+
new[] {"dbo", "UserBio", "UserId"},
23+
new[] {"dbo", "UserBio", "Text"});
24+
25+
schemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 });
26+
schemaProvider.SetForeignKeys(new object[] { "FK_Users_UserBio", "dbo", "UserBio", "UserId", "dbo", "Users", "Id", 0 });
27+
}
28+
29+
[Test]
30+
public void SpecifyingColumnsShouldRestrictSelect()
31+
{
32+
_db.Users.All()
33+
.Select(_db.Users.Name, _db.Users.Password)
34+
.ToList();
35+
GeneratedSqlIs("select [dbo].[users].[name],[dbo].[users].[password] from [dbo].[users]");
36+
}
37+
38+
[Test]
39+
public void SpecifyingColumnWithAliasShouldAddAsClause()
40+
{
41+
_db.Users.All()
42+
.Select(_db.Users.Name, _db.Users.Password.As("SuperSecretPassword"))
43+
.ToList();
44+
GeneratedSqlIs("select [dbo].[users].[name],[dbo].[users].[password] as [supersecretpassword] from [dbo].[users]");
45+
}
46+
47+
[Test]
48+
public void SpecifyingColumnsFromOtherTablesShouldAddJoin()
49+
{
50+
_db.Users.All()
51+
.Select(_db.Users.Name, _db.Users.Password, _db.Users.UserBio.Text)
52+
.ToList();
53+
GeneratedSqlIs(
54+
"select [dbo].[users].[name],[dbo].[users].[password],[dbo].[userbio].[text] from [dbo].[users]" +
55+
" join [dbo].[userbio] on ([dbo].[users].[id] = [dbo].[userbio].[userid])");
56+
}
57+
58+
[Test]
59+
public void SpecifyingCountShouldSelectCount()
60+
{
61+
try
62+
{
63+
_db.Users.All().Count();
64+
}
65+
catch (InvalidOperationException)
66+
{
67+
// This won't work on Mock provider, but the SQL should be generated OK
68+
}
69+
70+
GeneratedSqlIs("select count(*) from [dbo].[users]");
71+
}
72+
73+
[Test]
74+
public void SpecifyingExistsShouldSelectDistinct1()
75+
{
76+
try
77+
{
78+
_db.Users.All().Exists();
79+
}
80+
catch (InvalidOperationException)
81+
{
82+
// This won't work on Mock provider, but the SQL should be generated OK
83+
}
84+
85+
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
86+
}
87+
88+
[Test]
89+
public void SpecifyingAnyShouldSelectDistinct1()
90+
{
91+
try
92+
{
93+
_db.Users.All().Any();
94+
}
95+
catch (InvalidOperationException)
96+
{
97+
// This won't work on Mock provider, but the SQL should be generated OK
98+
}
99+
100+
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
101+
}
102+
103+
[Test]
104+
public void SpecifyingMinShouldSelectFunction()
105+
{
106+
try
107+
{
108+
_db.Users.All().Select(_db.Users.Age.Min()).ToScalar();
109+
}
110+
catch (InvalidOperationException)
111+
{
112+
// This won't work on Mock provider, but the SQL should be generated OK
113+
}
114+
catch (SimpleDataException)
115+
{
116+
// This won't work on Mock provider, but the SQL should be generated OK
117+
}
118+
119+
GeneratedSqlIs("select min([dbo].[users].[age]) from [dbo].[users]");
120+
}
121+
122+
[Test]
123+
public void SpecifyingNonAggregatedColumnAndMinShouldAddGroupBy()
124+
{
125+
try
126+
{
127+
_db.Users.All().Select(_db.Users.Name, _db.Users.Age.Min().As("Youngest")).ToList();
128+
}
129+
catch (InvalidOperationException)
130+
{
131+
// This won't work on Mock provider, but the SQL should be generated OK
132+
}
133+
134+
GeneratedSqlIs("select [dbo].[users].[name],min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by [dbo].[users].[name]");
135+
}
136+
137+
[Test]
138+
public void SpecifyingNonAggregateFunctionAndMinShouldAddGroupBy()
139+
{
140+
try
141+
{
142+
_db.Users.All().Select(_db.Users.Name.Length(), _db.Users.Age.Min().As("Youngest")).ToList();
143+
}
144+
catch (InvalidOperationException)
145+
{
146+
// This won't work on Mock provider, but the SQL should be generated OK
147+
}
148+
149+
GeneratedSqlIs("select len([dbo].[users].[name]),min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by [dbo].[users].[name]");
150+
}
151+
152+
[Test]
153153
public void SpecifyingNonAggregateFunctionShouldNotApplyGroupBy()
154154
{
155155
try
@@ -161,6 +161,21 @@ public void SpecifyingNonAggregateFunctionShouldNotApplyGroupBy()
161161
// This won't work on Mock provider, but the SQL should be generated OK
162162
}
163163
GeneratedSqlIs("select [dbo].[users].[name],len([dbo].[users].[name]) as [namelength] from [dbo].[users]");
164-
}
165-
}
166-
}
164+
}
165+
166+
[Test]
167+
public void SpecifyingJoinTableShouldCreateDirectQuery()
168+
{
169+
try
170+
{
171+
_db.Users.QueryById(1).UserBio.ToList();
172+
}
173+
catch (InvalidOperationException)
174+
{
175+
// This won't work on Mock provider, but the SQL should be generated OK
176+
}
177+
GeneratedSqlIs("select [dbo].[userbio].[userid],[dbo].[userbio].[text] from [dbo].[userbio]" +
178+
" join [dbo].[users] on ([dbo].[users].[id] = [dbo].[userbio].[userid]) where [dbo].[users].[id] = @p1");
179+
}
180+
}
181+
}

0 commit comments

Comments
 (0)