I want to create a dynamic list for the following SQL

SELECT   SUM(ISNULL(dd.DebitValue, 0) - ISNULL(dd.CreditValue, 0)) AS AmountBalance
FROM         dbo.GLVoucherHeader AS cc INNER JOIN
                      dbo.GLVoucherDetail AS dd ON cc.VoucherID = dd.VoucherID AND cc.Currency = dd.CurrencyID

I tried the following code but ofcourse it does not work

ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(GLVoucherDetailFields.DebitValue, 0,AggregateFunction.Sum);
            fields[0].ExpressionToApply = new DbFunctionCall("ISNULL({0},0)", new object[] { GLVoucherDetailFields.DebitValue });

            fields.DefineField(GLVoucherDetailFields.CreditValue, 1,AggregateFunction.Sum);
            fields[1].ExpressionToApply = new DbFunctionCall("ISNULL({0},0)", new object[] { GLVoucherDetailFields.CreditValue });

            fields.DefineField(new EntityField2("TotalCost",(fields[0]  - fields[1])),2) ;
            fields[2].ExpressionToApply = new DbFunctionCall("ISNULL({0},0)", new object[] { fields[2] });

The issue here is how refernce fields[0] and fields[1] in the expression for fields[2]???

SELECT   SUM(ISNULL(dd.DebitValue, 0) - ISNULL(dd.CreditValue, 0)) AS AmountBalance

Hi mohamed, I would do that this way:

ResultsetFields fields = new ResultsetFields(1);

EntityField2 isnullDebitValue = GLVoucherDetailFields.DebitValue.SetExpression(
    new DbFunctionCall("ISNULL({0},0)", new object[] { GLVoucherDetailFields.DebitValue}));

EntityField2 isnullCreditValue = GLVoucherDetailFields.CreditValue.SetExpression(
    new DbFunctionCall("ISNULL({0},0)", new object[] { GLVoucherDetailFields.CreditValue }));

fields.DefineField(GLVoucherDetailFields.CreditValue
     .SetExpression(isnullDebitValue - isnullCreditValue), 0, "TotalPrice", AggregateFunction.Sum);