Hi,

I want to update "CustomerBalance.HistoryHighestBalance" if the calculated value is more than existing database value.

To avoid an extra read for an existing value from the database, I tried the following code using an expression (using Linqpad):

var value = 1000;  // Calculated Value

var entity = new CustomerBalanceEntity();
entity.Fields[CustomerBalanceFields.HistoryHighestBalance.FieldIndex].SetExpression (Functions.IIF(CustomerBalanceFields.HistoryHighestBalance > value, CustomerBalanceFields.HistoryHighestBalance, value));

this.AdapterToUse.UpdateEntitiesDirectly(entity, new RelationPredicateBucket(CustomerBalanceFields.CustomerId == 1));

I was expecting to generate "CASE WHEN END" statement to SQL Server, but it throws an internal error as follow:

at SD.LLBLGen.Pro.QuerySpec.FunctionMappingExpression.SD.LLBLGen.Pro.ORMSupportClasses.IExpression.get_LeftOperand()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IExpression expression)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IEntityField2 field)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.GetFieldPersistenceInfos(IEntity2 entity)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.UpdateEntitiesDirectly(IEntity2 entityWithNewValues, IRelationPredicateBucket filterBucket)
   at UserQuery.RunUserAuthoredQuery()

LLBLGen v3.5.12.317 Adapter Model.

If I write DbFunctionCall instead of Functions.IIF method, the above code works as expected, but I prefer to use Functions.IIF for readability purpose.

Please let me know if it's possible to use it.

Kind Regards.

RelationPredicateBucket bucket = 
    new RelationPredicateBucket(CustomerBalance.HistoryHighestBalance > value);
var entity = new CustomerBalanceEntity();

entity.HistoryHighestBalance = value;

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    int amountUpdated = adapter.UpdateEntitiesDirectly(entity, bucket);
}

Hi Walaa,

Thanks for your response.

The expression can not be moved to "Where" clause because there are 10 fields on CustomerBalance entity - and some needs to be updated by value, but others fields may not be changed...

For example: - The entity contains "Activity" field - which updates all the time by "calculated value". - The entity contains "HistoryHighestBalance" field - which updates only when calculated balance is more than existing database value in the same field. - The entity contains "HistoryHighestActivityDate" field - which updates only when entered date is more than existing database value in the same field.

For example the following code updates "Activity" value always, but HistoryHighestBalance with IIF condition only.

var value = 1000; // Calculated Value
var entity = new CustomerBalanceEntity();

entity.Fields[CustomerBalanceFields.HistoryHighestBalance.FieldIndex].SetExpression (Functions.IIF(CustomerBalanceFields.HistoryHighestBalance > value, CustomerBalanceFields.HistoryHighestBalance, value));

entity.Fields[CustomerBalanceFields.Activity.FieldIndex].SetExpression(CustomerBalanceFields.Activity + value);

this.AdapterToUse.UpdateEntitiesDirectly(entity, new RelationPredicateBucket(CustomerBalanceFields.CustomerId == 1));

The code can be moved to separate SQL statements, but there are 5 other "HistoryHighest*" fields on the same entity and it will cause multiple database calls...

It's better to include "HistoryHighest*" fields on the same update statement to avoid multiple database calls. Currently, I manually added "DbFunctionCall" with "CASE WHEN END" statement, and it works as expected

I am not sure why Functions.IIF does not work in this instance. Please advise me.

Kind Regards,

If I am right, the Functions.IIF is a function mapping for QuerySpec, which is not directly convertible to the expression. I think there is conversion behind but when the framework does the expansion. My opinion is: use QuerySpec if you want to use Functions.IIF, use DBFunctionCall if you use the LLBLGen API.

Hi,

Yes, we are using QuerySpec for SELECT statements, and used "Functions.IIF" in few IPredicate

My opinion is: use QuerySpec if you want to use Functions.IIF, use DBFunctionCall if you use the LLBLGen API.

However, I am not sure how to use QuerySpec for database UPDATE statements. Is it possible to update an entity directly via QuerySpec?

I think there is conversion behind but when the framework does the expansion.

Is there any way to call the same Functions conversion, so we do not have to write manual SQL?

Kind Regards,

Functions.IIF converts to a CASE statement native to the DB at hand. So in update statements, you should use a DbFunctioncall with a CASE statement. In theory you could grab the function mapping Functions.IIF maps on, by first obtaining the FunctionMappingStore of the DynamicQueryEngine instance you're using, then by calling functionMappingStore.CreateKeyValue("IIF", typeof(object).FullName, 3);

this key you can then pass to FindMapping which will return the functionmapping and the string to produce a DbFunctionCall.