How to parse OData $filter with regular expression in C#?

Question

Hi I'm wondering what the best approach would be to parse an OData $filter string in C#, for example

/API/organisations?$filter="name eq 'Facebook' or name eq 'Twitter' and subscribers gt '30'"

Should return all organisations with a name of Facebook or Twitter and who have more than 30 subscribers. I've researched quite a bit but can't find any solutions which don't revolve around WCF. I was thinking of using Regex and grouping them so I have a list of Filter classes such that:

Filter
    Resource: Name
    Operator: Eq
    Value: Facebook
Filter
    Resource: Name
    Operator: Eq
    Value: Twitter
Filter
    Resource: Subscribers
    Operator: gt
    Value: 30

but I'm stumped as how to handle ANDs / ORs.

Ennoble

Consider using an actual parser toolkit and working off the spec, not stumbling around with a bunch of REs. The docs I found mention a "normative OData specification" with a grammar for the filter expression. – Stasiastasis

Answer 1

In .NET, there's a library available that will do this for you. Writing your own regex runs the risk of missing some edge case.

Using NuGet, bring in Microsoft.Data.OData. Then, you can do:

using Microsoft.Data.OData.Query;

var result = ODataUriParser.ParseFilter(
  "name eq 'Facebook' or name eq 'Twitter' and subscribers gt 30",
  model,
  type);

result here will be in the form of an AST representing the filter clause.

(To get the model and type inputs, you could parse your $metadata file using something like this:

using Microsoft.Data.Edm;
using Microsoft.Data.Edm.Csdl;

IEdmModel model = EdmxReader.Parse(new XmlTextReader(/*stream of your $metadata file*/));
IEdmEntityType type = model.FindType("organisation");

)

Medium

Jen, can you or anyone else explain how to generate an EDM dynamically? The first idea I have is generating one using reflection, but the IEdmModel has a ton of members to implement and I am hoping to avoid having to learn what each one does. – Headdress

Is there a way to just download C++ source without using nuGet? Is there a source repository for the Microsoft.Data.OData anywhere? – Fitzhugh

Just a note: This solution uses the Odata V 3.0. (Microsoft.Data.OData). The newer OData V 4.0 requires Microsoft.OData.Core – Roseline

Can someone explain what should be given in XmlTextReader parameter in model? – Arruda

Answer 2

I think you are supposed to travserse the AST with the interface provided using the visitor pattern.

Consider you have this class that represents a filter

public class FilterValue
{
    public string ComparisonOperator { get; set; }
    public string Value { get; set; }
    public string FieldName { get; set; }
    public string LogicalOperator { get; set; }
}

So, how do we "extract" the filters that comes with the OData parameters to your class?

Well the FilterClause object have an Expression property which is a SingleValueNode wich inherits from a QueryNode. The QueryNode have the Accept method who takes a QueryNodeVisitor.

    public virtual T Accept<T>(QueryNodeVisitor<T> visitor);

Right, so you must implement your own QueryNodeVisitor and do your stuff. Below is a non finished example (I dont override all possible visitors).

public class MyVisitor<TSource> : QueryNodeVisitor<TSource>
    where TSource: class
{ 
    List<FilterValue> filterValueList = new List<FilterValue>();
    FilterValue current = new FilterValue();
    public override TSource Visit(BinaryOperatorNode nodeIn)
    {
        if(nodeIn.OperatorKind == Microsoft.Data.OData.Query.BinaryOperatorKind.And 
            || nodeIn.OperatorKind == Microsoft.Data.OData.Query.BinaryOperatorKind.Or)
        {
            current.LogicalOperator = nodeIn.OperatorKind.ToString();
        }
        else
        {
            current.ComparisonOperator = nodeIn.OperatorKind.ToString();
        }
        nodeIn.Right.Accept(this);
        nodeIn.Left.Accept(this);
        return null;
    }
    public override TSource Visit(SingleValuePropertyAccessNode nodeIn)
    {
        current.FieldName = nodeIn.Property.Name;
        //We are finished, add current to collection.
        filterValueList.Add(current);
        //Reset current
        current = new FilterValue();
        return null;
    }

    public override TSource Visit(ConstantNode nodeIn)
    {
        current.Value = nodeIn.LiteralText;
        return null;
    }

}

Then, fire away :)

MyVisitor<object> visitor = new MyVisitor<object>();
options.Filter.FilterClause.Expression.Accept(visitor);

When it has traversed the tree your

visitor.filterValueList

should contain the filters in your desired format. Im sure more work is needed but if you can get this rolling I think you can figure it out.

Easement

Just one note: If you are using Odata V4.0, like I'm doing, you need to use Microsoft.OData.Core.UriParser.TreeNodeKinds.BinaryOperatorKind.And instead – Roseline

Thanks for real-life example! But why return current as TSource? Is this retunr of any use anywhere? I tried to return null in all my overridden Visit(...) methods, and it worked as well. Is there any more detailed help on overriding those visits? I did not manage to Google it out, this answer seems to be the only googlable source of implementing QueryNodeVisitor – Stets

I don’t know actually. But the signature says return T so I just did that. – Easement

You can choose the Type T to be something like List<FilterValue> if you want and then you don't have to expose the inner filterValueList directly. – Propose

Answer 3

Building on what Jen S says, you can traverse the AST tree that is returned by the FilterClause.

For instance, you can retrieve the FilterClause from the controller's query options:

public IQueryable<ModelObject> GetModelObjects(ODataQueryOptions<ModelObject> queryOptions)        
    {
        var filterClause = queryOptions.Filter.FilterClause;

You can then traverse the resultant AST tree with code like the following (borrowed from this article):

var values = new Dictionary<string, object>();
TryNodeValue(queryOptions.Filter.FilterClause.Expression, values);

The function called is like so:

public void TryNodeValue(SingleValueNode node, IDictionary<string, object> values)
    {
        if (node is BinaryOperatorNode )
        {
            var bon = (BinaryOperatorNode)node;
            var left = bon.Left;
            var right = bon.Right;

            if (left is ConvertNode)
            {
                var convLeft = ((ConvertNode)left).Source;

                if (convLeft is SingleValuePropertyAccessNode && right is ConstantNode)
                    ProcessConvertNode((SingleValuePropertyAccessNode)convLeft, right, bon.OperatorKind, values);
                else
                    TryNodeValue(((ConvertNode)left).Source, values);                    
            }

            if (left is BinaryOperatorNode)
            {
                TryNodeValue(left, values);
            }

            if (right is BinaryOperatorNode)
            {
                TryNodeValue(right, values);
            }

            if (right is ConvertNode)
            {
                TryNodeValue(((ConvertNode)right).Source, values);                  
            }

            if (left is SingleValuePropertyAccessNode && right is ConstantNode)
            {
                ProcessConvertNode((SingleValuePropertyAccessNode)left, right, bon.OperatorKind, values);
            }
        }
    }

    public void ProcessConvertNode(SingleValuePropertyAccessNode left, SingleValueNode right, BinaryOperatorKind opKind, IDictionary<string, object> values)
    {            
        if (left is SingleValuePropertyAccessNode && right is ConstantNode)
        {
            var p = (SingleValuePropertyAccessNode)left;

            if (opKind == BinaryOperatorKind.Equal)
            {
                var value = ((ConstantNode)right).Value;
                values.Add(p.Property.Name, value);
            }
        }
    }

You then can go through the list dictionary and retrieve your values:

 if (values != null && values.Count() > 0)
        {
            // iterate through the filters and assign variables as required
            foreach (var kvp in values)
            {
                switch (kvp.Key.ToUpper())
                {
                    case "COL1":
                        col1 = kvp.Value.ToString();
                        break;
                    case "COL2":
                        col2 = kvp.Value.ToString();
                        break;
                    case "COL3":
                        col3 = Convert.ToInt32(kvp.Value);
                        break;
                    default: break;
                }
            }
        }

This example is fairly simplistic in that it only accounts for "eq" evaluations, but for my purposes it worked well. YMMV. ;)

Nebuchadnezzar

Answer 4

Thanks you @Stinky Buffalo for the answer. i change your code and resolve error when add duplicate key in dictionary.

example:

CreateDate%20gt%202021-05-22T00:00:00Z%20and%20CreateDate%20lt%202021-05-26T00:00:00Z%20

and also:

BookRequestType%20eq%20%27BusDomestic%27%20or%20BookRequestType%20eq%20%27TrainDomestic%27%20or%20BookRequestType%20eq%20%27FlightDomestic%27%20

The following code worked very well for me:

first install Install-Package Microsoft.Data.OData -Version 5.8.4 package.

then create class with name 'ODataHelper' and after that copy below codes:

    public class ODataHelper<T> where T : class
{
    private static readonly TextInfo TextInfo = new CultureInfo("en-US", false).TextInfo;

    public static Dictionary<string, Tuple<object, ODataOperatorType>> ODataUriParser(
        ODataQueryOptions<T> queryOptions)
    {
        var dictFilters = new Dictionary<string, Tuple<object, ODataOperatorType>>();

        TryNodeValue(queryOptions.Filter?.FilterClause?.Expression, dictFilters);

        return dictFilters;
    }

