In Azure Search, how do I compare a datetime field to a datetime literal?

Question

So i am trying to do a query on my Table storage using Azure search, the table is indexed. See entity of table here

   [SerializePropertyNamesAsCamelCase]
public class Asset : TableEntity
{
    public Asset(){ }

    public Asset(string name, DateTimeOffset toBePublished)
    {
        Name = name;
        ToBePublishedDate = toBePublished.ToString();
    }

    [System.ComponentModel.DataAnnotations.Key]
    public string Id{ get; set; } = DateTimeOffset.UtcNow.ToString();

    [IsFilterable, IsSortable, IsSearchable]
    public string Name { get; set; }

    [IsFilterable, IsSortable, IsSearchable]
    public string Version { get; set; }

    [IsFilterable, IsSortable, IsSearchable]
    public string ToBePublishedDate { get; set; }

    [IsFilterable, IsSortable, IsSearchable]
    public string ToBeRetiredDate { get; set; }

    [IsFilterable, IsSortable]
    public bool IsApproved { get; set; } = false;

    [IsFilterable, IsSortable]
    public bool IsDraft { get; set; } = true;

i am trying to run a query that will return all ToBePublishedDates that are less than the current time. The way i have tried doing this so far is like this

 public static Task UpdateLatestAssetViewTableAsync(Asset asset, CloudTableClient client)
    {
        return Task.Run(() =>
        {
            CloudTable table = client.GetTableReference("TestClient");

            SearchParameters parameters;
            DocumentSearchResult<Asset> result;

            parameters = new SearchParameters
            {
                Filter = $"toBePublishedDate lt {DateTimeOffset.UtcNow}",
                Select = new [] {"name", "version"}
            };

            try
            {
                result = AzureSearch.CreateSearchIndexClient().Documents.Search<Asset>("*", parameters);

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            Console.Write(result);

        });
    }

This throws the following exception

`{Microsoft.Rest.Azure.CloudException: Invalid expression: An identifier was expected at position 21.
Parameter name: $filter
   at Microsoft.Azure.Search.DocumentsOperations.<DoContinueSearchWithHttpMessagesAsync>d__21`3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.Search.DocumentsOperationsExtensions.<SearchAsync>d__17`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.Search.DocumentsOperationsExtensions.Search[T](IDocumentsOperations operations, String searchText, SearchParameters searchParameters, SearchRequestOptions searchRequestOptions)
   at AssetSynch.Controllers.TableStorageViewFunctions.<>c__DisplayClass0_0.<UpdateLatestAssetViewTableAsync>b__0() in C:\Users\Harry\onedrive - presentation solutions ltd\documents\visual studio 2015\Projects\AssetSynch\src\AssetSynch\Controllers\TableStorageViewFunctions.cs:line 32}`

I have just started working with azure search and can't find any similar issue, i was trying to do something similar to the example at Microsoft's website found at: https://learn.microsoft.com/en-us/rest/api/searchservice/odata-expression-syntax-for-azure-search

$filter=baseRate lt 200 and lastRenovationDate ge 2012-01-01T00:00:00-08:00

but as far as i can see when debugging my c# code turns the filter to this

parameters = {$count=false&$filter=toBePublishedDate%20lt%2017%2F05%2F2017%2014%3A19%3A26%20%2B00%3A00&queryType=simple&searchMode=any&$select=name,version}

which looks nothing like that, any suggestions on what to change?

Answer 1

There are two issues that prevent the filter from working:

1. The type of the toBePublishedDate field is incorrect. The corresponding property in the Asset class is of type string, but it needs to be a DateTimeOffset. Otherwise you cannot do comparisons on it using the lt operator.
2. The format of the datetime literal in the filter needs to follow the correct format. This should work: Filter = $"toBePublishedDate lt {DateTimeOffset.UtcNow.ToString("O")}". It uses ToString with the roundtrip format specifier "O". See MSDN for documentation on the various DateTime format specifiers.