I couldn't find a direct workaround, and also couldn't find a way to fix it. The problem is that the DbFunctionCall class doesn't know whether it's being re-used. Even if it does, it goes wrong for databases with anonymous parameters (Sybase ASE, IBM DB2, Access), as the query contains ? characters at the position where a parameter should be, meaning you can't re-use parameters in different parts of the query.
So fixing it won't work. There's a different solution however. Instead of DbFunctionCall, use a derived class of DbFunctionCall:
public class ReusableDbFunctionCall : DbFunctionCall
{
private string _queryText;
public ReusableDbFunctionCall(string functionName, object[] parameters) : base(functionName, parameters)
{
_queryText = string.Empty;
}
public override string ToQueryText(bool inHavingClause)
{
if(string.IsNullOrEmpty(_queryText))
{
_queryText = base.ToQueryText(inHavingClause);
}
else
{
// already processed, clear parameters, otherwise they're added again.
if(this.DatabaseParameters != null)
{
this.DatabaseParameters.Clear();
}
}
return _queryText;
}
}
Test:
[Test]
public void ReUseParametersInExpressionGroupByTest()
{
var caseExpression = new ReusableDbFunctionCall("CASE WHEN {0} IS NULL THEN {1} ELSE {2} END", new object[] { EmployeeFields.ReportsTo, 0, 1});
var fields = new ResultsetFields(2);
fields.DefineField(EmployeeFields.EmployeeId.SetExpression(caseExpression), 0);
fields.DefineField(new EntityField2("Amount", null, AggregateFunction.CountRow), 1);
var groupBy = new GroupByCollection(EmployeeFields.EmployeeId.SetExpression(caseExpression));
var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, results, null, 0, null, true, groupBy);
}
Assert.AreEqual(2, results.Rows.Count);
}