Skip to content

Commit d24c1e4

Browse files
ThatRendleThatRendle
committed
With using explicit Joins
1 parent 3aee86b commit d24c1e4

10 files changed

Lines changed: 158 additions & 30 deletions

File tree

‎Simple.Data.Ado/Joiner.cs‎

Lines changed: 27 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -67,16 +67,22 @@ public IEnumerable<string> GetJoinClauses(IEnumerable<JoinClause> joins, IComman
6767
{
6868
var builder = new StringBuilder(JoinTypeToKeyword(join.JoinType));
6969
var joinExpression = join.JoinExpression ?? InferJoinExpression(join.Table);
70-
builder.AppendFormat(" JOIN {0}{1} ON ({2})",
71-
_schema.FindTable(_schema.BuildObjectName(join.Table.ToString())).QualifiedName,
72-
string.IsNullOrWhiteSpace(join.Table.GetAlias()) ? string.Empty : " " + _schema.QuoteObjectName(join.Table.GetAlias()),
73-
expressionFormatter.Format(joinExpression));
74-
yield return builder.ToString().Trim();
70+
if (!ReferenceEquals(joinExpression, null))
71+
{
72+
builder.AppendFormat(" JOIN {0}{1} ON ({2})",
73+
_schema.FindTable(_schema.BuildObjectName(join.Table.ToString())).QualifiedName,
74+
string.IsNullOrWhiteSpace(join.Table.GetAlias())
75+
? string.Empty
76+
: " " + _schema.QuoteObjectName(join.Table.GetAlias()),
77+
expressionFormatter.Format(joinExpression));
78+
yield return builder.ToString().Trim();
79+
}
7580
}
7681
}
7782

7883
private SimpleExpression InferJoinExpression(ObjectReference table)
7984
{
85+
if (table.GetOwner().IsNull()) return null;
8086
var table1 = _schema.FindTable(table.GetOwner().GetName());
8187
var table2 = _schema.FindTable(table.GetName());
8288
var foreignKey = GetForeignKey(table1, table2);
@@ -90,7 +96,7 @@ private void AddJoin(ObjectName table1Name, ObjectName table2Name, JoinType join
9096
var table1 = _schema.FindTable(table1Name);
9197
var table2 = _schema.FindTable(table2Name);
9298
var foreignKey = GetForeignKey(table1, table2);
93-
return MakeJoinText(table2, foreignKey, joinType);
99+
return MakeJoinText(table2, table2Name.Alias, foreignKey, joinType);
94100
});
95101
}
96102

@@ -109,16 +115,18 @@ private static ForeignKey GetForeignKey(Table table1, Table table2)
109115
return foreignKey;
110116
}
111117

112-
private string MakeJoinText(Table rightTable, ForeignKey foreignKey, JoinType joinType)
118+
private string MakeJoinText(Table rightTable, string alias, ForeignKey foreignKey, JoinType joinType)
113119
{
114120
var builder = new StringBuilder(JoinKeywordFor(joinType));
115-
builder.AppendFormat(" JOIN {0} ON (", rightTable.QualifiedName);
116-
builder.Append(FormatJoinExpression(foreignKey, 0));
121+
builder.AppendFormat(" JOIN {0}", rightTable.QualifiedName);
122+
if (!string.IsNullOrWhiteSpace(alias)) builder.Append(" " + _schema.QuoteObjectName(alias));
123+
builder.Append(" ON (");
124+
builder.Append(FormatJoinExpression(foreignKey, 0, alias));
117125

118126
for (int i = 1; i < foreignKey.Columns.Length; i++)
119127
{
120128
builder.Append(" AND ");
121-
builder.Append(FormatJoinExpression(foreignKey, i));
129+
builder.Append(FormatJoinExpression(foreignKey, i, alias));
122130
}
123131
builder.Append(")");
124132
return builder.ToString();
@@ -159,9 +167,12 @@ private SimpleExpression CreateJoinExpression(ObjectReference table, ForeignKey
159167
return masterObjectReference == detailObjectReference;
160168
}
161169

162-
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex)
170+
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex, string alias)
163171
{
164-
return string.Format("{0}.{1} = {2}.{3}", _schema.QuoteObjectName(foreignKey.MasterTable), _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
172+
var leftTable = string.IsNullOrWhiteSpace(alias)
173+
? _schema.QuoteObjectName(foreignKey.MasterTable)
174+
: _schema.QuoteObjectName(alias);
175+
return string.Format("{0}.{1} = {2}.{3}", leftTable, _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
165176
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
166177
}
167178

@@ -178,7 +189,7 @@ private string JoinTypeToKeyword(JoinType joinType)
178189
private static IEnumerable<Tuple<ObjectName, ObjectName>> GetTableNames(IEnumerable<ObjectReference> references, string schema)
179190
{
180191
return references.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))
181-
.TupleSelect((table1, table2) => Tuple.Create(new ObjectName(schema, table1), new ObjectName(schema, table2)))
192+
.TupleSelect((table1, table2) => Tuple.Create(new ObjectName(schema, table1.Item1, table1.Item2), new ObjectName(schema, table2.Item1, table2.Item2)))
182193
.Distinct();
183194
}
184195

