Skip to content

Commit 4fd06ca

Browse files
ThatRendleThatRendle
committed
Allow complex references as update values
1 parent be2bb27 commit 4fd06ca

File tree

9 files changed

+136
-47
lines changed

9 files changed

+136
-47
lines changed

‎Simple.Data.Ado/DelegatingConnectionBase1.cs‎

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,5 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.Data;
1+
using System.Data;
42
using System.Data.Common;
5-
using System.Linq;
6-
using System.Text;
73

84
namespace Simple.Data.Ado
95
{

‎Simple.Data.Ado/SimpleReferenceFormatter.cs‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,9 @@ private string FormatColumnClause(SimpleReference reference, bool excludeAlias)
4242
throw new InvalidOperationException("SimpleReference type not supported.");
4343
}
4444

45+
// ReSharper disable UnusedParameter.Local
4546
private string TryFormatAsAllColumnsReference(AllColumnsSpecialReference allColumnsSpecialReference, bool excludeAlias)
47+
// ReSharper restore UnusedParameter.Local
4648
{
4749
if (ReferenceEquals(allColumnsSpecialReference, null)) return null;
4850
var table = _schema.FindTable(allColumnsSpecialReference.Table.GetAllObjectNamesDotted());
@@ -55,7 +57,7 @@ private string TryFormatAsAllColumnsReference(AllColumnsSpecialReference allColu
5557
private string FormatObject(object obj)
5658
{
5759
var reference = obj as SimpleReference;
58-
return reference != null ? FormatColumnClause(reference) : obj.ToString();
60+
return reference != null ? FormatColumnClause(reference) : _commandBuilder.AddParameter(obj).Name;
5961
}
6062

6163
private string TryFormatAsMathReference(MathReference mathReference, bool excludeAlias)
@@ -133,10 +135,12 @@ private string TryFormatAsObjectReference(ObjectReference objectReference, bool
133135
: _schema.QuoteObjectName(objectReference.GetOwner().GetAlias());
134136
var column = table.FindColumn(objectReference.GetName());
135137
if (excludeAlias || objectReference.GetAlias() == null)
138+
{
136139
return string.Format("{0}.{1}", tableName, column.QuotedName);
137-
else
138-
return string.Format("{0}.{1} AS {2}", tableName, column.QuotedName,
139-
_schema.QuoteObjectName(objectReference.GetAlias()));
140+
}
141+
142+
return string.Format("{0}.{1} AS {2}", tableName, column.QuotedName,
143+
_schema.QuoteObjectName(objectReference.GetAlias()));
140144
}
141145

142146
}

‎Simple.Data.Ado/UpdateHelper.cs‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,20 @@ private string GetUpdateClause(Table table, IEnumerable<KeyValuePair<string, obj
101101
private string CreateColumnUpdateClause(string columnName, object value, Table table)
102102
{
103103
var column = table.FindColumn(columnName);
104-
return string.Format("{0} = {1}", column.QuotedName, _commandBuilder.AddParameter(value, column).Name);
104+
var mathReference = value as SimpleReference;
105+
string rightOperand;
106+
if (ReferenceEquals(mathReference, null))
107+
{
108+
rightOperand = _commandBuilder.AddParameter(value, column).Name;
109+
}
110+
else
111+
{
112+
rightOperand =
113+
new SimpleReferenceFormatter(_schema, _commandBuilder).FormatColumnClauseWithoutAlias(mathReference);
114+
}
115+
116+
117+
return string.Format("{0} = {1}", column.QuotedName, rightOperand);
105118
}
106119
}
107120
}

‎Simple.Data.BehaviourTest/FindTest.cs‎

Lines changed: 26 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
namespace Simple.Data.IntegrationTest
22
{
3-
using System.Collections;
43
using System.Collections.Generic;
54
using Mocking.Ado;
65
using NUnit.Framework;
@@ -10,6 +9,7 @@ public class FindTest : DatabaseIntegrationContext
109
{
1110
protected override void SetSchema(MockSchemaProvider schemaProvider)
1211
{
12+
// ReSharper disable CoVariantArrayConversion
1313
schemaProvider.SetTables(new[] { "dbo", "Users", "BASE TABLE" },
1414
new[] { "dbo", "MyTable", "BASE TABLE" });
1515
schemaProvider.SetColumns(new object[] { "dbo", "Users", "Id", true },
@@ -18,29 +18,30 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
1818
new[] { "dbo", "Users", "Age" },
1919
new[] { "dbo", "MyTable", "Column1"});
2020
schemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 });
21+
// ReSharper restore CoVariantArrayConversion
2122
}
2223

