Introduction
This article will show you how you can define expressions in your code and reuse them in memory and in query expressions. The solution will work with any LINQ provider, including LINQ to SQL.
The Problem
When I first started using LINQ to SQL, I liked the fact that the LINQ to SQL designer generated partial classes. It meant that I could add custom, "calculated" properties to my entities just by adding a partial class file, like this:
public partial class OrderDetail {
public decimal Subtotal {
get { return Quantity * UnitPrice; }
}
}
I quickly ran into a problem, though, when I wanted to use this property in a query:
var bigOrders =
from o in orderContext.OrderDetails
where o.Subtotal > 1000
select o;
System.NotSupportedException : The member 'Overboard.Linq.OrderDetail.Subtotal'
has no supported translation to SQL.
LINQ to SQL doesn't know how to translate the Subtotal property into a SQL query. Unlike the Quantity and UnitPrice columns, it doesn't have a column mapping, and we can't give it one because it doesn't directly correspond to a column in a table.
There are several possible workarounds for this situation:
I wanted another option, but after looking around for a while, I found a bunch of other people with the same problem, but no solution. So I decided to make one.
Designing the Solution
LINQ to SQL is an implementation of IQueryable, which uses expression trees to translate between the LINQ standard query operators and the underlying query implementation. I realized that by building my own IQueryable implementation, I could preprocess the expression tree, and convert the Subtotal property reference (which LINQ to SQL doesn't understand) to its underlying expression (which LINQ to SQL does understand). Then, LINQ to SQL can take over and run the query as it normally would.
To accomplish this preprocessing, I made use of Matt Warren's excellent series of blog posts on implementing IQueryable, which he has since packaged into the IQ Toolkit. This is definitely something to check out if you do any advanced LINQ work, or just like digging around in the innards of stuff.
You don't need to know how expression trees work to use this solution, but it will help if you want to understand how the solution works. Charlie Calvert has a good introduction on his blog, and you can find many others by searching the web.
I had three main goals when implementing this solution:
- Easy to use. Nothing kills a clever solution faster than if no one can figure out how to make it work.
- Low visibility. I needed something that wasn't going to introduce a lot of ceremony into the code, especially since the cases where it was most likely to be used were relatively simple to begin with.
- Low impact. I wanted something that would have little effect on calling code or throughput when it was being used, and none at all when it wasn't.
The Basics
First, we will go through the basic steps of using QueryMap in your code, and then we will look at how we can make using it a little easier.
As was already mentioned, this solution is going to work with expression trees. That means the first thing we need to do is redefine Subtotal as an expression tree:
private static readonly Expression<Func<OrderDetail, decimal>>
_subtotalExpression = od => od.Quantity * od.UnitPrice;
But we still need to be able to use Subtotal like a regular property. How do we do that using an expression tree? We use the fact that all generated expression trees can be compiled into delegates, which execute the expression just as if it had been written as a normal method:
private static readonly Func<OrderDetail, decimal> _subtotalLambda = _subtotalExpression.Compile();
public decimal Subtotal {
get { return _subtotalLambda(this); }
}
Now we can call Subtotal like a regular property, and we also have its definition as an expression tree. Next, we need a way to associate the property with the expression tree. The MapToExpressionAtrribute tells QueryMap where to get the definition of the Subtotal property:
[MapToExpression("_subtotalExpression")]
public decimal Subtotal {
get { return _subtotalLambda(this); }
}
The final step is to hook the preprocessor into the query, using the AsMapped extension method:
var orders =
from o in context.OrderDetails.AsMapped()
where o.Subtotal > 1000
select o;
AsMapped is an extension method for IQueryable, so you will need to have imported the Overboard.Linq namespace. Similar to AsQueryable, AsMapped changes the query provider that will be used to process the query, in this case to the MappedQueryProvider. The original query provider, be it LINQ to SQL or anything else, is stored so that it can be used to produce the final query after the preprocessor has finished its work.
That is all that is required to make it work! Not too bad, but we can make it a little easier.
Cleaning Up
Let's start by cleaning up the way we declare the expression tree. Right now, we have to define the expression tree and the compiled delegate separately; we can combine those into a single definition, using the ExpressionMethod class:
private static readonly ExpressionMethod<Func<OrderDetail, decimal>> _subtotal =
new ExpressionMethod<Func<OrderDetail, decimal>>(od => od.Quantity * od.UnitPrice);
The ExpressionMethod class automatically performs the step of compiling the expression tree into a delegate, and exposes that delegate in its Invoke property. A small change to the associated property definition is required:
[MapToExpression("_subtotal")]
public decimal Subtotal {
get { return _subtotal.Invoke(this); }
}
Now we're down to one definition, but its pretty long, with a lot of ugly angle brackets. Fortunately, we can take advantage of type inference to create some helper methods which will make it a little less painful:
private static readonly ExpressionMethod<Func<OrderDetail, decimal>> _subtotal =
ExpressionMethod.Create((OrderDetail od) => od.Quantity * od.UnitPrice);
ExpressionMethod.Create has 9 overloads, one for each variation of the Func and Action delegates. However, one of them is different from the others. Whenever we are using an expression which takes one parameter and returns a value (which will always be the case when the expression represents a property), the Create method returns an instance of a class derived from ExpressionMethod, which substitutes its type parameters into a Func delegate. The result is a slightly more succint left-hand side of the ExpressionMethod declaration:
private static readonly ExpressionMethod<OrderDetail, decimal> _subtotal =
ExpressionMethod.Create((OrderDetail od) => od.Quantity * od.UnitPrice);
So, we started with this...
public decimal Subtotal {
get { return Quantity * UnitPrice; }
}
var orders =
from o in context.OrderDetails
where o.Subtotal > 1000
select o;
...and we ended up with this...
private static readonly ExpressionMethod<OrderDetail, decimal> _subtotal =
ExpressionMethod.Create((OrderDetail od) => od.Quantity * od.UnitPrice);
[MapToExpression("_subtotal")]
public decimal Subtotal {
get { return _subtotal.Invoke(this); }
}
var orders =
from o in context.OrderDetails.AsMapped()
where o.Subtotal > 1000
select o;
A little extra code gives us the ability to define a calculated property once, and then reuse that definition in regular code and in query expressions. In LINQ to SQL, this query would produce the following SQL.
Manual Mapping
Attribute-based mapping is great when you are adding custom properties to your own types. But what if you want to map an expression to a member on a type you don't control? You can create your own mapping for any member on any type by passing the member and the expression that should replace it to the AsMapped method:
public decimal Total {
get { return Subtotal; }
}
var totalProperty = typeof(Order).GetProperty("Total");
var orders = from o in context.Orders
.AsMapped(totalProperty, x => x.Subtotal)
where o.Total > 10000
select o;
You can repeatedly call AsMapped to set additional mappings. You can also store the result of the AsMapped call, which is an instance of IMappedQuery<T>, and then call SetMapping on that.
You should be careful using this method; while it can be useful, it also runs the risk of the member definition and the replacement expression getting out of sync.
The Details
Terminology:
- Mapped Member: The member which needs to be replaced during query processing (e.g.,
Subtotal in the example above). - Target Member: The member identified by the
MapToExpressionAttribute on the mapped member (e.g., _subtotal in the example above). - Target Expression: The expression (or
ExpressionMethod), obtained by retrieving the value of the target member, that is used to replace the mapped member during query processing (e.g., the value of _subtotal in the example above).
The MapToExpressionAttribute can be applied to any instance or static field, property, or method (the mapped member). The argument is the name of a target field, property, or method in the same class (the target member) which has a return type of either LambdaExpression or ExpressionMethod. The value of that member (the target expression) will be the expression that is substituted for the mapped member.
The parameters in the target expression must match the parameters of the mapped member (if any). If the mapped member is an instance member, then the expression that is used to replace it must have as its first parameter the class containing the mapped member (similar to the way extension methods are evaluated). This is the case in the Subtotal example; Subtotal is an instance property, and the expression which replaces it has as its first (and only) parameter a reference to an OrderDetail.
The target member should usually be a static field. It can be instance if the instance it is called on can be completely evaluated prior to the execution of the query. It can also be a property or a method instead of a field. I can't think of a use case where this flexibility would be necessary or desirable, but I wanted to enable it in case someone comes up with one. Generally, you should stick to static fields as target expressions (such as _subtotal in the example above).
Let's look at how QueryMap uses the MapToExpressionAttribute to convert expressions into their underlying representation:
- When the query is ready to be processed (for example, when it is used as the source in a
foreach loop), QueryMap examines every field, property, and method that is referenced in the expression tree for the MapToExpressionAttribute. These are the mapped members. - For every member which has the
MapToExpressionAttribute, the QueryMap locates the member in the same class that is named in the attribute. This is the target member. The target member must be a field, a non-indexed property, or a method with no parameters. If the target member cannot be found, an exception is thrown. - The target member is evaluated. Its return value must be a
LambdaExpression (from which Expression<TDelegate> descends) or an ExpressionMethod. If it is not one of these two types, an exception is thrown. Otherwise, the return value becomes the target expression. - The target expression is substituted into the expression tree for the mapped member. If the mapped member is instance, then the instance on which it was called is passed as the first parameter to the target expression. If the mapped member had parameters, they are also passed in to the target expression. If the target expression does not have the right number of parameters of the right type, an exception is thrown.
- The target expression is evaluated recursively, using the same steps.
That last point is interesting, in that it can be used to create mapped members which refer to other mapped members. For example, we could define a Subtotal on Order:
private static readonly ExpressionMethod<Order, decimal> _subtotal =
ExpressionMethod.Create((Order o) => o.OrderDetails.Sum(od => od.Subtotal));
[MapToExpression("_subtotal")]
public decimal Subtotal {
get { return _subtotal.Invoke(this); }
}
var orders = from o in context.Orders.AsMapped()
select o.Subtotal;
When this query is processed, the QueryMap will first replace the reference to Order.Subtotal with its underlying expression, and then in that expression, it will replace the reference to OrderDetail.Subtotal with its underlying expression.
History
- 4/22/2012 - Initial release
