DbFunctionCall with Long Array as Parameter

Posts   
 
    
WillLeap
User
Posts: 1
Joined: 15-Aug-2018
# Posted on: 15-Aug-2018 19:59:08   

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
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Aug-2018 07:34:10   

Hi WillLeap,

The DBFunctionaCall calls the .ToString() method of each internal parameter to resolve the string, so it must be that the array object returns "System.Int64[]". My recommendation is that you expand the list yourself and pass it as a constant to the expression. Example:

var accountIds = new long[] { 1, 2, 3 };
var accountIdsAsString = string.Join(",", accountIds.Select(x => x.ToString()).ToArray());

var priorityExpression = new DbFunctionCall("CASE WHEN {0} IN (" + accountIdsAsString + ") THEN 1 ELSE 0 END", 
    new object[] { BatchFields.AccountId });
David Elizondo | LLBLGen Support Team