Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / OData Query applying o...
Microsoft Dynamics NAV (Archived)

OData Query applying order

(0) ShareShare
ReportReport
Posted on by Pavel Penkava 115

Hello,
correct me if I am wrong, but I believe there is a bug in the Query implementation of OData.

I have a Web Service that returns Items from Dynamics NAV 2013 R2. There are plenty of products (Items) which has VAT_Prod_Posting_Group equal to empty string '', but when I send the request below, it returns just one item. It seems like the filter is applied after the applying of skip and top instead of the reversed order (one of the first 5 items really has the string value of '').
http://localhost:7048/DynamicsNAV71/OData/ItemsList?$skip=0&$top=5&$filter=VAT_Prod_Posting_Group eq ''

I've tried a similar request on the OData testing site (as seen below) and there it works as expected (skip and top are applied after the filter pattern).
http://services.odata.org/Northwind/Northwind.svc/Customers?$skip=0&$top=5&$filter=Fax eq null

*This post is locked for comments

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at
    RE: OData Query applying order

    Unfortunately it was application written in Java (for Android), so I couldn't use Linq. Everything would be much easier if it would be written in C#, which I prefer too :)

    But I guess the suggested page msdn.microsoft.com/.../jj551780.aspx could help, so we can close it because I can't test it any more ;)

  • Verified answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: OData Query applying order

    hi,

    i tried

    http://localhost:7048/DynamicsNAV70/OData/Company('CRONUS%20AG')/ItemsList?$filter=Product_Group_Code%20eq%20%27%27&$top=3

    that works fine. 

    the field "Product Group Code" is in odata translated to Product_Group_Code.

    the field "VAT Prod. Posting Group" is translated to VAT_Prod_Posting_Group. with VAT_Prod_Posting_Group it does not work. Vat_Item_No does also not work. but only if the comparative value is '' (empty). if you use a concrete, valid value, e.g. 'VAT16', the top tag works too.

    strange enough.


    maybe that helps too:

    msdn.microsoft.com/.../jj551780(v=nav.71).aspx

    an alternative way:

    you are using an odata webservice for developing an application in c#?

    if so, filter the data with linq in c#. sample code:

    var client = new ServiceReference1.NAV(new Uri("http://localhost:7048/DynamicsNAV71/OData/"));
    client.Credentials = System.Net.CredentialCache.DefaultCredentials;

    var items = (from item in client.ItemsList
    where item.VAT_Prod_Posting_Group.Equals("")
    select item).ToList();
    Console.WriteLine("Result");
    int i=0;
    while (i < 5) {
      Console.WriteLine(items[i].No + " " + items[i].Description);
      i++; }
    Console.WriteLine("---------------------------");
    Console.ReadLine();

    for java check:

    https://code.google.com/p/odata4j/

    best regards

    if you find one of the answers helpful to find a solution please set it to verified. thx.

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at
    RE: OData Query applying order

    Hello,

    it's good to know, but how can I bypass the need for the "top" tag? I wanted to retrieve only a few elements in each request so I could use it in lazy loaded list on mobile platforms.

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: OData Query applying order

    hi,

    after a greater number of tests, it seems that in nav odata services the filter tag and the top tag in the uri to not go together. skip is no problem. in general the filter call works.

    so

    localhost/.../ItemsList$skip=0&$filter=VAT_Prod_Posting_Group eq ''

    works.

    best regards

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at
    RE: OData Query applying order

    Hello, I can't test it because I no longer work on the project and my licence expired already. But I guess I've tried this before and didn't work either.

  • keoma Profile Picture
    keoma 32,675 on at
    RE: OData Query applying order

    hi

    try "...&filter=VAT_Prod_Posting_Group eq null" in your query instead.

    best regards

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans