Find purchase orders by an OrderForm meta field in EPiServer Commerce

If you've added a meta field to an OrderForm, you can use the OrderContext.Current.FindPurchaseOrders method to find all purchase orders with a given value in that meta field. However, figuring out what the SqlWhereClause should be might not be completely straight forward, so I thought I'd share a solution.

I've added a meta field to the OrderForm called OrderStatus. This meta field will then show up as a new column in the OrderFormEx table in the Commerce database:

What if I want to find all purchase orders where OrderStatus is NULL? The following code would do the trick:

var searchOptions = new OrderSearchOptions
{
   CacheResults = false,
   Classes = new StringCollection { "PurchaseOrder" },
   Namespace = "Mediachase.Commerce.Orders"
};

OrderSearchParameters parameters = new OrderSearchParameters()
{
   SqlWhereClause = "OrderGroupId IN (SELECT OrderGroupId FROM OrderForm WHERE OrderFormId IN (SELECT ObjectId FROM OrderFormEx WHERE OrderStatus IS NULL))"
};

PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

If you want to know more about how this works, you should check out EPiServer Commerce Order Search Made Easy (not sure I agree on the "made easy" part, but that's a different story).

Hopefully this will save you some time!