If you remove dealerMetaWhere, and just only have poMetaWhere as the only condition, does it work with multiple erpOrderNums ?

Without dealerMetaWhere it returns empty collection in both cases also.

Mind to post the entire code here (with only poMetaWhere condition) ?

No, no. Look.

public IEnumerable<PurchaseOrder> GetOrdersByERPOrderNums(Guid? dealershipId, List<String> erpOrderNums)
{
    int orderCount;
    var parameters = new OrderSearchParameters();  
    var poMetaWhere = string.Format("META.{0} in ('{1}')", MetaFieldErpOrderNumber, String.Join("','", erpOrderNums));

    //var stringBuilder = new System.Text.StringBuilder();
    //for(var i = 0; i < erpOrderNums.Count;i++)
    //{
    //    stringBuilder.Append(string.Format("META.{0} = '{1}'", MetaFieldErpOrderNumber, erpOrderNums[i]));

    //    if(i + 1 == erpOrderNums.Count)
    //    {
    //        break;
    //    }

    //    stringBuilder.Append(" OR ");
    //}

    //var poMetaWhere = stringBuilder.ToString();

    parameters.SqlMetaWhereClause = poMetaWhere;
    parameters.OrderByClause = "OrderGroupId DESC";
    var classes = new StringCollection();
    classes.Add("PurchaseOrder");
    var options = new OrderSearchOptions
    {
        CacheResults = true,
        RecordsToRetrieve = erpOrderNums.Count,
        ReturnTotalCount = true,
        Classes = classes,
        Namespace = "Mediachase.Commerce.Orders",
        StartingRecord = 0
    };

    int totalRecords = 0;         
    return OrderContext.Current.FindPurchaseOrders(parameters, options, out totalRecords);
}

This is interesting. I have some minutes to spare so I look into this and it seems I can reproduce this issue. It can be a bug in our side. We will look into this and keep you posted. 

No I did it wrong, I accidentally add the SQLWhereClause condition which has no matches

So the same code for your test data works fine?

If so maybe the problem arise if some of order numbers is no present in DB.

There exists some PurchaseOrder.LoadByOrderGroupId(orderId) method but I’ve not found any overload or similar method able to retrieve multiple PurchaseOrder at once. There should be some or am I wrong?

No, I did run it directly on SQL Server.

What would return if you run this in SQL Server Management Studio (update MetaSQLClause value with your clause)?

declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',@MetaSQLClause=N'META.trackingnumber in (''618000'', ''618002'', ''618003'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8

If you are using Commerce 10 or earlier, you would have to add @ApplicationId = '395C98A4-F799-43A6-9DA0-47F5AF3D6EF2' (need to update ApplicationId to your site)

I need to colaborate on this with my boss. I shall tell you as it is done.

I’ve tried your query. It returns 0 rows despite the correct tracking numbers were used.

But does it return correct value if you change the MetaSQLClause to only one TrackingNumber?

It sounds like you have to contact our developer support service and provide your database :) 

Unfortunately does not.

The query:

declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',
@ApplicationId = '395C98A4-F799-43A6-9DA0-47F5AF3D6EF2',
@MetaSQLClause=N'META.trackingnumber = ''PO2152395259''',
@OrderBy=N'OrderGroupId DESC',
@Namespace=N'Mediachase.Commerce.Orders',
@Classes=N'PurchaseOrder',
@StartingRec=0,
@NumRecords=2147483647,
@RecordCount=@p8 output
select @p8

Very strange. As I suggested you should contact developer support service and provide the database. Might be interesting to look at :) 

We received your support case from support team today, and I took a quick look at your database. Running this query (You probably recognize the data, even though I obfuscated it a bit)

declare @p8 int
set @p8=0
exec ecf_OrderSearch @ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@SQLClause=N'',
@MetaSQLClause=N'META.trackingnumber in (''PO1204'', ''PO1205'', ''PO1206'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8
go
declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',
@ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@MetaSQLClause=N'META.trackingnumber = ''PO1205''',
@OrderBy=N'OrderGroupId DESC',
@Namespace=N'Mediachase.Commerce.Orders',
@Classes=N'PurchaseOrder',
@StartingRec=0,
@NumRecords=2147483647,
@RecordCount=@p8 output
select @p8

Returns correctly on my machine. I'm not sure what is wrong here ... 

I think I have a clue here

Running this

declare @p8 int
set @p8=0
exec ecf_OrderSearch @ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@SQLClause=N'',
@MetaSQLClause=N'META.[ErpOrderNumber] in (''172212'', ''172212'', ''172212'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8

returns the correct result as well. So you might have been mixing between the metafield (TrackingNumber instead of ErpOrderNumber) and the data. 

Hi Quan,

nice to know that. I verified my queries and I have forgotten to use proper ApplicationId so all queries works as expected. My fault, I am sorry.

I have verified the C# method code and found it magically working. I hope it was not working due some connection issue or because some relation between real data and dev DB but I am not sure what happened that time.

I shall delete this thread after your reply since there is no question/solution information.

No you don't have to, when a thread is created it should be there. Even if there was no real question, solution then it still might contain useful information. For example the code you posted might be beneficial for the one looking for an example - as we confirmed that it was written correctly. 

Ok, as you wish.