OrderContext.FindPurchaseOrdersByStatus gives SQL Error

Jeroen van Lieshout

Vote:

Yesterday we installed the latest version of Episerver Commerce. It seems that the way how FindPurchaseOrdersByStatus method in OrderContext works has been changed. Already dived into the decompiled code and found out that there is a bug in the query which is executed. The single quotes are missing in the IN operator in the Where Clause, which causes SQL to return the error "Invalid column name 'InProgress'" when filtering on all orders which are InProgress. Can this be logged as bug?

#199469

Edited, Nov 28, 2018 11:22

Quan Mai

Vote:

Sure thing. (This has something to do with me, actually). I will file a bug and will work on it soon. Thanks for bringing it to our attention.

#199470

Nov 28, 2018 11:36

Quan Mai

Vote:

In the meanwhile if you need a work around, please use this snippet:

 OrderSearchOptions options = new OrderSearchOptions { Namespace = OrderNamespace };
            options.Classes.Add(PurchaseOrderClassType);
            options.RecordsToRetrieve = int.MaxValue;

            OrderSearchParameters parameters = new OrderSearchParameters();

            if (statuses.Length > 0)
            {
                parameters.SqlWhereClause = $" Status IN ({string.Join(",", statuses.Select(s => $"'{s}'"))})";
            }

            return OrderContext.Current.Search<PurchaseOrder>(parameters, options, out int _);

#199476

Nov 28, 2018 12:06

Jeroen van Lieshout

Vote:

We indeed implemented something similar using a wrapper class which we use for testability in mocked objects. Thanks for the workaround anyway!

#199484

Nov 28, 2018 14:04

Try our conversational search powered by Generative AI!

OrderContext.FindPurchaseOrdersByStatus gives SQL Error