Be smart about Business Central OData, $filter like a Pro!

In this video, I show to do filtering on OData, at the source, check it out:

https://youtu.be/D0RSAHGm1Pw

In this video, Erik demonstrates one of the most common mistakes people make when working with OData endpoints in Business Central: pulling all the data and filtering on the client side. He walks through the proper way to use the $filter query parameter to let the server do the heavy lifting, covering comparison operators, logical operators, and string functions — all with live examples in the browser.

The Problem: Client-Side Filtering

One of the most typical errors Erik has seen over recent months is a pattern that goes like this: developers find an OData endpoint, successfully retrieve data, and then do all their filtering and processing on the client side. This creates two significant issues:

  1. Server performance impact: If Business Central is spending a lot of time generating and returning large datasets, it affects the entire tenant. Your posting routines might slow down, and other users will feel the pain.
  2. Wasted transmission time: You’re transferring far more data over the network than you actually need, only to throw most of it away on the client side.

The solution? Use the $filter query parameter to let the server filter the data before it’s transmitted. This is the same concept that every Business Central and NAV user already understands — filtering is the key to getting the right data. The OData syntax is just a bit different.

The Basics: Equality and Comparison Operators

Erik starts with a simple API page that exposes the Customer table with the Number and Name fields. The base URL returns all customers, but adding $filter changes everything.

To filter for a specific customer number, you append the filter to the URL:

$filter=Number eq '30000'

The syntax is: field name, space, operator, space, value. The reason we use eq instead of = is that special characters like equals signs, greater-than and less-than brackets are problematic in URL query strings. So OData uses text-based abbreviations instead.

This is functionally identical to opening the Customer List in Business Central and setting a filter on the Number field to “30000” — the server processes it the same way.

Comparison Operators

Here’s the full list of comparison operators available:

  • eq — Equal
  • ne — Not equal
  • gt — Greater than
  • lt — Less than
  • ge — Greater than or equal
  • le — Less than or equal

For example, to get customers with a number greater than 30000:

$filter=Number gt '30000'

Combining Filters with AND and OR

To create range filters (equivalent to the “..” filter in BC), you combine two conditions with and. An important detail: you must specify the field name on both sides of the and operator.

$filter=Number gt '30000' and Number lt '50000'

This returns only customer 40000, since it’s strictly greater than 30000 and strictly less than 50000. To include the boundary values (like a “..” range in BC), use ge and le:

$filter=Number ge '30000' and Number le '50000'

Now you get customers 30000, 40000, and 50000.

You can also use and between different fields:

$filter=Country_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS'

The or operator is available but with a limitation — you can only use or on the same field. You cannot say “payment code equals something or zip code equals something else.”

$filter=Number eq '30000' or Number eq '50000'

This returns both customers. Note that if you used and here instead of or, you’d get zero results — no customer can have a number that is both 30000 and 50000 simultaneously.

String Functions

Beyond simple comparison operators, OData supports string functions for more advanced filtering:

  • endswith(FieldName, 'value') — Filter where a field ends with a specific string
  • startswith(FieldName, 'value') — Filter where a field starts with a specific string
  • contains(FieldName, 'value') — Filter where a field contains a specific string
  • indexof(FieldName, 'value') — Find position of a substring
  • tolower(FieldName) / toupper(FieldName) — Case conversion for comparison
  • trim(FieldName) — Remove leading/trailing whitespace
  • substring(FieldName, start, length) — Extract part of a string

For example, to find customers whose number ends with “0000”:

$filter=endswith(Number, '0000')

Erik notes that from this point on, these functions may not be backed by indexes or keys in Business Central. However, you’re still pushing the processing to the server and saving on transmission costs, which is a significant win.

Additional mathematical functions like round, floor, and ceiling are also available for numeric fields.

Using $filter from C# Code

The accompanying C# project demonstrates how to apply OData filters programmatically. The solution uses the Microsoft.OData.Client library to connect to both the API and ODataV4 endpoints:

using System;

namespace ODataFromCSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            var serviceRoot = "http://bc18:7048/BC/api/v2.0/companies(d00aced7-6ec1-eb11-b388-da215ec232af)/";
            var context = new BCAPI.NAV(new Uri(serviceRoot));
            context.BuildingRequest += Context_BuildingRequest;

            var data = context.Vendors.Execute();
            foreach (var vendor in data)
                Console.WriteLine("{0} {1}", vendor.Number, vendor.DisplayName);

            var serviceRoot2 = "http://bc18:7048/BC/ODataV4/Company('Hougaard')/";
            var context2 = new BC18.NAV(new Uri(serviceRoot2));
            context2.BuildingRequest += Context_BuildingRequest;

            var data2 = context2.Chart_of_Accounts.AddQueryOption("$filter", "Net_Change gt 0");
            foreach (var account in data2)
                Console.WriteLine("{0} {1} {2}", account.No, account.Name, account.Net_Change);
        }

        private static void Context_BuildingRequest(object sender, 
            Microsoft.OData.Client.BuildingRequestEventArgs e)
        {
            e.Headers.Add("Authorization", "Basic ZGVtbzpEZW1vMjAwNCE=");
        }
    }
}

Notice the key line where filtering is applied in C#:

var data2 = context2.Chart_of_Accounts.AddQueryOption("$filter", "Net_Change gt 0");

The AddQueryOption method appends the $filter parameter to the OData request, so only Chart of Accounts entries with a positive net change are returned from the server. This is far more efficient than retrieving all accounts and filtering in your C# code.

The project references are defined in the project file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.OData.Client" Version="7.9.0" />
    <PackageReference Include="Microsoft.OData.Core" Version="7.9.0" />
    <PackageReference Include="Microsoft.OData.Edm" Version="7.9.0" />
    <PackageReference Include="Microsoft.Spatial" Version="7.9.0" />
    <PackageReference Include="System.Text.Json" Version="5.0.2" />
  </ItemGroup>
</Project>

Quick Reference: OData Filter Operators

Here’s a handy summary of the filter syntax compared to what you might use in Business Central:

  • eq → equals (=)
  • ne → not equal (<>)
  • gt → greater than (>)
  • lt → less than (<)
  • ge → greater than or equal (>=)
  • le → less than or equal (<=)
  • and → combine conditions (must repeat field name)
  • or → alternative conditions (same field only)

Conclusion

The $filter parameter is your best friend when working with OData in Business Central. Instead of pulling hundreds of thousands or even millions of records and filtering them away in Power BI or your integration code, push that filtering to the server where it belongs. Don’t be confused by the text-based operators like gt, lt, and eq — they’re simply replacements for characters that don’t survive well in HTTP query strings. The more data you’re working with, the more critical it becomes to filter at the source. Microsoft’s documentation page “Using Filter Expressions in OData URIs” is an excellent reference to keep bookmarked.

Picture of hougaard

hougaard