Skip to content

Commit 83a51ed

Browse files
ThatRendleThatRendle
committed
Complex update and With SQL tests
1 parent cda43bd commit 83a51ed

6 files changed

Lines changed: 98 additions & 12 deletions

File tree

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,10 @@ private void HandleWithClauses()
114114
}
115115
else
116116
{
117+
if (withClause.Type == WithType.NotSpecified)
118+
{
119+
InferWithType(withClause);
120+
}
117121
HandleWithClauseUsingNaturalJoin(withClause, relationTypeDict);
118122
}
119123
}
@@ -124,6 +128,22 @@ private void HandleWithClauses()
124128
}
125129
}
126130

131+
private void InferWithType(WithClause withClause)
132+
{
133+
var objectReference = withClause.ObjectReference;
134+
while (!ReferenceEquals(objectReference.GetOwner(), null))
135+
{
136+
var fromTable = _schema.FindTable(objectReference.GetName());
137+
var toTable = _schema.FindTable(objectReference.GetOwner().GetName());
138+
if (_schema.GetRelationType(fromTable.ActualName, toTable.ActualName) == RelationType.OneToMany)
139+
{
140+
withClause.Type = WithType.Many;
141+
return;
142+
}
143+
objectReference = objectReference.GetOwner();
144+
}
145+
}
146+
127147
private void HandleWithClauseUsingAssociatedJoinClause(Dictionary<ObjectReference, RelationType> relationTypeDict, WithClause withClause)
128148
{
129149
var joinClause =

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

Lines changed: 28 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -21,19 +21,22 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
2121
new[] { "dbo", "Employee", "DepartmentId" },
2222
new[] { "dbo", "Department", "Id" },
2323
new[] { "dbo", "Department", "Name" },
24-
new[] { "dbo", "Activity", "ID_Activity" },
25-
new[] { "dbo", "Activity", "ID_Trip" },
26-
new[] { "dbo", "Activity", "Activity_Time" },
27-
new[] { "dbo", "Activity", "Is_Public" },
24+
new[] { "dbo", "Activity", "ID" },
25+
new[] { "dbo", "Activity", "Description" },
2826
new[] { "dbo", "Activity_Join", "ID_Activity" },
2927
new[] { "dbo", "Activity_Join", "ID_Location" },
30-
new[] { "dbo", "Location", "ID_Location" }
28+
new[] { "dbo", "Location", "ID" },
29+
new[] { "dbo", "Location", "Address" }
3130
);
3231

3332
schemaProvider.SetPrimaryKeys(new object[] {"dbo", "Employee", "Id", 0},
3433
new object[] {"dbo", "Department", "Id", 0});
3534

36-
schemaProvider.SetForeignKeys(new object[] { "FK_Employee_Department", "dbo", "Employee", "DepartmentId", "dbo", "Department", "Id", 0 });
35+
schemaProvider.SetForeignKeys(
36+
new object[] { "FK_Employee_Department", "dbo", "Employee", "DepartmentId", "dbo", "Department", "Id", 0 },
37+
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 }
39+
);
3740
}
3841

3942
[Test]
@@ -113,6 +116,25 @@ public void SingleWithClauseUsingReferenceWithAliasShouldApplyAliasToSql()
113116
GeneratedSqlIs(expectedSql);
114117
}
115118

