When I try to do: trans.GetScalar(TranFields.Amt, IIf(TranFields.Debit = True, TranFields.Amt, -1 * TranFields.Amt), AggregateFunction.Sum)

I get: intellisense blue squiggly: entityfield cannot be converted to boolean.

Is iif not supported in expression?

Thanks,

Terry

Hi Terry. That expression isn't possible (at least in v2.5). Conditional must be done DB-side or after fetch. For example, you could use LLBLGen DBFunctionCalls:

// (assuming SQLServer, LLBLGen v2.5, Adapter, C#)
IEntityField2 expField = TranFields.Amt;
expField.ExpressionToApply = new DbFunctionCall(
    "CASE {0} WHEN '1' THEN {1} ELSE {2} END",
    new object[] { 
        TranFields.Debit, 
        TranFields.Amt,
        TranFields.Amt * -1 });

decimal result = 0;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    object tmp = adapter.GetScalar(expField, AggregateFunction.Sum);
    result = (tmp == null) ? 0 : (decimal)tmp;
}

(Edit) Or (if you are using v2.6), get ahead with LLBLGen Linq Support:

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);

    var q = (from t in metaData.Tran
    select (t.Debit == true) ? t.Amt :  (t.Amt * -1)).Sum();

    Console.WriteLine("the total: {0}", q);
}

Interesting response, since I used linq in vs2008 to solve the problem.

Since Linq to objects works already with llblgenpro, what is v2.6 in general gaining for us?

Thanks,

I recommend you to read this Frans Bouma's series of articles and the LLBL2LINQ docs.

tvoss wrote:

Interesting response, since I used linq in vs2008 to solve the problem.

Since Linq to objects works already with llblgenpro, what is v2.6 in general gaining for us? Thanks,

Terry, you're mixing things.

You typed: trans.GetScalar(TranFields.Amt, IIf(TranFields.Debit = True, TranFields.Amt, -1 * TranFields.Amt), AggregateFunction.Sum)

here, you use non-linq code, and you specify TranFields.Debit=true. However, TranFields.Debit isn't a value, it's a field. The expression you specified will be compiled (if it compiled) to a delegate, not an expression tree. (as there's no queryable). This means that the delegate isn't usable for database usage, as it's compiled IL.

In v2.6, with Linq and our Linq provider, you can use IIf inside a Linq query. Microsoft made it a bit fuzzy when what is executed with Linq, and you hit that wall as well: a Linq expression only compiles to an expression tree if its source of data is a Queryable (e.g. LinqMetaData.SomeEntity). If it's not, it's just a delegate, and only useable in-memory.