Converting ODataQueryOptions into LINQ Expressions in C#

OData makes it very easy for us to pass query options from the URL down to an object mapper like EntityFramework. However, this somehow happens automagically and cannot be really controlled in code. In this blog post I show how we can extract and convert FilterQueryOption and OrderByQueryOption to manually apply them to our underlying LINQ provider.

Let’s start with a small introduction of the example environment. In one of our software products we have two types of models:

  1. a public model that is served and exposed by ODATA (deriving from TPublicEntity)
  2. an internal data model that we use to query against and persist to the data layer via EntityFramework (deriving from TDataEntity)

Our models differ in terms and properties in such a way that not all ODATA navigation properties map one to one to the data model (as described in
Separating your ODATA Models from the Persistence Layer with AutoMapper and More Fun with your ODATA Models and AutoMapper). In addition, we want to implement and enforce entity based permission checks on the data layer, so that not all entities are returned to the caller on the ODATA REST layer.

So in order to have the data layer to be able to perform the query we have to transform or convert the FilterQueryOption into a LINQ Expression and translate the expression from the public model to our internal data model. ODATA supplies a ApplyTo method that returns an IQueryable from which we can extract the expression that we then can apply to the DbContext.Set.

1. Converting FilterQueryOption to Expression

We extract the expression by converting the filter to an IQueryable as described in How to transform OData filter to a LINQ expression?:

public static Expression ToExpression<TPublicEntity>(this OrderByQueryOption filter)
  where TPublicEntity : PublicEntity
  Contract.Requires(null != filter);
  Contract.Ensures(null != Contract.Result<Expression>());

  IQueryable queryable = Enumerable.Empty<TPublicEntity>().AsQueryable();
  queryable = filter.ApplyTo(queryable, _odataQuerySettings);
  return queryable.Expression;

2. Converting the Expression to LambdaExpression and replacing TPublicEntity with TDataEntity

The expression generated by ApplyTo is a quoted MethodCallExpression where we are only interested in the LambdaExpression part. Before we actually create our Expression&lt;Func&gt; we have to replace MemberExpression as our data layer operates on TDataEntity and not on TPublicEntity. This is done by an ExpressionVisitor as described in Replace parameter in lambda expression. We just have to make sure that we replace the MemberExpression and not only the ParameterExpression and that we supply the same instance of our Expression.Parameter() to the lambda or otherwise we get an error from EntityFramework like:

The parameter '$it' was not bound in the specified LINQ to Entities query expression.

And we have to make sure we use the same parameter name ($it in our example, which is the same as ODATA uses when constructing expressions):

var visitor = new MemberExpressionVisitor<TPublicEntity>(Expression.Parameter(typeof(TDataEntity), "$it"));

public static Expression ReplaceExpression(Expression filterExpression)
  Contract.Requires(null != filterExpression);
  Contract.Ensures(null != Contract.Result<Expression>());

  var methodCallExpressionFilter = filterExpression as MethodCallExpression;
  Contract.Assert(null != methodCallExpressionFilter);
  Contract.Assert(2 == methodCallExpressionFilter.Arguments.Count);

  var unquotedLambdaExpressionFilter = visitor.Replace(methodCallExpressionFilter.Arguments[1])
    .Unquote() as LambdaExpression;
  Contract.Assert(null != unquotedLambdaExpressionFilter);

  var expressionLambda = Expression.Lambda<Func<TDataEntity, bool>>(unquotedLambdaExpressionFilter.Body, visitor.Parameter);
  return expressionLambda;

The ExpressionVisitor is quite simple as its only use is to replace all MemberExpressions of type TPublicEntity the supplied ParameterExpression and looks like this:

public class MemberExpressionVisitor<TPublicEntity> : ExpressionVisitor
  where TPublicEntity : PublicEntity
  public ParameterExpression Parameter { get; set; }

  public MemberExpressionVisitor(ParameterExpression parameter)
    Contract.Requires(null != parameter);

    Parameter = parameter;

  public Expression Replace(Expression expression)
    return Visit(expression);

  protected override Expression VisitMember(MemberExpression node)
    if (node.Member.ReflectedType != typeof(TPublicEntity))
      return base.VisitMember(node);

    var result = Expression.Property
    return result;

3. Apply the LambdaExpression to IQueryable DbSet

Now the only part is missing is to apply the converted expression to our DbSet which also implements an IQueryable:

The easiest way to do this is to construct a Where expression via a generic IQueryable expression default(IQueryable).Where(default(Expression&lt;Func&gt;)) (as the ODATA code does this internally as well when converting filter options to expressions). It does not matter of which type this IQueryable.Where clause is as we make a generic type of it of type TDataEntity anyway:

private static MethodInfo GenericMethodInfoFor<TDataEntity>(Expression<Func<object, TDataEntity>> expression)
  var methodInfo = ((((Expression) expression) as LambdaExpression).Body as MethodCallExpression)
  return (Expression) methodInfo;

public static IQueryable<TDataEntity> Where<TDataEntity>(IQueryable source, Expression expression)
  where TDataEntity : DataEntity
  return GenericMethodInfoFor(e => default(IQueryable<int>).Where(default(Expression<Func<int, bool>>)))
      new object[]
    ) as IQueryable<TDataEntity>;

With these two helper methods. we can apply the expression to the DbSet like this:

public IQueryable<TDataEntity> Get(Expression filterExpression)
  var queryable = ExpressionHelpers.Where<TDataEntity>(DbContext.Set<TDataEntity>(), filterExpression);
  return queryable;

This is all it take to convert a $filter expression like Id ne 0L and startswith(Name, 'm') to a query expression we can use on our DbSet directly.

4. Converting OrderByQueryOption to Expression

Converting OrderByQueryOptions is nearly the same, except there we cannot use the generic version of Expression.Lambda as the return type depends on the $orderby parameter (e.g. ordering by Id requires a long and by Name requires a string). In addition, we have to remember the direction of the order (as it is not contained in the lambda expression itself.

public LambdaExpression Replace(Expression orderByExpression)
  var visitor = new MemberExpressionVisitor<TPublicEntity>(Expression.Parameter(typeof(TDataEntity), "$it"));

  var methodCallExpressionOrderBy = orderByExpression as MethodCallExpression;
  Contract.Assert(null != methodCallExpressionOrderBy);
  Contract.Assert(2 == methodCallExpressionOrderBy.Arguments.Count);

  var unquotedLambdaExpressionOrderBy = visitor.Replace(methodCallExpressionOrderBy.Arguments[1])
    .Unquote() as LambdaExpression;
  Contract.Assert(null != unquotedLambdaExpressionOrderBy);
  var isDescending = nameof(Enumerable.OrderBy) != methodCallExpressionOrderBy.Method.Name;

  var lambdaType = typeof(Func<,>).MakeGenericType(typeof(TDataEntity), unquotedLambdaExpressionOrderBy.ReturnType);
  Contract.Assert(null != lambdaType);
  var expressionLambdaOrderBy = Expression.Lambda(lambdaType, unquotedLambdaExpressionOrderBy.Body, visitor.Parameter);

  orderByExpression = expressionLambdaOrderBy;

  return orderByExpression;

5. Apply LambdaExpression to IQueryable DbSet

Now that we have the Expression we can apply it to our DbSet. When doing so, we have to remember the ordering direction and optionally check if there is more than $orderby parameter:

public static IQueryable<TDataEntity> OrderBy<TDataEntity>(IQueryable source, LambdaExpression expression, bool descending, bool alreadyOrdered = false)
  where TDataEntity : DataEntity
  var bodyType = expression.Body.Type;
  IOrderedQueryable orderedQueryable;
  if (alreadyOrdered)
    var methodInfo = !descending 
      ? ExpressionHelperMethods.QueryableThenByGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType) 
      : ExpressionHelperMethods.QueryableThenByDescendingGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType);

    var orderedQueryable2 = source as IOrderedQueryable;
    orderedQueryable = methodInfo.Invoke(null, new[]
      (object) expression
    }) as IOrderedQueryable;
    var methodInfo = !descending 
      ? ExpressionHelperMethods.QueryableOrderByGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType) 
      : ExpressionHelperMethods.QueryableOrderByDescendingGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType);
    orderedQueryable = methodInfo.Invoke(null, new[]
      (object) expression
    }) as IOrderedQueryable;
  return orderedQueryable as IQueryable<TDataEntity>;

Notice: the above code is more or less directly taken from the official Microsoft WebApi repository and is available under the MIT license. The reason for this is, that these helper classes are all internal and therefore cannot be used by our code.

If we do not want to use the code but want to create the MethodCallExpression ourselves we can do so by invoking Expression.Call directly:

var whereExpression = Expression.Call
  new[] { source.ElementType },

In this case source is our DbSet().AsQueryable() and expression the Where or OrderBy expression we want to create.