119+
[Test]
120+
public void SingleWithClauseUsingTwoStepReference()
121+
{
122+
const string expectedSql = "select "+
123+
"[dbo].[activity].[id],"+
124+
"[dbo].[activity].[description],"+
125+
"[dbo].[location].[id] as [__withn__location__id]," +
126+
"[dbo].[location].[address] as [__withn__location__address]" +
127+
" from [dbo].[activity] "+
128+
"left join [dbo].[activity_join] on ([dbo].[activity].[id] = [dbo].[activity_join].[id_activity]) "+
129+
"left join [dbo].[location] on ([dbo].[location].[id] = [dbo].[activity_join].[id_location])";
130+
131+
var q = _db.Activity.All().With(_db.Activity.ActivityJoin.Location);
132+
133+
EatException(() => q.ToList());
134+
135+
GeneratedSqlIs(expectedSql);
136+
}
137+
116138
[Test]
117139
public void SingleWithClauseUsingExplicitJoinShouldApplyAliasToSql()
118140
{

‎Simple.Data.BehaviourTest/UpdateTest.cs‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,9 +11,9 @@ public class UpdateTest : DatabaseIntegrationContext
1111
{
1212
protected override void SetSchema(MockSchemaProvider schemaProvider)
1313
{
14-
schemaProvider.SetTables(new[] { "dbo", "Users", "BASE TABLE" },
15-
new[] { "dbo", "UserHistory", "BASE TABLE"},
16-
new[] { "dbo", "AnnoyingMaster", "BASE TABLE"},
14+
schemaProvider.SetTables(new object[] { "dbo", "Users", "BASE TABLE" },
15+
new object[] { "dbo", "UserHistory", "BASE TABLE"},
16+
new object[] { "dbo", "AnnoyingMaster", "BASE TABLE"},
1717
new[] { "dbo", "AnnoyingDetail", "BASE TABLE"},
1818
new[] {"dbo", "USER_TABLE", "BASE TABLE"});
1919

‎Simple.Data.SqlTest/QueryTest.cs‎

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -332,7 +332,31 @@ public void WithClauseShouldPreselectDetailTableAsCollection()
332332
}
333333

334334
[Test]
335-
public void WithClauseWithJoinCriteriaShouldPreselectDetailTableAsCollection()
335+
public void FindAllWithClauseWithJoinCriteriaShouldPreselectDetailTableAsCollection()
336+
{
337+
var db = DatabaseHelper.Open();
338+
var result = db.Customers.FindAllByCustomerId(1).With(db.Customers.Orders.OrderItems).FirstOrDefault() as IDictionary<string, object>;
339+
Assert.IsNotNull(result);
340+
Assert.Contains("OrderItems", (ICollection)result.Keys);
341+
var orderItems = result["OrderItems"] as IList<IDictionary<string, object>>;
342+
Assert.IsNotNull(orderItems);
343+
Assert.AreEqual(1, orderItems.Count);
344+
}
345+
346+
[Test]
347+
public void GetWithClauseWithJoinCriteriaShouldPreselectDetailTableAsCollection()
348+
{
349+
var db = DatabaseHelper.Open();
350+
var result = db.Customers.With(db.Customers.Orders.OrderItems).Get(1) as IDictionary<string, object>;
351+
Assert.IsNotNull(result);
352+
Assert.Contains("OrderItems", (ICollection)result.Keys);
353+
var orderItems = result["OrderItems"] as IList<IDictionary<string, object>>;
354+
Assert.IsNotNull(orderItems);
355+
Assert.AreEqual(1, orderItems.Count);
356+
}
357+
358+
[Test]
359+
public void WithClauseWithTwoStepShouldPreselectManyToManyTableAsCollection()
336360
{
337361
var db = DatabaseHelper.Open();
338362
var result = db.Customers.FindAll(db.Customers.Order.OrderId == 1).WithOrders().FirstOrDefault() as IDictionary<string, object>;

‎Simple.Data.SqlTest/UpdateTests.cs‎

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,24 @@ public void TestUpdateWithVarBinaryMaxColumn()
8787
Assert.IsTrue(newData.SequenceEqual(blob.Data));
8888
}
8989

90+
[Test]
91+
public void TestUpdateWithJoinCriteria()
92+
{
93+
var db = DatabaseHelper.Open();
94+
db.Customers.UpdateAll(db.Customers.Orders.OrderId == 1, Name: "Updated");
95+
var customer = db.Customers.Get(1);
96+
Assert.AreEqual("Updated", customer.Name);
97+
}
98+
99+
[Test]
100+
public void TestUpdateWithJoinCriteriaOnCompoundKeyTable()
101+
{
102+
var db = DatabaseHelper.Open();
103+
db.CompoundKeyMaster.UpdateAll(db.CompoundKeyMaster.CompoundKeyDetail.Value == 1, Description: "Updated");
104+
var record = db.CompoundKeyMaster.Get(1, 1);
105+
Assert.AreEqual("Updated", record.Description);
106+
}
107+
90108
[Test]
91109
public void ToListShouldExecuteQuery()
92110
{
@@ -97,7 +115,8 @@ public void ToListShouldExecuteQuery()
97115
customer.Address = "Updated";
98116
}
99117

100-
db.Customers.Update(customers);
118+
Assert.DoesNotThrow(() =>
119+
db.Customers.Update(customers));
101120
}
102121
}
103122
}

‎Simple.Data/WithClause.cs‎

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

99
public WithClause(ObjectReference objectReference) : this(objectReference, WithType.NotSpecified)
1010
{
@@ -33,6 +33,7 @@ public WithMode Mode
3333
public WithType Type
3434
{
3535
get { return _type; }
36+
set { _type = value; }
3637
}
3738

3839
public ObjectReference ObjectReference

0 commit comments

Comments
 (0)