@@ -187,10 +198,10 @@ private static IEnumerable<Tuple<ObjectName, ObjectName>> GetTableNames(SimpleEx
187198
return expression == null ? Enumerable.Empty<Tuple<ObjectName, ObjectName>>() : GetTableNames(GetReferencesFromExpression(expression), schema);
188199
}
189200

190-
private static IEnumerable<Tuple<string, string>> DynamicReferenceToTuplePairs(ObjectReference reference, string schema)
201+
private static IEnumerable<Tuple<Tuple<string, string>, Tuple<string, string>>> DynamicReferenceToTuplePairs(ObjectReference reference, string schema)
191202
{
192-
return reference.GetAllObjectNames()
193-
.SkipWhile(s => s.Equals(schema, StringComparison.OrdinalIgnoreCase))
203+
return reference.GetAllObjectNamesAndAliases()
204+
.SkipWhile(s => s.Item1.Equals(schema, StringComparison.OrdinalIgnoreCase))
194205
.SkipLast()
195206
.ToTuplePairs();
196207
}

‎Simple.Data.Ado/ObjectName.cs‎

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ public class ObjectName : IEquatable<ObjectName>
1010
{
1111
private readonly string _schema;
1212
private readonly string _name;
13+
private readonly string _alias;
1314

1415
public ObjectName(object schema, object name)
1516
{
@@ -18,11 +19,21 @@ public ObjectName(object schema, object name)
1819
_name = (string)name;
1920
}
2021

21-
public ObjectName(string schema, string name)
22+
public ObjectName(string schema, string name) : this(schema, name, null)
23+
{
24+
}
25+
26+
public ObjectName(string schema, string name, string alias)
2227
{
2328
if (name == null) throw new ArgumentNullException("name");
2429
_schema = schema;
2530
_name = name;
31+
_alias = alias;
32+
}
33+
34+
public string Alias
35+
{
36+
get { return _alias; }
2637
}
2738

2839
public string Name

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 30 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -80,12 +80,30 @@ private void SetQueryContext(SimpleQuery query)
8080
private void HandleWithClauses()
8181
{
8282
var withClauses = _query.Clauses.OfType<WithClause>().ToList();
83+
var relationTypeDict = new Dictionary<ObjectReference, RelationType>();
8384
if (withClauses.Count > 0)
8485
{
8586
foreach (var withClause in withClauses)
8687
{
87-
if (withClause.ObjectReference.GetOwner().GetName() == _tableName.Name)
88+
if (withClause.ObjectReference.GetOwner().IsNull())
8889
{
90+
var joinClause =
91+
_query.Clauses.OfType<JoinClause>().FirstOrDefault(j => j.Table.GetAliasOrName() == withClause.ObjectReference.GetAliasOrName());
92+
if (joinClause != null)
93+
{
94+
_columns =
95+
_columns.Concat(
96+
_schema.FindTable(joinClause.Table.GetName()).Columns.Select(
97+
c => new ObjectReference(c.ActualName, joinClause.Table)))
98+
.ToArray();
99+
relationTypeDict[joinClause.Table] = withClause.Type == WithType.One
100+
? RelationType.ManyToOne
101+
: RelationType.OneToMany;
102+
}
103+
}
104+
else
105+
{
106+
relationTypeDict[withClause.ObjectReference] = RelationType.None;
89107
_columns =
90108
_columns.Concat(
91109
_schema.FindTable(withClause.ObjectReference.GetName()).Columns.Select(
@@ -95,14 +113,22 @@ private void HandleWithClauses()
95113
}
96114
_columns =
97115
_columns.OfType<ObjectReference>()
98-
.Select(c => _schema.FindTable(c.GetOwner().GetName()) == _table ? c : AddWithAlias(c))
116+
.Select(c => IsCoreTable(c.GetOwner()) ? c : AddWithAlias(c, relationTypeDict[c.GetOwner()]))
99117
.ToArray();
100118
}
101119
}
102120

103-
private ObjectReference AddWithAlias(ObjectReference c)
121+
private bool IsCoreTable(ObjectReference tableReference)
122+
{
123+
if (ReferenceEquals(tableReference, null)) throw new ArgumentNullException("tableReference");
124+
if (!string.IsNullOrWhiteSpace(tableReference.GetAlias())) return false;
125+
return _schema.FindTable(tableReference.GetName()) == _table;
126+
}
127+
128+
private ObjectReference AddWithAlias(ObjectReference c, RelationType relationType = RelationType.None)
104129
{
105-
var relationType = _schema.GetRelationType(c.GetOwner().GetOwner().GetName(), c.GetOwner().GetName());
130+
if (relationType == RelationType.None)
131+
relationType = _schema.GetRelationType(c.GetOwner().GetOwner().GetName(), c.GetOwner().GetName());
106132
if (relationType == RelationType.None) throw new InvalidOperationException("No Join found");
107133
return c.As(string.Format("__with{0}__{1}__{2}",
108134
relationType == RelationType.OneToMany

‎Simple.Data.BehaviourTest/DatabaseIntegrationContext.cs‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,8 @@
66

77
namespace Simple.Data.IntegrationTest
88
{
9+
using System.Diagnostics;
10+
911
public abstract class DatabaseIntegrationContext
1012
{
1113
protected MockDatabase _mockDatabase;
@@ -68,9 +70,10 @@ protected static void EatException<TException>(Action action)
6870
{
6971
action();
7072
}
71-
catch (TException)
73+
catch (TException ex)
7274
{
7375
// This won't work on Mock provider, but the SQL should be generated OK
76+
Trace.TraceError(ex.Message);
7477
}
7578

7679
}

‎Simple.Data.BehaviourTest/Query/WithTest.cs‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,5 +96,24 @@ public void SingleWithClauseUsingReferenceWithAliasShouldApplyAliasToSql()
9696

9797
GeneratedSqlIs(expectedSql);
9898
}
99+
100+
[Test]
101+
public void SingleWithClauseUsingExplicitJoinShouldApplyAliasToSql()
102+
{
103+
const string expectedSql = "select [dbo].[employee].[id],[dbo].[employee].[name]," +
104+
"[dbo].[employee].[managerid],[dbo].[employee].[departmentid]," +
105+
"[manager].[id] as [__withn__manager__id],[manager].[name] as [__withn__manager__name]," +
106+
"[manager].[managerid] as [__withn__manager__managerid],[manager].[departmentid] as [__withn__manager__departmentid]" +
107+
" from [dbo].[employee] left join [dbo].[employee] [manager] on ([manager].[id] = [dbo].[employee].[managerid])";
108+
109+
dynamic manager;
110+
var q = _db.Employees.All()
111+
.OuterJoin(_db.Employees.As("Manager"), out manager).On(Id: _db.Employees.ManagerId)
112+
.With(manager);
113+
114+
EatException(() => q.ToList());
115+
116+
GeneratedSqlIs(expectedSql);
117+
}
99118
}
100119
}

‎Simple.Data/ObjectReference.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,6 +165,16 @@ public string[] GetAllObjectNames()
165165
return _owner.GetAllObjectNames().Concat(new[] {_name}).ToArray();
166166
}
167167

168+
/// <summary>
169+
/// Gets the names of all objects included in the reference as an array, with the uppermost object first.
170+
/// </summary>
171+
/// <returns></returns>
172+
public Tuple<string,string>[] GetAllObjectNamesAndAliases()
173+
{
174+
if (ReferenceEquals(GetOwner(), null)) return new[] {Tuple.Create(_name, GetAlias())};
175+
return _owner.GetAllObjectNamesAndAliases().Concat(new[] {Tuple.Create(_name, GetAlias())}).ToArray();
176+
}
177+
168178
public string GetAllObjectNamesDotted()
169179
{
170180
return string.Join(".", GetAllObjectNames());

‎Simple.Data/Simple.Data.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,7 @@
177177
<Compile Include="WithClause.cs" />
178178
<Compile Include="WithCountClause.cs" />
179179
<Compile Include="WithMode.cs" />
180+
<Compile Include="WithType.cs" />
180181
</ItemGroup>
181182
<ItemGroup>
182183
<None Include="app.config" />

‎Simple.Data/SimpleQuery.cs‎

Lines changed: 29 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -298,10 +298,6 @@ private SimpleQuery With(IEnumerable<object> args)
298298
foreach (var reference in args.OfType<ObjectReference>())
299299
{
300300
clauses.Add(new WithClause(reference));
301-
if (!string.IsNullOrWhiteSpace(reference.GetAlias()))
302-
{
303-
clauses.Add(new JoinClause(reference, JoinType.Outer));
304-
}
305301
}
306302
return new SimpleQuery(this, clauses.ToArray());
307303
}
@@ -334,11 +330,37 @@ public SimpleQuery Join(ObjectReference objectReference, out dynamic queryObject
334330
return this;
335331
}
336332

333+
public SimpleQuery LeftJoin(ObjectReference objectReference)
334+
{
335+
return OuterJoin(objectReference);
336+
}
337+
338+
public SimpleQuery LeftJoin(ObjectReference objectReference, out dynamic queryObjectReference)
339+
{
340+
return OuterJoin(objectReference, out queryObjectReference);
341+
}
342+
343+
public SimpleQuery OuterJoin(ObjectReference objectReference)
344+
{
345+
if (ReferenceEquals(objectReference, null)) throw new ArgumentNullException("objectReference");
346+
_tempJoinWaitingForOn = new JoinClause(objectReference, JoinType.Outer);
347+
348+
return this;
349+
}
350+
351+
public SimpleQuery OuterJoin(ObjectReference objectReference, out dynamic queryObjectReference)
352+
{
353+
_tempJoinWaitingForOn = new JoinClause(objectReference, JoinType.Outer);
354+
queryObjectReference = objectReference;
355+
356+
return this;
357+
}
358+
337359
public SimpleQuery On(SimpleExpression joinExpression)
338360
{
339361
if (_tempJoinWaitingForOn == null)
340362
throw new InvalidOperationException("Call to On must be preceded by call to Join.");
341-
return AddNewJoin(new JoinClause(_tempJoinWaitingForOn.Table, joinExpression));
363+
return AddNewJoin(new JoinClause(_tempJoinWaitingForOn.Table, _tempJoinWaitingForOn.JoinType, joinExpression));
342364
}
343365

344366
[Obsolete]
@@ -386,11 +408,12 @@ private SimpleQuery ParseOn(InvokeMemberBinder binder, IEnumerable<object> args)
386408
throw new InvalidOperationException("Call to On must be preceded by call to Join.");
387409
var joinExpression = ExpressionHelper.CriteriaDictionaryToExpression(_tempJoinWaitingForOn.Table,
388410
binder.NamedArgumentsToDictionary(args));
389-
return new SimpleQuery(this, _clauses.Append(new JoinClause(_tempJoinWaitingForOn.Table, joinExpression)));
411+
return AddNewJoin(new JoinClause(_tempJoinWaitingForOn.Table, _tempJoinWaitingForOn.JoinType, joinExpression));
390412
}
391413

392414
private SimpleQuery AddNewJoin(JoinClause newJoin)
393415
{
416+
_tempJoinWaitingForOn = null;
394417
return new SimpleQuery(this, _clauses.Append(newJoin));
395418
}
396419

‎Simple.Data/WithClause.cs‎

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,22 +4,37 @@ public class WithClause : SimpleQueryClauseBase
44
{
55
private readonly ObjectReference _objectReference;
66
private readonly WithMode _mode;
7+
private readonly WithType _type;
78

8-
public WithClause(ObjectReference objectReference) : this(objectReference, WithMode.NotSpecified)
9+
public WithClause(ObjectReference objectReference) : this(objectReference, WithType.NotSpecified)
910
{
1011
}
1112

12-
public WithClause(ObjectReference objectReference, WithMode mode)
13+
public WithClause(ObjectReference objectReference, WithType type) : this(objectReference, WithMode.NotSpecified, type)
14+
{
15+
}
16+
17+
public WithClause(ObjectReference objectReference, WithMode mode) : this(objectReference, mode, WithType.NotSpecified)
18+
{
19+
}
20+
21+
public WithClause(ObjectReference objectReference, WithMode mode, WithType type)
1322
{
1423
_objectReference = objectReference;
1524
_mode = mode;
25+
_type = type;
1626
}
1727

1828
public WithMode Mode
1929
{
2030
get { return _mode; }
2131
}
2232

33+
public WithType Type
34+
{
35+
get { return _type; }
36+
}
37+
2338
public ObjectReference ObjectReference
2439
{
2540
get { return _objectReference; }

‎Simple.Data/WithType.cs‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
namespace Simple.Data
2+
{
3+
public enum WithType
4+
{
5+
NotSpecified = 0,
6+
One = 1,
7+
Many = 2
8+
}
9+
}

0 commit comments

Comments
 (0)