Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Using DB Function Call when Updating Entities Directly
 

Pages: 1
Bugs & Issues
Using DB Function Call when Updating Entities Directly
Page:1/1 

  Print all messages in this thread  
Poster Message
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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.

  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8109 posts
# 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.)?


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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.

  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# 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
  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# 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.
  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37866 posts
# Posted on: 11-Feb-2009 17:50:56.  
Will look into it.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37866 posts
# 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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37866 posts
# 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. ..


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.