Using version 2.6.08.0819, Adapter, C#, compiling for .NET 3.5.

I'm trying to achieve the following in an adapter.UpdateEntitiesDirectly call:

Update SpecialPrice
set PeriodicRenewalDate = null,
ExpiryDate = (CASE WHEN PeriodicRenewalDate IS NOT NULL THEN NULL ELSE ExpiryDate END)
from SpecialPrice
where SpecialPrice.Id = @Id1

In the above, I'm aiming to set ExpiryDate to null if PeriodicRenewalDate isn't null, but if PeriodicRenewalDate is null I'd like to avoid updating ExpiryDate. I've written the following:

var affectedSpecialPriceFilter = new RelationPredicateBucket();
affectedSpecialPriceFilter.PredicateExpression.Add(SpecialPriceFields.Id == specialPriceId);
var adapter = new DataAccessAdapter();
var spUpdate = new SpecialPriceEntity();
spUpdate.SensitivityId = newSens.Id;
spUpdate.PeriodicCalculationDate = null;
spUpdate.Fields[(int) SpecialPricesFieldIndex.ExpiryDate].ExpressionToApply = new DbFunctionCall(
                    "CASE WHEN {0} IS NOT NULL THEN NULL ELSE {1} END",
                    new object[] {SpecialPriceFields.PeriodicCalculationDate, SpecialPriceFields.ExpiryDate});
            worker.UpdateEntitiesDirectly(spUpdate, affectedSpecialPriceFilter);

But this results in the following SQL query:

UPDATE [UnivarQ].[dbo].[SpecialPrice] SET [PeriodicRenewalDate]=CASE WHEN [UnivarQ].[dbo].[SpecialPrice].[PeriodicRenewalDate] IS NOT NULL THEN NULL ELSE [UnivarQ].[dbo].[SpecialPrice].[ExpiryDate] END,[SensitivityId]=@SensitivityId WHERE ( ( ( [UnivarQ].[dbo].[SpecialPrice].[Id] = @Id1)))

What am I doing wrong?

Thanks for your help.

I don't see any wrong in the query, other that is different in this part versus your approximate SQL:

[SensitivityId]=@SensitivityId

But that is because you write this code:

spUpdate.SensitivityId = newSens.Id;

Could you please scpecify what is wrong (no expected rows updated, exception, etc.)?

In my intended query, two sets take place - one where PeriodicRenewalDate gets set to null, and another where ExpiryDate gets set to the result of the CASE statement.

In the query my code actually produced, only one set takes place, where PeriodicRenewalDate gets set to the result of the CASE statement, and ExpiryDate is not updated at all.

Slight mistake in my intended query, it is my intention for the Sensitivity column to be updated. So what I'm aiming at is:

Update SpecialPrice
Set PeriodicRenewalDate = null,
ExpiryDate = (CASE WHEN PeriodicRenewalDate IS NOT NULL THEN NULL ELSE ExpiryDate END),
SensitivityId = @Id2
from SpecialPrice
where SpecialPrice.Id = @Id1

In the query my code actually produced, only one set takes place, where PeriodicRenewalDate gets set to the result of the CASE statement, and ExpiryDate is not updated at all.

This sounds like a bug that was solved befpore, maybe you are using an old runtime library version, would you please try the latest build.

Also you may try this:

spUpdate.SetNewFieldValue((int)SpecialPriceFieldIndex.PeriodicCalculationDate, null);

Instead of:

spUpdate.PeriodicCalculationDate = null;

(EDIT) On a second thought it might just require you to set the

spUpdate.PeriodicCalculationDate = null;
spUpdate.PeriodicCalculationDate.IsChanged = true;

As explained here: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14469

I'm now doing:

var affectedSpecialPriceFilter = new RelationPredicateBucket();
affectedSpecialPriceFilter.PredicateExpression.Add(SpecialPriceFields.Id == specialPriceId);
var adapter = new DataAccessAdapter();
var spUpdate = new SpecialPriceEntity();
spUpdate.SensitivityId = newSens.Id;
spUpdate.Fields[(int) SpecialPriceFieldIndex.PeriodicCalculationDate].IsChanged = true;
spUpdate.Fields[(int) SpecialPriceFieldIndex.ExpiryDate].IsChanged = true;
spUpdate.PeriodicCalculationDate = null;
spUpdate.Fields[(int) SpecialPricesFieldIndex.ExpiryDate].ExpressionToApply = new DbFunctionCall(
                    "CASE WHEN {0} IS NOT NULL THEN NULL ELSE {1} END",
                    new object[] {SpecialPriceFields.PeriodicCalculationDate, SpecialPriceFields.ExpiryDate});
 worker.UpdateEntitiesDirectly(spUpdate, affectedSpecialPriceFilter);

And I now get:

UPDATE [UnivarQ].[dbo].[SpecialPrice]
SET [ExpiryDate]=@ExpiryDate,
[PeriodicRenewalDate]=CASE WHEN [UnivarQ].[dbo].[SpecialPrice].[PeriodicRenewalDate] IS NOT NULL THEN NULL ELSE [UnivarQ].[dbo].[SpecialPrice].[ExpiryDate] END,
[SensitivityId]=@SensitivityId 
WHERE
( ( ( [UnivarQ].[dbo].[SpecialPrice].[Id] = @Id1)))

So still not right - it's the wrong way round, ExpiryDate should be set to the result of the case statement and PeriodicRenewalDate should be set to null.

I can try upgrading to the latest version of LLBLGen if you think that'll resolve the issue.

Definitly you are using an old runtime library built.

And anyway I'd recommend you upgrade to the latest built, this will put us in the safe side while looking into your issue.

I've now upgraded to the latest version (2.6.09.0116) and regenerated the DAL, no change.

Will look into it.

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

You're right:

Update SpecialPrice
set PeriodicRenewalDate = null,
ExpiryDate = NULL
from SpecialPrice
where SpecialPrice.Id = @Id1
and PeriodicRenewalDate IS NOT NULL

Is a simpler form of the query I'm looking for, I don't know why I didn't think of it before.

I hope I've identified a genuine bug for you, that way my inefficient query will benefited LLBLGen's code if not my own.

saggett wrote:

You're right:

Update SpecialPrice
set PeriodicRenewalDate = null,
ExpiryDate = NULL
from SpecialPrice
where SpecialPrice.Id = @Id1
and PeriodicRenewalDate IS NOT NULL

Is a simpler form of the query I'm looking for, I don't know why I didn't think of it before.

I hope I've identified a genuine bug for you, that way my inefficient query will benefited LLBLGen's code if not my own.

Unfortunately, as I posted above, my testquery ran fine... so I'm not sure why the query gave the wrong SQL statement in your case. ..