Skip to content

Commit 2d18f17

Browse files
ThatRendleThatRendle
committed
Fixed grouping on function-wrapped references
1 parent 337b0e5 commit 2d18f17

5 files changed

Lines changed: 67 additions & 19 deletions

File tree

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -152,7 +152,7 @@ private void HandleGrouping()
152152

153153
if (groupColumns.Count == 0) return;
154154

155-
_commandBuilder.Append(" GROUP BY " + string.Join(",", groupColumns.Select(FormatGroupByColumnClause)));
155+
_commandBuilder.Append(" GROUP BY " + string.Join(",", groupColumns.Select(_simpleReferenceFormatter.FormatColumnClauseWithoutAlias)));
156156
}
157157

158158
private void HandleOrderBy()
@@ -165,9 +165,18 @@ private void HandleOrderBy()
165165

166166
private string ToOrderByDirective(OrderByClause item)
167167
{
168-
var col = _table.FindColumn(item.Reference.GetName());
168+
string name;
169+
if (_columns.Any(r => (!string.IsNullOrWhiteSpace(r.GetAlias())) && r.GetAlias().Equals(item.Reference.GetName())))
170+
{
171+
name = item.Reference.GetName();
172+
}
173+
else
174+
{
175+
name = _table.FindColumn(item.Reference.GetName()).QualifiedName;
176+
}
177+
169178
var direction = item.Direction == OrderByDirection.Descending ? " DESC" : string.Empty;
170-
return col.QualifiedName + direction;
179+
return name + direction;
171180
}
172181

173182
private string GetSelectClause(ObjectName tableName)

‎Simple.Data.Ado/SimpleReferenceFormatter.cs‎

Lines changed: 37 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -19,11 +19,21 @@ public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBu
1919

2020
public string FormatColumnClause(SimpleReference reference)
2121
{
22-
var formatted = TryFormatAsObjectReference(reference as ObjectReference)
22+
return FormatColumnClause(reference, false);
23+
}
24+
25+
public string FormatColumnClauseWithoutAlias(SimpleReference reference)
26+
{
27+
return FormatColumnClause(reference, true);
28+
}
29+
30+
private string FormatColumnClause(SimpleReference reference, bool excludeAlias)
31+
{
32+
var formatted = TryFormatAsObjectReference(reference as ObjectReference, excludeAlias)
2333
??
24-
TryFormatAsFunctionReference(reference as FunctionReference)
34+
TryFormatAsFunctionReference(reference as FunctionReference, excludeAlias)
2535
??
26-
TryFormatAsMathReference(reference as MathReference);
36+
TryFormatAsMathReference(reference as MathReference, excludeAlias);
2737

2838
if (formatted != null) return formatted;
2939

@@ -36,12 +46,22 @@ private string FormatObject(object obj)
3646
return reference != null ? FormatColumnClause(reference) : obj.ToString();
3747
}
3848

39-
private string TryFormatAsMathReference(MathReference mathReference)
49+
private string TryFormatAsMathReference(MathReference mathReference, bool excludeAlias)
4050
{
4151
if (ReferenceEquals(mathReference, null)) return null;
4252

43-
return string.Format("{0} {1} {2}", FormatObject(mathReference.LeftOperand),
44-
MathOperatorToString(mathReference.Operator), FormatObject(mathReference.RightOperand));
53+
if (excludeAlias || mathReference.GetAlias() == null)
54+
{
55+
return string.Format("{0} {1} {2}", FormatObject(mathReference.LeftOperand),
56+
MathOperatorToString(mathReference.Operator),
57+
FormatObject(mathReference.RightOperand));
58+
}
59+
60+
return string.Format("{0} {1} {2} AS {3}", FormatObject(mathReference.LeftOperand),
61+
MathOperatorToString(mathReference.Operator),
62+
FormatObject(mathReference.RightOperand),
63+
mathReference.GetAlias());
64+
4565
}
4666

4767
private static string MathOperatorToString(MathOperator @operator)
@@ -63,17 +83,20 @@ private static string MathOperatorToString(MathOperator @operator)
6383
}
6484
}
6585

