Skip to content

Commit d5f5ae6

Browse files
ThatRendleThatRendle
committed
Basic With clause generates correct SQL
1 parent 6257f34 commit d5f5ae6

4 files changed

Lines changed: 55 additions & 12 deletions

File tree

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 24 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,8 +8,7 @@ namespace Simple.Data.Ado
88
{
99
public class QueryBuilder
1010
{
11-
private readonly IFunctionNameConverter _functionNameConverter = new FunctionNameConverter();
12-
private SimpleReferenceFormatter _simpleReferenceFormatter;
11+
private readonly SimpleReferenceFormatter _simpleReferenceFormatter;
1312
private readonly AdoAdapter _adoAdapter;
1413
private readonly int _bulkIndex;
1514
private readonly DatabaseSchema _schema;
@@ -20,7 +19,7 @@ public class QueryBuilder
2019
private SimpleExpression _whereCriteria;
2120
private SimpleExpression _havingCriteria;
2221
private SimpleReference[] _columns;
23-
private CommandBuilder _commandBuilder;
22+
private readonly CommandBuilder _commandBuilder;
2423
private List<SimpleQueryClauseBase> _unhandledClauses;
2524

2625
public QueryBuilder(AdoAdapter adoAdapter) : this(adoAdapter, -1)
@@ -54,31 +53,49 @@ public ICommandBuilder Build(SimpleQuery query, out IEnumerable<SimpleQueryClaus
5453
private void SetQueryContext(SimpleQuery query)
5554
{
5655
_query = query;
56+
_tableName = _schema.BuildObjectName(query.TableName);
57+
_table = _schema.FindTable(_tableName);
5758
var selectClause = _query.Clauses.OfType<SelectClause>().SingleOrDefault();
5859
if (selectClause != null)
5960
{
6061
_columns = selectClause.Columns.ToArray();
6162
}
6263
else
6364
{
64-
_columns = new SimpleReference[0];
65+
_columns = _table.Columns.Select(c => ObjectReference.FromStrings(_table.ActualName, c.ActualName)).ToArray();
66+
}
67+
68+
var withClauses = _query.Clauses.OfType<WithClause>().ToList();
69+
if (withClauses.Count > 0)
70+
{
71+
foreach (var withClause in withClauses)
72+
{
73+
if (withClause.ObjectReference.GetOwner().GetName() == _tableName.Name)
74+
{
75+
_columns =
76+
_columns.Concat(
77+
_schema.FindTable(withClause.ObjectReference.GetName()).Columns.Select(
78+
c => ObjectReference.FromStrings(_table.ActualName, withClause.ObjectReference.GetName(), c.ActualName))).ToArray();
79+
}
80+
}
81+
_columns =
82+
_columns.OfType<ObjectReference>().Select(
83+
c => c.As(string.Format("__with__{0}__{1}", c.GetOwner().GetName(), c.GetName()))).ToArray();
6584
}
6685

6786
_whereCriteria = _query.Clauses.OfType<WhereClause>().Aggregate(SimpleExpression.Empty,
6887
(seed, where) => seed && where.Criteria);
6988
_havingCriteria = _query.Clauses.OfType<HavingClause>().Aggregate(SimpleExpression.Empty,
7089
(seed, having) => seed && having.Criteria);
7190

72-
_tableName = _schema.BuildObjectName(query.TableName);
73-
_table = _schema.FindTable(_tableName);
7491
_commandBuilder.SetText(GetSelectClause(_tableName));
7592
}
7693

7794
private void HandleJoins()
7895
{
7996
if (_whereCriteria == SimpleExpression.Empty && _havingCriteria == SimpleExpression.Empty
8097
&& (!_query.Clauses.OfType<JoinClause>().Any())
81-
&& (_columns.Where(r => !(r is CountSpecialReference)).Count() == 0)) return;
98+
&& (_columns.All(r => (r is CountSpecialReference)))) return;
8299

83100
var joiner = new Joiner(JoinType.Inner, _schema);
84101

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -39,10 +39,10 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
3939
[Test]
4040
public void SingleWithClauseShouldUseJoin()
4141
{
42-
const string expectedSql = "select [employee].[id] as [__with__employee__id],[employee].[name] as [__with__employee__name],"+
43-
"[employee].[managerid] as [__with__employee__managerid],[employee].[departmentid] as [__with__employee__departmentid],"+
44-
"[department].[id] as [__with__department__id],[department].[name] as [__with__department__name]"+
45-
"from [employee] left join [department] on [employee].[departmentid] = [department].[id]";
42+
const string expectedSql = "select [dbo].[employee].[id] as [__with__employee__id],[dbo].[employee].[name] as [__with__employee__name],"+
43+
"[dbo].[employee].[managerid] as [__with__employee__managerid],[dbo].[employee].[departmentid] as [__with__employee__departmentid],"+
44+
"[dbo].[department].[id] as [__with__department__id],[dbo].[department].[name] as [__with__department__name]"+
45+
" from [dbo].[employee] left join [dbo].[department] on ([dbo].[department].[id] = [dbo].[employee].[departmentid])";
4646

4747
var q = _db.Employees.All().WithDepartment();
4848

‎Simple.Data/SimpleQuery.cs‎

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -271,10 +271,32 @@ public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, o
271271
return true;
272272
}
273273
}
274+
if (binder.Name.StartsWith("with", StringComparison.OrdinalIgnoreCase))
275+
{
276+
result = ParseWith(binder, args);
277+
return true;
278+
}
274279

275280
return false;
276281
}
277282

283+
private SimpleQuery ParseWith(InvokeMemberBinder binder, object[] args)
284+
{
285+
if (binder.Name.Equals("with", StringComparison.OrdinalIgnoreCase))
286+
{
287+
return With(args);
288+
}
289+
290+
var objectName = binder.Name.Substring(4);
291+
var withClause = new WithClause(new ObjectReference(objectName, new ObjectReference(_tableName, _dataStrategy), _dataStrategy));
292+
return new SimpleQuery(this, _clauses.Append(withClause));
293+
}
294+
295+
private SimpleQuery With(object[] args)
296+
{
297+
throw new NotImplementedException();
298+
}
299+
278300
private ObjectReference ObjectAsObjectReference(object o)
279301
{
280302
var objectReference = o as ObjectReference;
@@ -392,7 +414,7 @@ public IEnumerable<T> Cast<T>()
392414

393415
public IEnumerable<T> OfType<T>()
394416
{
395-
return new OfTypeEnumerable<T>(Run());
417+
return new OfTypeEnumerable<T>(Run());
396418
}
397419

398420
public IList<dynamic> ToList()

‎Simple.Data/WithClause.cs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,10 @@ public class WithClause : SimpleQueryClauseBase
55
private readonly ObjectReference _objectReference;
66
private readonly WithMode _mode;
77

8+
public WithClause(ObjectReference objectReference) : this(objectReference, WithMode.NotSpecified)
9+
{
10+
}
11+
812
public WithClause(ObjectReference objectReference, WithMode mode)
913
{
1014
_objectReference = objectReference;

0 commit comments

Comments
 (0)