Forum:  Bugs & Issues

Thread:  Using DB Function Call when Updating Entities Directly


saggett (User)   Posted on: 09-Feb-2009 18:07:36.
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:

Code:

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:

Code:

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:

Code:

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.

daelmo (Support Team)   Posted on: 10-Feb-2009 03:38:03.
I don't see any wrong in the query, other that is different in this part versus your approximate SQL:
Quote:
[SensitivityId]=@SensitivityId

But that is because you write this code:
Code:
spUpdate.SensitivityId = newSens.Id;


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


saggett (User)   Posted on: 10-Feb-2009 10:15:00.
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.

saggett (User)   Posted on: 10-Feb-2009 10:18:09.
Slight mistake in my intended query, it is my intention for the Sensitivity column to be updated. So what I'm aiming at is:

Code:

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


Walaa (Support Team)   Posted on: 10-Feb-2009 11:27:32.
Quote:
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:
Code:
spUpdate.SetNewFieldValue((int)SpecialPriceFieldIndex.PeriodicCalculationDate, null);

Instead of:
Quote:
spUpdate.PeriodicCalculationDate = null;



(EDIT)
On a second thought it might just require you to set the
Code:
spUpdate.PeriodicCalculationDate = null;
spUpdate.PeriodicCalculationDate.IsChanged = true;

As explained here:
http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14469
saggett (User)   Posted on: 11-Feb-2009 10:31:29.
I'm now doing:

Code:

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:

Code:

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.


Walaa (Support Team)   Posted on: 11-Feb-2009 12:15:43.
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.
saggett (User)   Posted on: 11-Feb-2009 15:08:37.
I've now upgraded to the latest version (2.6.09.0116) and regenerated the DAL, no change.

Otis (LLBLGen Pro Team)   Posted on: 11-Feb-2009 17:50:56.
Will look into it.
Otis (LLBLGen Pro Team)   Posted on: 12-Feb-2009 11:31:07.
Btw, am I correct in the assumption that the whole case statement is not necessary ?

->

Code:

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:
Code:
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:
Code:
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:
Code:

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:
Code:

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


saggett (User)   Posted on: 12-Feb-2009 15:18:50.
You're right:

Code:

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. Regular Smiley
Otis (LLBLGen Pro Team)   Posted on: 12-Feb-2009 17:52:07.
saggett wrote:
You're right:

Code:

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. Regular Smiley

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. ..