Azure Table Storage query retrieving no entities from valid filter when records are present in table

Question

I am using the Azure.Data.Tables library version 12.8.

The ITableEntity is as follows:

public sealed class DailyEventEntity : ITableEntity
{
    private const string Format = AzureConstants.AzureFormats.AzureTableDateFormat;
    public DailyEventEntity()
    { }

    public DailyEventEntity(IReadOnlyList<string> lineParts)
    {
        // for loading from .csv file
        PartitionKey = lineParts[0];
        RowKey = Convert.ToDateTime(lineParts[1]).Date.ToString(Format);
        EventTitle = lineParts[2];
        EventType = ConvertBackToEventType(int.Parse(lineParts[3]));
        EventTime = lineParts[4];
        SurveyPeriod = lineParts[5];
        ExpectedValue = lineParts[6];
        PriorValue = lineParts[7];
    }

    // interface properties
    public string PartitionKey { get; set; } //Event ticker
    public string RowKey { get; set; }  // Event date
    public DateTimeOffset? Timestamp { get; set; }
    public ETag ETag { get; set; }

    // custom properties
    public string EventTitle { get; set; }
    public EventType EventType { get; set; }
    public string EventTime { get; set; }
    public string SurveyPeriod { get; set; }
    public string ExpectedValue { get; set; }
    public string PriorValue { get; set; }

    // helper methods
    private static EventType ConvertBackToEventType(int intToParse)
    {
        return intToParse switch
        {
            0 => EventType.Earnings,
            1 => EventType.EconomicData,
            2 => EventType.CommodityData,
            _ => EventType.Other,
        };
    }
}

The method that builds the filter is as follows:

public static string GenerateDailyEventsFilter(int numOfEventDaysToShow)
{
    const string dateFormat = AzureConstants.AzureFormats.AzureTableDateFormat;
    var now = DateTime.Now;
    var start = now.AddDays(1).ToString(dateFormat);

    var additionalDays = now.DayOfWeek switch
    {
        DayOfWeek.Saturday => 1,
        DayOfWeek.Wednesday or DayOfWeek.Thursday or DayOfWeek.Friday => 2,
        _ => 0
    };

    var endDate = now.AddDays(numOfEventDaysToShow + additionalDays).ToString(dateFormat);

    return TableClient.CreateQueryFilter($"RowKey ge {start} and RowKey le {endDate}");
}

The data retrieval method is as follows:

public async Task<IEnumerable<DailyEventEntity>> GetDailyEventsAsync()
{
    var results = new List<DailyEventEntity>();
    var filter = EquityNoteQueryFilters.GenerateDailyEventsFilter(3);
    try
    {
        var entities = TableClient!.QueryAsync<DailyEventEntity>(filter);
        results = (List<DailyEventEntity>)await entities.ToListAsync();
        Log.Information("Retrieved {Count} daily events", results.Count);
        //await foreach (var entity in TableClient!.QueryAsync<DailyEventEntity>(filter))
        //{
        //    results.Add(entity);
        //}
    }
    catch (RequestFailedException ex)
    {
        Log.Error(_baseErrorMessage, Environment.NewLine, ex.Message, Environment.NewLine, ex.InnerException);
    }
    catch (Exception ex)
    {
        Log.Error(_baseErrorMessage, Environment.NewLine, ex.Message, Environment.NewLine, ex.InnerException);
    }

    return results;
}

This is what the query filter returns:

RowKey ge '2023-08-26' and RowKey le '2023-08-30'

The Table service methods returns zero items for entities, and thus fails on the conversion to results. (Yeah, I'll fix that so it doesn't error)

There are valid items that should be retrieved in the table:

The TableClient IS NOT the issue (IMHO) as it is created correctly, and works for the other 12 methods in the service (That operate on different tables, this is the only method for this table so far, and the only one where I am NOT using a partition key as part of the search And yes, I know that's not great but this table is purged and re sourced every two weeks and rarely has more than 100 entries)

FYI...I'm perfectly fine with table redesign, but I don't think its needed really.

Answer 1

EDITED: As a matter of fact @dinotom is right and QueryAsync is not awaitable, so my original answer was way off.

I'm starting to think that the issue somes from the ge and le operators that are used to perform comparisons on string values in the query filter.

As far as I know, the filtering will never be able to determine if something is greater than or less than a string value, even if it's originally taken from a date value and the format is "valid".

I believe that in order to get this working, you're going to have to prefix your RowKey values with datetime to get it working.

public static string GenerateDailyEventsFilter(int numOfEventDaysToShow)
{
    const string dateFormat = AzureConstants.AzureFormats.AzureTableDateFormat;
    var now = DateTime.Now;
    var start = now.AddDays(1).ToString(dateFormat);

    var additionalDays = now.DayOfWeek switch
    {
        DayOfWeek.Saturday => 1,
        DayOfWeek.Wednesday or DayOfWeek.Thursday or DayOfWeek.Friday => 2,
        _ => 0
    };

    var endDate = now.AddDays(numOfEventDaysToShow + additionalDays).ToString(dateFormat);

    return TableClient.CreateQueryFilter($"RowKey ge datetime{start} and RowKey le datetime{endDate}");
}

I'm unable to test it right now, but you might have to append T00:00:00 to your {start} and {endDate} strings for the conversion/comparison to work correctly.

Docs for filter string construction using dates here.