66-
private string TryFormatAsFunctionReference(FunctionReference functionReference)
86+
private string TryFormatAsFunctionReference(FunctionReference functionReference, bool excludeAlias)
6787
{
6888
if (ReferenceEquals(functionReference, null)) return null;
6989

7090
var sqlName = _functionNameConverter.ConvertToSqlName(functionReference.Name);
71-
return functionReference.GetAlias() == null
72-
? string.Format("{0}({1}{2})", sqlName,
73-
FormatColumnClause(functionReference.Argument),
74-
FormatAdditionalArguments(functionReference.AdditionalArguments))
75-
: string.Format("{0}({1}) AS {2}", sqlName,
91+
if (excludeAlias || functionReference.GetAlias() == null)
92+
{
93+
return string.Format("{0}({1}{2})", sqlName,
94+
FormatColumnClause(functionReference.Argument),
95+
FormatAdditionalArguments(functionReference.AdditionalArguments));
96+
}
97+
return string.Format("{0}({1}{2}) AS {3}", sqlName,
7698
FormatColumnClause(functionReference.Argument),
99+
FormatAdditionalArguments(functionReference.AdditionalArguments),
77100
_schema.QuoteObjectName(functionReference.GetAlias()));
78101
}
79102

@@ -88,7 +111,7 @@ private string FormatAdditionalArguments(IEnumerable<object> additionalArguments
88111
return builder != null ? builder.ToString() : string.Empty;
89112
}
90113

91-
private string TryFormatAsObjectReference(ObjectReference objectReference)
114+
private string TryFormatAsObjectReference(ObjectReference objectReference, bool excludeAlias)
92115
{
93116
if (ReferenceEquals(objectReference, null)) return null;
94117

@@ -97,7 +120,7 @@ private string TryFormatAsObjectReference(ObjectReference objectReference)
97120
? table.QualifiedName
98121
: _schema.QuoteObjectName(objectReference.GetOwner().GetAlias());
99122
var column = table.FindColumn(objectReference.GetName());
100-
if (objectReference.GetAlias() == null)
123+
if (excludeAlias || objectReference.GetAlias() == null)
101124
return string.Format("{0}.{1}", tableName, column.QuotedName);
102125
else
103126
return string.Format("{0}.{1} AS {2}", tableName, column.QuotedName,

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,5 +47,21 @@ public void SubstringIsEnteredCorrectlyInFindOne()
4747
Parameter(1).Is(1);
4848
Parameter(2).Is("A");
4949
}
50+
51+
[Test]
52+
public void GroupingAndOrderingOnFunction()
53+
{
54+
const string expected =
55+
@"select substring([dbo].[users].[name],@p1,@p2) as [foo],max(substring([dbo].[users].[name],@p3,@p4)) as [bar] from [dbo].[users] group by substring([dbo].[users].[name],@p5,@p6) order by bar desc";
56+
57+
var column1 = _db.Users.Name.Substring(0, 5).As("Foo");
58+
var column2 = _db.Users.Name.Substring(5, 5).Max().As("Bar");
59+
EatException<InvalidOperationException>( () => _db.Users.All()
60+
.Select(column1, column2)
61+
.OrderByBarDescending()
62+
.ToList());
63+
64+
GeneratedSqlIs(expected);
65+
}
5066
}
5167
}

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -168,7 +168,7 @@ public void SpecifyingNonAggregateFunctionAndMinShouldAddGroupBy()
168168
// This won't work on Mock provider, but the SQL should be generated OK
169169
}
170170

171-
GeneratedSqlIs("select len([dbo].[users].[name]),min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by [dbo].[users].[name]");
171+
GeneratedSqlIs("select len([dbo].[users].[name]),min([dbo].[users].[age]) as [youngest] from [dbo].[users] group by len([dbo].[users].[name])");
172172
}
173173

174174
[Test]

‎Simple.Data/FunctionReference.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ public IEnumerable<object> AdditionalArguments
4545

4646
public FunctionReference As(string alias)
4747
{
48-
return new FunctionReference(_name, _argument, _isAggregate, alias);
48+
return new FunctionReference(_name, _argument, _isAggregate, alias, _additionalArguments);
4949
}
5050

5151
public override string GetAliasOrName()

0 commit comments

Comments
 (0)