Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Question about using expressions with UpdateEntitiesDirectly
 

Pages: 1
LLBLGen Pro Runtime Framework
Question about using expressions with UpdateEntitiesDirectly
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: 20-Aug-2009 15:29:43.  
Using Adapter pattern, compiling for .NET 3.5, version 2.6.09.0116

I'm having difficulty in using Expressions correctly. I'm trying to represent a SQL query that looks like this:
Code:
UPDATE Quote
set HasPriceUpdateDocs =
(CASE WHEN EXISTS(SELECT qdcpo.QuoteId FROM QuoteDocumentCorePriceOffset qdcpo WHERE qdcpo.QuoteId = @quoteId) THEN 1 ELSE 0 END)
WHERE
Quote.Id = @quoteId


Essentially I want to set HasPriceUpdateDocs to true if there's any rows in the QuoteDocumentCorePriceOffset table with a quote id equal to my parameter, setting it to false otherwise. This is easy enough to do with a GetScalar fetch then an update, but I'd prefer to do it in one go.

What I've got so far is:
Code:
var quoteUpdate = new QuoteEntity();
quoteUpdate.Fields[(int) QuoteFieldIndex.HasPriceUpdateDocs].ExpressionToApply = new ScalarQueryExpression(????);
var adapter = new DataAccessAdapter();
adapter.UpdateEntitiesDirectly(quoteUpdate, new RelationPredicateBucket(QuoteFields.Id == quoteId));


but I've no idea what should go in ScalarQueryExpression, especially since there's no Exists aggregation, only Count.

Thanks, Stephen
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 21-Aug-2009 08:59:27.  
Quote:
(CASE WHEN EXISTS(SELECT qdcpo.QuoteId FROM QuoteDocumentCorePriceOffset qdcpo WHERE qdcpo.QuoteId = @quoteId) THEN 1 ELSE 0 END)

That's complex but you can ry to set the ExpressionToApply to a DBFunctionCall, and then you can use the constant form, to supply most of the expression in text format.

Please check the following (some how relevant) thread:
http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16411


  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 21-Aug-2009 10:23:55.  
I'm not sure it has to utilize a CASE statement, it would be nice to evaluate the result of the EXISTS as a boolean directly. Is this possible?

I'm aware that DbFunctionCall would do the job, but I prefer not to use that unless I have to as I like to keep SQL strings out of the code, and the intention of much of LLBLGen seems directed at enabling the developer to represent these queries programmatically.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 21-Aug-2009 10:33:54.  
I can see no other way but doing it in code and thus query the database once and the issue the update statement next.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37804 posts
# Posted on: 21-Aug-2009 11:15:26.  
saggett wrote:
I'm not sure it has to utilize a CASE statement, it would be nice to evaluate the result of the EXISTS as a boolean directly. Is this possible?

I'm aware that DbFunctionCall would do the job, but I prefer not to use that unless I have to as I like to keep SQL strings out of the code, and the intention of much of LLBLGen seems directed at enabling the developer to represent these queries programmatically.

'CASE' is a statement which is pretty problematic to define in a query language also because it's not equal on every db and not every db supports it. So we left it as a DbFunctionCall() usage. You can use a ScalarQueryExpression as parameter for the DbFunctionCall, so you can define "SELECT qdcpo.QuoteId FROM QuoteDocumentCorePriceOffset qdcpo WHERE qdcpo.QuoteId = @quoteId" as the scalar query expression, and use "CASE WHEN EXISTS({0}) THEN 1 ELSE 0 END" as DbFunctionCall.

As you use a rather old runtime build, please upgrade to the latest runtime build as well.
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: 21-Aug-2009 11:49:03.  
Thanks, that worked. The code was:

Code:

//expression returns results if quote has price update docs, returns nothing otherwise
var scalarExp = new ScalarQueryExpression(QuoteDocumentCorePriceOffsetFields.QuoteId,
                                                     QuoteDocumentCorePriceOffsetFields.QuoteId == quoteId);
var quoteUpdate = new QuoteEntity();
quoteUpdate.Fields[(int) QuoteFieldIndex.HasPriceUpdateDocs].ExpressionToApply = new DbFunctionCall("CASE WHEN EXISTS({0}) THEN 1 ELSE 0 END", new[] {scalarExp});
var adapter = new DataAccessAdapter();
adapter.UpdateEntitiesDirectly(quoteUpdate, new RelationPredicateBucket(QuoteFields.Id == quoteId));


I'm curious though - how can the expression (scalarExp) be considered a scalar query when it returns a result set?


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37804 posts
# Posted on: 21-Aug-2009 12:36:26.  
It returns 1 value, so that's the 'scalar' query. the EXISTS is resolved to true if at least 1 value is there, hence it works. Regular Smiley

of course if you define a scalar query with a where clause which could return multiple values, it on paper would be a bit odd. When you fetch a scalar query, only the first value is used. When you use a scalar query expression in a where clause, and it returns more than 1 value, it's going to result in an error from the DB.
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.