Skip to content

Commit cda43bd

Browse files
ThatRendleThatRendle
committed
Added support for joined criteria in Updates where table has multi-column primary key
1 parent f73478b commit cda43bd

2 files changed

Lines changed: 55 additions & 1 deletion

File tree

‎Simple.Data.Ado/UpdateHelper.cs‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,10 @@ public ICommandBuilder GetUpdateCommand(string tableName, IDictionary<string, ob
3636
{
3737
whereStatement = CreateWhereInStatement(criteria, table);
3838
}
39+
else if (table.PrimaryKey.Length > 1)
40+
{
41+
whereStatement = CreateWhereExistsStatement(criteria, table);
42+
}
3943
}
4044
else
4145
{
@@ -63,6 +67,29 @@ private string CreateWhereInStatement(SimpleExpression criteria, Table table)
6367
return string.Format("{0} IN ({1})", keyColumn.QualifiedName, inClauseBuilder.Text);
6468
}
6569

70+
private string CreateWhereExistsStatement(SimpleExpression criteria, Table table)
71+
{
72+
var inClauseBuilder = new CommandBuilder(_schema);
73+
inClauseBuilder.Append(string.Join(" ",
74+
new Joiner(JoinType.Inner, _schema).GetJoinClauses(
75+
new ObjectName(table.Schema, table.ActualName), criteria)));
76+
inClauseBuilder.Append(" where ");
77+
inClauseBuilder.Append(_expressionFormatter.Format(criteria));
78+
var updateJoin = _schema.QuoteObjectName("_updatejoin");
79+
var whereClause = new StringBuilder(string.Format("SELECT 1 FROM {0} [_updatejoin] ", table.QualifiedName));
80+
whereClause.Append(inClauseBuilder.Text.Replace(table.QualifiedName, updateJoin));
81+
whereClause.Append(" AND (");
82+
bool appendAnd = false;
83+
foreach (var column in table.PrimaryKey.AsEnumerable().Select(table.FindColumn))
84+
{
85+
if (appendAnd) whereClause.Append(" AND ");
86+
whereClause.AppendFormat("{0}.{1} = {2}", updateJoin, column.QuotedName, column.QualifiedName);
87+
appendAnd = true;
88+
}
89+
whereClause.Append(")");
90+
return string.Format("EXISTS ({0})", whereClause);
91+
}
92+
6693
private string GetUpdateClause(Table table, IEnumerable<KeyValuePair<string, object>> data)
6794
{
6895
var setClause = string.Join(", ",

‎Simple.Data.BehaviourTest/UpdateTest.cs‎

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,8 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
1313
{
1414
schemaProvider.SetTables(new[] { "dbo", "Users", "BASE TABLE" },
1515
new[] { "dbo", "UserHistory", "BASE TABLE"},
16+
new[] { "dbo", "AnnoyingMaster", "BASE TABLE"},
17+
new[] { "dbo", "AnnoyingDetail", "BASE TABLE"},
1618
new[] {"dbo", "USER_TABLE", "BASE TABLE"});
1719

1820
schemaProvider.SetColumns(new object[] { "dbo", "Users", "Id", true },
@@ -22,16 +24,30 @@ protected override void SetSchema(MockSchemaProvider schemaProvider)
2224
new[] { "dbo", "UserHistory", "Id" },
2325
new[] { "dbo", "UserHistory", "UserId" },
2426
new[] { "dbo", "UserHistory", "LastSeen" },
27+
new[] { "dbo", "AnnoyingMaster", "Id1" },
28+
new[] { "dbo", "AnnoyingMaster", "Id2" },
29+
new[] { "dbo", "AnnoyingMaster", "Text" },
30+
new[] { "dbo", "AnnoyingDetail", "Id" },
31+
new[] { "dbo", "AnnoyingDetail", "MasterId1" },
32+
new[] { "dbo", "AnnoyingDetail", "MasterId2" },
33+
new[] { "dbo", "AnnoyingDetail", "Value" },
2534
new object[] { "dbo", "USER_TABLE", "ID", true },
2635
new[] { "dbo", "USER_TABLE", "NAME" },
2736
new[] { "dbo", "USER_TABLE", "PASSWORD" },
2837
new[] { "dbo", "USER_TABLE", "AGE" });
2938

3039
schemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 },
3140
new object[] { "dbo", "UserHistory", "Id", 0 },
41+
new object[] { "dbo", "AnnoyingMaster", "Id1", 0 },
42+
new object[] { "dbo", "AnnoyingMaster", "Id2", 1 },
43+
new object[] { "dbo", "AnnoyingDetail", "Id", 0 },
3244
new object[] { "dbo", "USER_TABLE", "ID", 0 });
3345

34-
schemaProvider.SetForeignKeys(new object[] { "FK_UserHistory_User", "dbo", "UserHistory", "UserId", "dbo", "Users", "Id", 0 });
46+
schemaProvider.SetForeignKeys(
47+
new object[] { "FK_UserHistory_User", "dbo", "UserHistory", "UserId", "dbo", "Users", "Id", 0 },
48+
new object[] { "FK_AnnoyingDetail_AnnoyingMaster", "dbo", "AnnoyingDetail", "MasterId1", "dbo", "AnnoyingMaster", "Id1", 0 },
49+
new object[] { "FK_AnnoyingDetail_AnnoyingMaster", "dbo", "AnnoyingDetail", "MasterId2", "dbo", "AnnoyingMaster", "Id2", 1 }
50+
);
3551
}
3652

3753
[Test]
@@ -321,6 +337,17 @@ public void TestUpdateWithCriteriaWithNaturalJoin()
321337
Parameter(1).Is(yearAgo);
322338
}
323339

340+
[Test]
341+
public void TestUpdateWithCriteriaWithNaturalJoinOnCompoundKeyTable()
342+
{
343+
_db.AnnoyingMaster.UpdateAll(_db.AnnoyingMaster.AnnoyingDetail.Value < 42, Text: "Really annoying");
344+
GeneratedSqlIs("update [dbo].[AnnoyingMaster] set [Text] = @p1 where exists " +
345+
"(select 1 from [dbo].[AnnoyingMaster] [_updatejoin] join [dbo].[AnnoyingDetail] on ([_updatejoin].[Id1] = [dbo].[AnnoyingDetail].[MasterId1] and [_updatejoin].[Id2] = [dbo].[AnnoyingDetail].[MasterId2]) "+
346+
"where [dbo].[AnnoyingDetail].[Value] < @p2 and ([_updatejoin].[Id1] = [dbo].[AnnoyingMaster].[Id1] and [_updatejoin].[Id2] = [dbo].[AnnoyingMaster].[Id2]))");
347+
Parameter(0).Is("Really annoying");
348+
Parameter(1).Is(42);
349+
}
350+
324351
[Test]
325352
public void TestUpdateWithCriteriaAndDictionary()
326353
{

0 commit comments

Comments
 (0)