Question about using expressions with UpdateEntitiesDirectly

Posts   
 
    
saggett
User
Posts: 50
Joined: 12-Nov-2007
# 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:

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:

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

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 21-Aug-2009 08:59:27   

(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

saggett
User
Posts: 50
Joined: 12-Nov-2007
# 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.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
saggett
User
Posts: 50
Joined: 12-Nov-2007
# Posted on: 21-Aug-2009 11:49:03   

Thanks, that worked. The code was:


//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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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. simple_smile

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 | Lead developer LLBLGen Pro