Btw, am I correct in the assumption that the whole case statement is not necessary ?
->
Update SpecialPrice
set PeriodicRenewalDate = null,
ExpiryDate = (CASE WHEN PeriodicRenewalDate IS NOT NULL THEN NULL ELSE ExpiryDate END)
from SpecialPrice
where SpecialPrice.Id = @Id1
is equal to:
Update SpecialPrice
set PeriodicRenewalDate = null,
from SpecialPrice
where SpecialPrice.Id = @Id1
as you always set PeriodicRenewalDate to null in this query for the matching specialprice entities, so "CASE WHEN PeriodicRenewalDate IS NOT NULL THEN" is never true, unless that 'PeriodicRenewalDate' value is the one of the original row which is being updated. (I haven't checked the SQL spec of your particular database that thorough). But it's also about semantics: PeriodicRenewalDate is set to null, for all matching entities, and ExpiryDate has to be set to null if PeriodicRenewalDate wasn't null before the set, otherwise it should be kept?
Why not move the case predicate to the where so the query becomes:
Update SpecialPrice
set PeriodicRenewalDate = null,
ExpiryDate = NULL
from SpecialPrice
where SpecialPrice.Id = @Id1
and PeriodicRenewalDate IS NOT NULL
More efficient and less complex code.
Still, the query should work, so I'll look into reproducing it.
(edit) also can't reproduce it:
OrderEntity order = new OrderEntity();
order.Customer = customer;
order.TestRunId = _testRunID;
order.OrderDate = DateTime.Now;
order.RequiredDate = DateTime.Now;
order.ShippedDate = DateTime.Now;
Assert.IsTrue(adapter.SaveEntity(customer));
// update order directly using a case statement.
RelationPredicateBucket orderFilter = new RelationPredicateBucket();
orderFilter.PredicateExpression.Add(OrderFields.OrderId == order.OrderId);
OrderEntity orderUpdater = new OrderEntity();
orderUpdater.RequiredDate = null;
orderUpdater.Fields[(int)OrderFieldIndex.ShippedDate].ExpressionToApply = new DbFunctionCall(
"CASE WHEN {0} IS NOT NULL THEN NULL ELSE {1} END",
new object[] { OrderFields.RequiredDate, OrderFields.ShippedDate});
Assert.AreEqual(1, adapter.UpdateEntitiesDirectly(orderUpdater, orderFilter));
works, query is:
UPDATE [LLBLGenProUnitTest].[dbo].[Order]
SET [RequiredDate]=@RequiredDate,
[ShippedDate]=
CASE WHEN [LLBLGenProUnitTest].[dbo].[Order].[RequiredDate] IS NOT NULL THEN NULL
ELSE [LLBLGenProUnitTest].[dbo].[Order].[ShippedDate]
END
WHERE ( ( ( [LLBLGenProUnitTest].[dbo].[Order].[OrderID] = @OrderId1)))
As expected.
Using build 01162009