So all in all it is quite simple to convert ODataQueryOptions to expressions and apply them to our underyling LINQ Provider.


  1. Hello ronald,
    this is my expression i want to convert to lambda and always returning null.
    {BMS.API.App.Dto.TicketDto[].Where($it => (($it.TicketNumber == value(System.Web.Http.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.String]).TypedProperty) AndAlso ($it.Title == value(System.Web.Http.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.String]).TypedProperty))).OrderBy($it => $it.Id).Take(value(System.Web.Http.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)}

    • Hi Ali, difficult to tell from my perspective. I would try to reduce the query (maybe start by leaving only the `Where` clause and removing `OrderBy` and `Take`). What is the actual type of the Expression? is it a `ParameterExpression`? did you debug the statement? and what happens inside the visitor when or before `null` is returned?

      • hello ronald,iam now stuck on another problem.
        Can you tell me how are you extracting the order by expression?
        can i make it like the filter one and work it the same:queryablesOrder.Expression

  2. Dear Ronald Rink, I hope I’m correct that your article could solve my problem with an ODATA service I’m trying to build using EF (MS SQL DB). My perception is that the standard code:

    // GET: odata/Applications
    public IQueryable GetApplications(IQueryable origQuery, ODataQueryOptions options)
    return db.Application;

    Will retrieve all records and will filter the records in Memory before sending them to the client. I want to use the ODataQueryOptions to sent a LINQ expression to MS SQL and filter on a DB level.

    But I’m too novice to understand the code above or getting it to work. It doesn’t find PublicEntity. I have searched through your GitHub but I can’t seem to find the above code in a working example. Could you be so kind to help me out?

    • Dear Remy
      In the code you pasted in your comment the ODataQueryOptions get applied to the SQL query as long as you don’t force evaluation by calling something like .ToList(). So in your case the records already get filtered on database level.

      Have a look at Jon Golloways blog post about “Does ASP.NET Web API + OData filter at the database level? Let’s ask IntelliTrace.” for a detailed explanation.

      Concerning PublicEntity you are right. There is no working example public available. The complete code is in a private repository. But I can provide you the code of the PublicEntity.

      public class PublicEntity
      public long Id { get; set; }

      public virtual string Name { get; set; }

      public virtual string Description { get; set; }

      public T ShallowCopy()
      where T : PublicEntity
      => MemberwiseClone() as T;

      public virtual PublicEntity SetBaseEntityDefaults()
      Id = 0;
      Name = nameof(Name);
      Description = nameof(Description);

      return this;

      public virtual TPublicEntity Cast()
      where TPublicEntity : PublicEntity
      return this as TPublicEntity;

      PublicEntity is just a base class for all public models that get served and exposed by ODATA.

      I hope this will help you.

  3. Hi Ronald,
    Is there a sample vs solution for the above or a gist? That would be helpful.

  4. samarrizvi says:

    Hi Ronald,
    Is there a sample VS solution or a gist for the above code? That shall be very helpful.

    • Hi Samar
      No, unfortunatley there is no public sample VS solution available for the above code. The complete code is in a private repository. Let us know, what code parts you miss so that we might can provide you the missing code excerpts.

  5. Hello,
    just want to ask u what is UnQuote() used for and it is not available for me to use,

  6. Hi Ali, `Unquote` is an extension method I created that will return an unquoted expression or the original expression (if it is not quoted). As not all expressions are quoted this is a just a convenience method for extracting the expression.

    public static Expression Unquote(this Expression quote)
    return quote.NodeType == ExpressionType.Quote
    ? ((UnaryExpression) quote).Operand.Unquote()
    : quote;

  7. ah thanks man,
    another question plz, this expression is always retruning null for me but didnt use unquote)
    .Unquote() as LambdaExpression;

  8. what kind of expression are you passing to the visitor? try `var expression = visitor.Replace(methodCallExpressionFilter.Arguments[1])
    .Unquote(); var lambdaExpression = expression as LambdaExpression;` to see if your expression is really a `LambdaExpression`.

  9. want to add also a question,does your code support paging also??

  10. yes this is the problem .
    This is always null :var expression = visitor.Replace(methodCallExpressionFilter.Arguments[1])

  11. yes this is the problem .
    This is always null :var expression = visitor.Replace(methodCallExpressionFilter.Arguments[1])
    Thanks man,

  12. I mean when i cast my expression to lambdaExpression it is returning null/I mean when you do this : var lambdaExpression = expression as LambdaExpression;
    LambdaExpression here is null, why is that

  13. yes, we also created a paging manager as part of the same project that will translate the output to an ODATA compatible page (with next link, top, etc). but this is not really part of this blog post. The paging manager takes an IQueryable as the source and `QueryOptions` that we translated from `ODataQueryOptions`.


  1. […] Converting ODataQueryOptions into LINQ Expressions I needed to cascade different Lambda Expressions via AND and OR. In this post I describe a way on […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: