LLBLgen v3.1
LLBLgen Runtime Framework
MSSQL
I'm trying to use a DbFunctionCall in a SortExpression and one of my parameters is a long array. I tried the following but received "Error converting data type nvarchar to bigint." It seems like it's treated as a single parameter instead of expanding it out into a comma separated list. Is there a way to pass the accountIds array as a parameter so that it is expanded as a list of parameters?
The code:
var accountIds = new long[] { 1, 2, 3 };
var priorityExpression = new DbFunctionCall("CASE WHEN {0} IN ({1}) THEN 1 ELSE 0 END", new object[] { BatchFields.AccountId, accountIds });
var priorityField = BatchFields.AccountId.SetExpression(priorityExpression);
var prioritySortClause = new SortClause(priorityField, null, SortOperator.Ascending);
prioritySortClause.EmitAliasForExpressionAggregateField = false;
var sort = new SortExpression();
sort.Add(prioritySortClause);
sort.Add(BatchFields.DateCreated | SortOperator.Ascending);
The generated SQL:
ORDER BY CASE
WHEN [dbo].[Batch].[AccountID] IN (@p8)
THEN 1
ELSE 0
END ASC
,[dbo].[Batch].[DateCreated] ASC
Parameter: @p8 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "System.Int64[]".
What I'm hoping to have generated where @p8, @p9, and @p10 are 1, 2, and 3 respectively:
ORDER BY CASE
WHEN [dbo].[Batch].[AccountID] IN (@p8, @p9, @p10)
THEN 1
ELSE 0
END ASC
,[dbo].[Batch].[DateCreated] ASC