    private static void TryNodeValue(SingleValueNode node,
        IDictionary<string, Tuple<object, ODataOperatorType>> dictFilters)
    {
        if (node is null)
            return;

        if (node is SingleValueFunctionCallNode valueFunction)
        {
            ParseSingleFunctionNode(valueFunction,
                Enum.Parse<ODataOperatorType>(TextInfo.ToTitleCase(valueFunction.Name)), dictFilters);
        }

        if (node is BinaryOperatorNode binaryOperatorNode)
        {
            var left = binaryOperatorNode.Left;
            var right = binaryOperatorNode.Right;

            if (left is SingleValuePropertyAccessNode leftNodeRight && right is ConstantNode rightNodeRight)
            {
                ParseSingleValueNode(
                    leftNodeRight,
                    rightNodeRight,
                    Enum.Parse<ODataOperatorType>(binaryOperatorNode.OperatorKind.ToString()),
                    dictFilters);
            }

            switch (left)
            {
                case ConvertNode node1:
                {
                    var convertLeft = node1.Source;

                    if (convertLeft is SingleValuePropertyAccessNode leftNodeLeft &&
                        right is ConstantNode rightNodeLeft)
                    {
                        ParseSingleValueNode(
                            leftNodeLeft,
                            rightNodeLeft,
                            Enum.Parse<ODataOperatorType>(
                                binaryOperatorNode.OperatorKind.ToString()),
                            dictFilters);
                    }
                    else
                        TryNodeValue(node1.Source, dictFilters);

                    break;
                }
                case BinaryOperatorNode:
                    TryNodeValue(left, dictFilters);
                    break;
                case SingleValueFunctionCallNode functionNode:
                    ParseSingleFunctionNode(functionNode,
                        Enum.Parse<ODataOperatorType>(TextInfo.ToTitleCase(functionNode.Name)),
                        dictFilters);
                    break;
            }

            switch (right)
            {
                case BinaryOperatorNode:
                    TryNodeValue(right, dictFilters);
                    break;
                case ConvertNode convertNode:
                    TryNodeValue(convertNode.Source, dictFilters);
                    break;
                case SingleValueFunctionCallNode functionNode:
                    ParseSingleFunctionNode(functionNode,
                        Enum.Parse<ODataOperatorType>(TextInfo.ToTitleCase(functionNode.Name)),
                        dictFilters);
                    break;
            }
        }
    }

    private static void ParseSingleValueNode(
        SingleValuePropertyAccessNode left,
        SingleValueNode right,
        ODataOperatorType operatorKind,
        IDictionary<string, Tuple<object, ODataOperatorType>> dictFilters)
    {
        string key = left.Property.Name.Trim();
        object value = ((ConstantNode) right).Value;

        object specifiedValue = value is ODataEnumValue enumValue ? enumValue.Value : value;

        if (operatorKind is ODataOperatorType.LessThan or ODataOperatorType.LessThanOrEqual)
        {
            dictFilters.TryAdd($"{key}_To", new Tuple<object, ODataOperatorType>(value, operatorKind));
        }
        else if (dictFilters.TryGetValue(key, out Tuple<object, ODataOperatorType> currentValue))
        {
            dictFilters[key] = new Tuple<object, ODataOperatorType>(
                $"{currentValue.Item1},{specifiedValue}",
                operatorKind);
        }
        else
        {
            dictFilters.Add(key, new Tuple<object, ODataOperatorType>(specifiedValue, operatorKind));
        }
    }

    private static void ParseSingleFunctionNode(
        SingleValueFunctionCallNode node,
        ODataOperatorType operatorKind,
        IDictionary<string, Tuple<object, ODataOperatorType>> dictFilters)
    {
        string key = (node.Parameters.First() as SingleValuePropertyAccessNode)?.Property.Name.Trim();
        object value = (node.Parameters.Last() as ConstantNode)?.Value;

        if (string.IsNullOrEmpty(Convert.ToString(value)?.Trim()))
            return;

        dictFilters.TryAdd(key, new Tuple<object, ODataOperatorType>(value, operatorKind));
    }
}

public enum ODataOperatorType
{
    Equal,
    NotEqual,
    GreaterThan,
    GreaterThanOrEqual,
    LessThan,
    LessThanOrEqual,
    Contains
}

for call ODataUriParser method you need to get the value from the input action.

get ODataQueryOptions<YourObjectModel> from request api :

input endpoint action => ODataQueryOptions<YourObjectModel> options

 public Task<IQueryable<YourObject>> Get(ODataQueryOptions<YourObject> options)
        {
            // call your service class
        }

then write below codes in your service class for call ODataUriParser and use the result runction:

   Dictionary<string, Tuple<object, ODataOperatorType>> dictFilters =
            ODataHelper<YourObject>.ODataUriParser(options);

An example of how to use ODataUriParser method result:

if (dictFilters.TryGetValue("Email", out Tuple<object, ODataOperatorType> emailValue))
                {
                    bookRequestProfileDto.Email =
                        Convert.ToDateTime(emailValue.Item1.ToString());
                }

For example, we want to convert a list of numeric strings to a list of text strings using the Enum: BookRequestType is enumuration class.

  if (dictFilters.TryGetValue("BookRequestType", out Tuple<object, ODataOperatorType> bookRequestTypeValue))
                {
                    customerTransactionDto.BookRequestType =
                        Convert.ToString(bookRequestTypeValue.Item1)
                            .ConvertStringNamesEnumToStringNumbers<BookRequestType>();
                }

// Extesion Method
    public static string ConvertStringNamesEnumToStringNumbers<T>(this string stringTypes) where T : Enum
            {
                var separateStringTypes = stringTypes.Split(',');

            StringBuilder stringBuilder = new StringBuilder();

            foreach (var item in separateStringTypes)
            {
                stringBuilder.Append((int) Enum.Parse(typeof(T), item)).Append(',');
            }

            return stringBuilder.ToString().Remove(stringBuilder.Length - 1);
        }

Sunstroke

This seemed to work for me. I had to edit it for C# version 8 but the changes were pretty trivial. – Curative