Querying assets with filter returns too small subset of assets

Question

I'm querying assets that have empty alternateId, but using the following code I only get around 7k assets. If I query without the filter and count the number of returned assets with empty alternateId, it is around 36k. It's not related to paging as I'm using the same NextPageLink paging logic in both test cases.

What could be the reason for this? I'm looking into optimizing the code by not having to get all 72k assets but only the ones with empty alternateId, and update that after having processed the asset.

I use Microsoft.Azure.Management.Media v6.0.0.

var assets = await client.Assets.ListAsync(
    config.ResourceGroup,
    config.AccountName,
    new Microsoft.Rest.Azure.OData.ODataQuery<Asset>() { 
        Filter = "properties/alternateId eq ''" 
    }
);

Answer 1

You need to filter by properties/alternateId eq null to get null values. If you want both null and empty values you can use properties/alternateId eq '' or properties/alternateId eq null

Here is some sample code to test out the behavior. I added an additional filter on created date to exclude old data.

            var alternateNull = await Client.Assets.CreateOrUpdateAsync(ResourceGroup, MediaAccount, "alternate_null", new Asset());
            var alternateEmpty = await Client.Assets.CreateOrUpdateAsync(ResourceGroup, MediaAccount, "alternate_empty", new Asset { AlternateId = "" });
            var alternateSet = await Client.Assets.CreateOrUpdateAsync(ResourceGroup, MediaAccount, "alternate_set", new Asset { AlternateId = "abcd" });

            var getNulls = await Client.Assets.ListAsync(
                ResourceGroup,
                MediaAccount,
                new Microsoft.Rest.Azure.OData.ODataQuery<Asset>()
                {
                    Filter = "properties/created gt 2022-10-07T00:00:00Z and properties/alternateId eq null"
                });

            Console.WriteLine("Nulls:");
            Console.WriteLine(string.Join(',', getNulls.Select(s => s.Name)));

            var getEmpty = await Client.Assets.ListAsync(
                ResourceGroup,
                MediaAccount,
                new Microsoft.Rest.Azure.OData.ODataQuery<Asset>()
                {
                    Filter = "properties/created gt 2022-10-07T00:00:00Z and properties/alternateId eq ''"
                });

            Console.WriteLine("Empty:");
            Console.WriteLine(string.Join(',', getEmpty.Select(s => s.Name)));

            var getNullOrEmpty = await Client.Assets.ListAsync(
                ResourceGroup,
                MediaAccount,
                new Microsoft.Rest.Azure.OData.ODataQuery<Asset>()
                {
                    Filter = "properties/created gt 2022-10-07T00:00:00Z and (properties/alternateId eq '' or properties/alternateId eq null)"
                });

            Console.WriteLine("Null Or Empty:");
            Console.WriteLine(string.Join(',', getNullOrEmpty.Select(s => s.Name)));

            var getAll = await Client.Assets.ListAsync(
                ResourceGroup,
                MediaAccount,
                new Microsoft.Rest.Azure.OData.ODataQuery<Asset>()
                {
                    Filter = "properties/created gt 2022-10-07T00:00:00Z"
                });

            Console.WriteLine("All:");
            Console.WriteLine(string.Join(',', getAll.Select(s => s.Name)));