AzureSearch filter not working expected

Question

I migrated my SQL data into AzureSearch document to try new search experience. I'm not able to filter data using .net sdk (3.0.4)

public IActionResult Search(string state, string category, string search, short pageNumber = 1, short pageSize = 10)
    {
        SearchIndexClient indexClient = new SearchIndexClient(searchServiceName, "search", new SearchCredentials(searchServiceApiKey));

        DocumentSearchResult<SearchResultDto> results = null;
        if (string.IsNullOrWhiteSpace(search))
            search = "*";
        if (state.Equals("All", StringComparison.InvariantCultureIgnoreCase))
            state = string.Empty;

        SearchParameters parameters = new SearchParameters()
        {
            Filter = "state eq " + state,
            Top = pageSize,
            Skip = (pageNumber - 1) * pageSize,
            SearchMode = SearchMode.All,
            IncludeTotalResultCount = true
        };

        try
        {
            results = indexClient.Documents.Search<SearchResultDto>(search, parameters);
            return Ok(results.Results);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error querying index: {0}\r\n", ex.Message.ToString());
            throw ex;
        }
    }

I'm getting error "Exception has been thrown by the target of an invocation."

parameters raw value : $count=true&$filter=state%20eq%20&queryType=simple&searchMode=all&$skip=0&$top=10

when I used parameters value in AzureSearch explore I'm getting error Invalid expression: Expression expected at position 19 in 'state eq delhi eq '.\r\nParameter name: $filter

What is wrong with my code??

There are two problems here. I’ll post an answer about the filter syntax, but I wanted to get more information about the “Exception was thrown by the target of an invocation” error. This error is unexpected (you should get a CloudException instead). Can you please edit your question to provide stack trace information for that error? — Bruce Johnston

Bruce Johnston Bruce Johnston · Accepted Answer · 2018-02-03T16:27:14

There are a few problems with your filter.

String literals in OData are delimited by single quotes. If you leave out the quotes, the string looks like a field name, but comparing fields to other fields is not allowed in Azure Search (also there is likely no field named delhi in your index). Try state eq 'delhi'.
The filter you tried with Search Explorer has an extra eq operator on the end: “state eq delhi eq “. If you remove the extra eq and put single quotes around delhi it should work.
Once you fix the syntax errors, the filter still might not work as intended. Filters are case-sensitive, so if the value you’re trying to match is actually ‘Delhi’ with a capital D, you won’t get a match. If the state field is matched to raw user input that might have the wrong case, it might be better to use the searchText parameter instead of Filter.

AzureSearch filter not working expected

1 Answers