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:
- a public model that is served and exposed by ODATA (deriving from
TPublicEntity) - 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<Func> 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
(
Parameter,
node.Member.Name
);
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<Func>)) (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)
.Method
.GetGenericMethodDefinition();
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>>)))
.MakeGenericMethod(typeof(TDataEntity))
.Invoke
(
null,
new object[]
{
source,
expression
}
) 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[]
{
orderedQueryable2,
(object) expression
}) as IOrderedQueryable;
}
else
{
var methodInfo = !descending
? ExpressionHelperMethods.QueryableOrderByGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType)
: ExpressionHelperMethods.QueryableOrderByDescendingGeneric.MakeGenericMethod(typeof(TDataEntity), bodyType);
orderedQueryable = methodInfo.Invoke(null, new[]
{
source,
(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
(
typeof(Queryable),
"Where",
new[] { source.ElementType },
source.Expression,
expression
);
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.
Ronald Rink
I am a senior auditor and consultant at d-fens for business processes and information systems.
d-fens GmbH 
Hello,
just want to ask u what is UnQuote() used for and it is not available for me to use,
thanks
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;
}
ah thanks man,
another question plz, this expression is always retruning null for me but didnt use unquote)
visitor.Replace(methodCallExpressionFilter.Arguments[1])
.Unquote() as LambdaExpression;
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`.
yes this is the problem .
This is always null :var expression = visitor.Replace(methodCallExpressionFilter.Arguments[1])
.Unquote()
yes this is the problem .
This is always null :var expression = visitor.Replace(methodCallExpressionFilter.Arguments[1])
.Unquote()
Thanks man,
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
want to add also a question,does your code support paging also??
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`.
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
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
[EnableQuery]
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
{
[Key]
public long Id { get; set; }
[Required]
[MaxLength(2048)]
[StringLength(2048)]
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.
Thank you very much for the information.
Hi Ronald,
Is there a sample vs solution for the above or a gist? That would be helpful.
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.
Thanks for your reply. I figured it out. I may trouble you again though
It looks like something has changed, the result from GenericMethodInfoFor is being cast into an Expression, which isn’t valid, removing this results in an exception: Object of type ‘System.Linq.Expressions.MethodCallExpression2’ cannot be converted to type ‘System.Linq.Expressions.Expression`
True. Somthing changed – I just had a look against our running code. This is how it looks today:
public static IQueryable Where(IQueryable source, Expression expression)
where TDataEntity : DataEntity
{
return ExpressionHelperMethods
.QueryableWhereGeneric
.MakeGenericMethod(typeof(TDataEntity))
.Invoke
(
null,
new object[]
{
source,
expression
}
) as IQueryable;
}
public class ExpressionHelperMethods
{
public static MethodInfo QueryableWhereGeneric => _whereMethod;
private static readonly MethodInfo _whereMethod = GenericMethodOf(_ => default(IQueryable).Where(default(Expression<Func>)));
private static MethodInfo GenericMethodOf(Expression<Func> expression) => GenericMethodOf((Expression)expression);
private static MethodInfo GenericMethodOf(Expression expression) => ((expression as LambdaExpression).Body as MethodCallExpression).Method.GetGenericMethodDefinition();
}
Thanks Ronald, that helps!
Any chance you can open source the complete class? It would save me quite a bit of reverse engineering on my own.
Hi, and thanks for this interesting article.
I’m having some issues trying to get the code to work. In your reply to Roger Far you show the new code where you also have:
> private static MethodInfo GenericMethodOf(Expression expression) => GenericMethodOf((Expression)expression);
I’m unable to have this compile. – I get a CS0305: Using the generic type ‘Func’ requires 1 type arguments
Do you have a definition of Func somewhere else in your code?
Hi Steinar, thanks for your comment. I had to look this up, as this code base is quite old and dates back to 2017. The Func that is referenced is (or was) part of the OData library from Microsoft (that was open source then). I copied the bits into this reply giving full credits to Microsoft and the original authors:
private static MethodInfo GenericMethodOf(Expression<Func> expression) => GenericMethodOf((Expression)expression);
private static MethodInfo GenericMethodOf(Expression expression) => ((expression as LambdaExpression).Body as MethodCallExpression).Method.GetGenericMethodDefinition();
Besides that, I had to rewrite quite some stuff, when using .Net Core/.Net 6. So I am not sure, how valuable this article is nowadays.
Regards, Ronald
Looking to do the same with .Net 6, would love an update with your .Net 6 implementation if you are willing to share! :)