23-
private const string usersColumns = "[dbo].[Users].[Id], [dbo].[Users].[Name], [dbo].[Users].[Password], [dbo].[Users].[Age]";
24+
private const string UsersColumns = "[dbo].[Users].[Id], [dbo].[Users].[Name], [dbo].[Users].[Password], [dbo].[Users].[Age]";
2425
[Test]
2526
public void TestFindEqualWithInt32()
2627
{
2728
_db.Users.Find(_db.Users.Id == 1);
28-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[users] where [dbo].[users].[id] = @p1");
29+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[users] where [dbo].[users].[id] = @p1");
2930
Parameter(0).Is(1);
3031
}
3132

3233
[Test]
3334
public void TestFindWithNull()
3435
{
3536
_db.Users.Find(_db.Users.Id == null);
36-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[users] where [dbo].[users].[id] IS NULL");
37+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[users] where [dbo].[users].[id] IS NULL");
3738
}
3839

3940
[Test]
4041
public void TestFindWithTwoCriteriasOneBeingNull()
4142
{
4243
_db.Users.Find(_db.Users.Id == 1 || _db.Users.Id == null);
43-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[users] where ([dbo].[users].[id] = @p1 OR [dbo].[users].[id] IS NULL)");
44+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[users] where ([dbo].[users].[id] = @p1 OR [dbo].[users].[id] IS NULL)");
4445
Parameter(0).Is(1);
4546
Parameter(1).DoesNotExist();
4647
}
@@ -49,95 +50,96 @@ public void TestFindWithTwoCriteriasOneBeingNull()
4950
public void TestFindNotEqualWithInt32()
5051
{
5152
_db.Users.Find(_db.Users.Id != 1);
52-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] != @p1");
53+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] != @p1");
5354
Parameter(0).Is(1);
5455
}
5556

5657
[Test]
5758
public void TestFindGreaterThanWithInt32()
5859
{
5960
_db.Users.Find(_db.Users.Id > 1);
60-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] > @p1");
61+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] > @p1");
6162
Parameter(0).Is(1);
6263
}
6364

6465
[Test]
6566
public void TestFindGreaterThanOrEqualWithInt32()
6667
{
6768
_db.Users.Find(_db.Users.Id >= 1);
68-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] >= @p1");
69+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] >= @p1");
6970
Parameter(0).Is(1);
7071
}
7172

7273
[Test]
7374
public void TestFindLessThanWithInt32()
7475
{
7576
_db.Users.Find(_db.Users.Id < 1);
76-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] < @p1");
77+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] < @p1");
7778
Parameter(0).Is(1);
7879
}
7980

8081
[Test]
8182
public void TestFindLessThanOrEqualWithInt32()
8283
{
8384
_db.Users.Find(_db.Users.Id <= 1);
84-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] <= @p1");
85+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] <= @p1");
8586
Parameter(0).Is(1);
8687
}
8788

8889
[Test]
8990
public void TestFindModulo()
9091
{
9192
_db.Users.Find(_db.Users.Id % 2 == 1);
92-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] % 2 = @p1");
93-
Parameter(0).Is(1);
93+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] % @p1 = @p2");
94+
Parameter(0).Is(2);
95+
Parameter(1).Is(1);
9496
}
9597

9698
[Test]
9799
public void TestFindWithAdd()
98100
{
99101
_db.Users.Find(_db.Users.Id + _db.Users.Age == 42);
100-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] + [dbo].[Users].[Age] = @p1");
102+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] + [dbo].[Users].[Age] = @p1");
101103
Parameter(0).Is(42);
102104
}
103105

104106
[Test]
105107
public void TestFindWithSubtract()
106108
{
107109
_db.Users.Find(_db.Users.Id - _db.Users.Age == 42);
108-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] - [dbo].[Users].[Age] = @p1");
110+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] - [dbo].[Users].[Age] = @p1");
109111
Parameter(0).Is(42);
110112
}
111113

112114
[Test]
113115
public void TestFindWithMultiply()
114116
{
115117
_db.Users.Find(_db.Users.Id * _db.Users.Age == 42);
116-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] * [dbo].[Users].[Age] = @p1");
118+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] * [dbo].[Users].[Age] = @p1");
117119
Parameter(0).Is(42);
118120
}
119121

120122
[Test]
121123
public void TestFindWithDivide()
122124
{
123125
_db.Users.Find(_db.Users.Id / _db.Users.Age == 42);
124-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] / [dbo].[Users].[Age] = @p1");
126+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[Id] / [dbo].[Users].[Age] = @p1");
125127
Parameter(0).Is(42);
126128
}
127129

128130
[Test]
129131
public void TestFindByNamedParameterSingleColumn()
130132
{
131133
_db.Users.FindBy(Name: "Foo");
132-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[name] = @p1");
134+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[name] = @p1");
133135
Parameter(0).Is("Foo");
134136
}
135137

136138
[Test]
137139
public void TestFindByNamedParameterTwoColumns()
138140
{
139141
_db.Users.FindBy(Name: "Foo", Password: "password");
140-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where ([dbo].[Users].[name] = @p1 and [dbo].[Users].[password] = @p2)");
142+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where ([dbo].[Users].[name] = @p1 and [dbo].[Users].[password] = @p2)");
141143
Parameter(0).Is("Foo");
142144
Parameter(1).Is("password");
143145
}
@@ -146,7 +148,7 @@ public void TestFindByNamedParameterTwoColumns()
146148
public void TestFindByDynamicSingleColumn()
147149
{
148150
_db.Users.FindByName("Foo");
149-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[name] = @p1");
151+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[name] = @p1");
150152
Parameter(0).Is("Foo");
151153
}
152154

@@ -160,7 +162,9 @@ public void TestFindByDynamicSingleColumnNull()
160162
[Test]
161163
public void TestFindAllByDynamicSingleColumnNull()
162164
{
165+
#pragma warning disable 168
163166
IEnumerable<MyTable> result = _db.MyTable.FindAllByColumn1(null).ToList<MyTable>();
167+
#pragma warning restore 168
164168
GeneratedSqlIs("select [dbo].[MyTable].[Column1] from [dbo].[MyTable] where [dbo].[MyTable].[Column1] is null");
165169
}
166170

@@ -183,15 +187,15 @@ public void TestFindByWithAnonymousObjectNullValue()
183187
public void TestFindWithLike()
184188
{
185189
_db.Users.Find(_db.Users.Name.Like("Foo"));
186-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[name] like @p1");
190+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[name] like @p1");
187191
Parameter(0).Is("Foo");
188192
}
189193

190194
[Test]
191195
public void TestFindWithNotLike()
192196
{
193197
_db.Users.Find(_db.Users.Name.NotLike("Foo"));
194-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where [dbo].[Users].[name] not like @p1");
198+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where [dbo].[Users].[name] not like @p1");
195199
Parameter(0).Is("Foo");
196200
}
197201

@@ -233,7 +237,7 @@ public void TestFindAllByNamedParameterTwoColumns()
233237
public void TestFindByDynamicTwoColumns()
234238
{
235239
_db.Users.FindByNameAndPassword("Foo", "secret");
236-
GeneratedSqlIs("select " + usersColumns + " from [dbo].[Users] where ([dbo].[Users].[name] = @p1 and [dbo].[Users].[password] = @p2)");
240+
GeneratedSqlIs("select " + UsersColumns + " from [dbo].[Users] where ([dbo].[Users].[name] = @p1 and [dbo].[Users].[password] = @p2)");
237241
Parameter(0).Is("Foo");
238242
Parameter(1).Is("secret");
239243
}

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

Lines changed: 40 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
namespace Simple.Data.IntegrationTest.Query
22
{
3-
using System;
43
using Mocking.Ado;
54
using NUnit.Framework;
65

@@ -9,9 +8,13 @@ public class WithTest : DatabaseIntegrationContext
98
{
109
protected override void SetSchema(MockSchemaProvider schemaProvider)
1110
{
11+
// ReSharper disable CoVariantArrayConversion
1212
schemaProvider.SetTables(new[] { "dbo", "Employee", "BASE TABLE" },
1313
new[] { "dbo", "Department", "BASE TABLE" },
1414
new[] { "dbo", "Activity", "BASE TABLE" },
15+
new[] { "dbo", "Customer", "BASE TABLE" },
16+
new[] { "dbo", "Order", "BASE TABLE" },
17+
new[] { "dbo", "Note", "BASE TABLE" },
1518
new[] { "dbo", "Activity_Join", "BASE TABLE" },
1619
new[] { "dbo", "Location", "BASE_TABLE" });
1720

@@ -21,6 +24,14 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
2124
new[] { "dbo", "Employee", "DepartmentId" },
2225
new[] { "dbo", "Department", "Id" },
2326
new[] { "dbo", "Department", "Name" },
27+
new[] { "dbo", "Customer", "Id" },
28+
new[] { "dbo", "Customer", "Name" },
29+
new[] { "dbo", "Order", "Id" },
30+
new[] { "dbo", "Order", "CustomerId" },
31+
new[] { "dbo", "Order", "Description" },
32+
new[] { "dbo", "Note", "Id" },
33+
new[] { "dbo", "Note", "CustomerId" },
34+
new[] { "dbo", "Note", "Text" },
2435
new[] { "dbo", "Activity", "ID" },
2536
new[] { "dbo", "Activity", "Description" },
2637
new[] { "dbo", "Activity_Join", "ID_Activity" },
@@ -30,13 +41,20 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
3041
);
3142

3243
schemaProvider.SetPrimaryKeys(new object[] {"dbo", "Employee", "Id", 0},
33-
new object[] {"dbo", "Department", "Id", 0});
44+
new object[] {"dbo", "Department", "Id", 0},
45+
new object[] {"dbo", "Customer", "Id", 0},
46+
new object[] {"dbo", "Order", "Id", 0},
47+
new object[] {"dbo", "Note", "Id", 0}
48+
);
3449

3550
schemaProvider.SetForeignKeys(
3651
new object[] { "FK_Employee_Department", "dbo", "Employee", "DepartmentId", "dbo", "Department", "Id", 0 },
3752
new object[] { "FK_Activity_Join_Activity", "dbo", "Activity_Join", "ID_Activity", "dbo", "Activity", "ID", 0 },
38-
new object[] { "FK_Activity_Join_Location", "dbo", "Activity_Join", "ID_Location", "dbo", "Location", "ID", 0 }
53+
new object[] { "FK_Activity_Join_Location", "dbo", "Activity_Join", "ID_Location", "dbo", "Location", "ID", 0 },
54+
new object[] { "FK_Order_Customer", "dbo", "Order", "CustomerId", "dbo", "Customer", "Id", 0 },
55+
new object[] { "FK_Note_Customer", "dbo", "Note", "CustomerId", "dbo", "Customer", "Id", 0 }
3956
);
57+
// ReSharper restore CoVariantArrayConversion
4058
}
4159

4260
[Test]
@@ -195,6 +213,25 @@ public void MultipleWithClauseJustDoesEverythingYouWouldHope()
195213
GeneratedSqlIs(expectedSql);
196214
}
197215

216+
/// <summary>
217+
/// Test for multiple child tables...
218+
/// </summary>
219+
[Test]
220+
public void CustomerWithOrdersAndNotes()
221+
{
222+
const string expectedSql = "select [dbo].[customer].[id],[dbo].[customer].[name]," +
223+
"[dbo].[order].[id] as [__withn__orders__id],[dbo].[order].[customerid] as [__withn__orders__customerid],[dbo].[order].[description] as [__withn__orders__description]," +
224+
"[dbo].[note].[id] as [__withn__notes__id],[dbo].[note].[customerid] as [__withn__notes__customerid],[dbo].[note].[text] as [__withn__notes__text]" +
225+
" from [dbo].[customer]" +
226+
" left join [dbo].[order] on ([dbo].[customer].[id] = [dbo].[order].[customerid])" +
227+
" left join [dbo].[note] on ([dbo].[customer].[id] = [dbo].[note].[customerid])";
228+
229+
var q = _db.Customers.All().WithOrders().WithNotes();
230+
EatException(() => q.ToList());
231+
232+
GeneratedSqlIs(expectedSql);
233+
}
234+
198235
/// <summary>
199236
/// Test for issue #157
200237
/// </summary>

0 commit comments

Comments
 (0)