Client-side properties and any remote LINQ provider
David Fowler on the ASP.NET team and I have been bouncing ideas about on how to solve an annoyance using LINQ:
If you write properties on the client you can’t use them in remote LINQ operations.
The problem occurs because these properties can’t be translated and sent to the server as they have been compiled into intermediate language (IL) and not LINQ expression trees that are required for translation by IQueryable implementations. There is nothing available in .NET to let us reverse-engineer the IL back into the methods and syntax that would allow us to translate the intended operation into a remote query.
This means you end up having to write your query in two parts; firstly the part the server can do, a ToList or AsEnumerable call to force that to happen and bring the intermediate results down to the client, and then the operations that can only be evaluated locally. This can hurt performance if you want to reduce or transform the result set significantly.
What we came up (David, Colin Meek and myself) is a provider-independent way of declaring properties just once so they can be used in both scenarios. Computed properties for LINQ to SQL, LINQ to Entities and anything else LINQ enabled with little effort and it works great on .NET 3.5 SP1 :)
Before example
Here we have extended the Employee class to add Age and FullName. We only wanted to people with “da” in their name but we are forced to pull down everything to the client in order to the do the selection.
partial class Employee {
public string FullName {
get { return Forename + " " + Surname; }
}
public int Age {
get { return DateTime.Now.Year - BirthDate.Year -
(((DateTime.Now.Month < BirthDate.Month)
|| DateTime.Now.Month == BirthDate.Month && DateTime.Now.Day < BirthDate.Day) ? 1 : 0));
}
}
}
...
var employees = db.Employees.ToList().Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age);After example
Here using our approach it all happens server side… and works on both LINQ to Entities and LINQ to SQL.
partial class Employee {
private static readonly CompiledExpression<Employee,string> fullNameExpression
= DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
private static readonly CompiledExpression<Employee,int> ageExpression
= DefaultTranslationOf<Employee>.Property(e => e.Age).Is(e => DateTime.Now.Year - e.BirthDate.Value.Year - (((DateTime.Now.Month < e.BirthDate.Value.Month) || (DateTime.Now.Month == e.BirthDate.Value.Month && DateTime.Now.Day < e.BirthDate.Value.Day)) ? 1 : 0)));
public string FullName {
get { return fullNameExpression.Evaluate(this); }
}
public int Age {
get { return ageExpression.Evaluate(this); }
}
}
...
var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();Getting started
Usage considerations
The caveats to the usage technique shown above is you need to ensure your class has been initialized before you write queries to it (check out alternatives below) and obviously the expression you register for a property must be able to be translated to the remote store so you will need to constrain yourself to the methods and operators your IQueryable provider supports.
There are a few alternative ways to use this rather than the specific examples above.
Registering the expressions
You can register the properties in the class itself as shown in the examples which means the properties themselves can evaluate the expressions without any reflection calls. Alternatively if performance is less critical you can register them elsewhere and have the methods look up their values dynamically via reflection. e.g.
...
DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();
...
partial class Employee {
public string FullName { get { return DefaultTranslationOf<Employees>.Evaluate<string>(this, MethodInfo.GetCurrentMethod());} }
}If performance of the client-side properties is critical then you can always have them as regular get properties with the full code in there at the expense of having the calculation duplicated, once in IL in the property and once as an expression for the translation.
Different maps for different scenarios
Sometimes certain parts of your application may want to run with different translations for different scenarios, performance etc. No problem!
The WithTranslations method normally operates against the default translation map (accessed with DefaultTranslationOf) but there is also another overload that takes a TranslationMap you can build for specific scenarios, e.g.
var myTranslationMap = new TranslationMap();
myTranslationMap.Add<Employees, string>(e => e.Name, e => e.FirstName + " " + e.LastName);
var results = (from e in db.Employees where e.Name.Contains("martin") select e).WithTranslations(myTranslationMap).ToList();How it works
CompiledExpression<T, TResult>
The first thing we needed to do was get the user-written client-side “computed” properties out of IL and back into expression trees so we could translate them. Given that we also want to evaluate them on the client we need to compile them at run time so CompiledExpression exists which just takes an expression of Func<T, TResult>, compiles it and allows evaluation of objects against the compiled version.
ExpressiveExtensions
This little class provides both the WithTranslations extensions methods and the internal TranslatingVisitor that unravels the property accesses into their actual registered Func<T, TResult> expressions via the TranslationMap so that the underlying LINQ provider can deal with that instead.
TranslationMap
We need to have a map of properties to compiled expressions and for that purpose TranslationMap exists. You can create a TranslationMap by hand and pass it in to WithTranslations if you want to programmatically create them at runtime or have different ones for different scenarios but generally you will want to use…
DefaultTranslationOf
This helper class lets you register properties against the default TranslationMap we use when nothing is passed to WithTranslations. It also allows you to lookup what is already registered so you can evaluate to that although there is a small reflection performance penalty for that:
public int Age { get { return DefaultTranslationOf<Employees>.Evaluate<int>(this, MethodInfo.GetCurrentMethod()); } }Have fun!
[)amien
29 responses
This is *very* neat, thanks for the sample & the translation lib. This is at least as handy as System.Linq.Dynamic (which is one of my favorites :) ).
Just one note though: when used as the only discriminator in a where clause like in the full name example, SQL Server will be unable to use any indexes on the underlying fields. In other words, this is fine to use in projections, as ‘non-primary’ discriminators, or on small tables/result sets.
E.g. the fullname where clause will translate to something along the lines:
WHERE ((([t0].[Forename] + @p0) + [t0].[Surname]) LIKE @p1)
…and that basically tells SQL Server to concatenate forename + p0 + surname in all records in the table and then apply the like comparison…
[...] Client-side properties and any remote LINQ provider (Damien Guard) [...]
[...] ORM – Client-side properties and any remote LINQ provider – Damien Guard (Suggested by David Fowler) [...]
Nice, now that’s what I’m talking about! Any chance we can get something like this baked into the framework for 4.0? ;)
This is great! I can’t wait to try it out.
I agree with shawn, this should be standard part of linq.
“The caveat to the usage technique shown above is you need to ensure your class has been initialized before you write queries to it. If this is a problem check out the usage considerations section below.”
I would like to keep the property expressions in the class itself, are there any other recommended ways of ensuring the class gets initialized before querying?
[...] Guard shows how to use client-side properties in LINQ remote queries eliminating the need to retrieve all the data from the server side and applying additional [...]
@BrianP, something simple like typeof(MyClass).ToString(); should cause it to be initialized.
[)amien
Very cool, thank you!
Just one problem – if you include “using Microsoft.Linq.Translations.Auto;” it creates an ambiguity with System.Linq.Queryable.Where extension, as well as the System.Linq.Queryable.Contains extension. How do you work around that?
@Shaul B
Do you have both of the namespaces included? (i.e the default Linq namespace and Microsoft.Linq.Translations.Auto) They can’t be used together since one overrides the other’s behavior and have the same signatures.
@David Fowler:
Yes, I was trying to include both namespaces. I can’t omit System.Linq, because that has some other extensions (e.g. First()) that I also need to use.
Effectively this means that I can’t use the Auto translations at all, which is a big pity… :(
@David: You can create the missing extension methods easily by hand, and then not use the original System.Linq ones. You have the examples in the AutoTranslation.cs file. There is no magic, each one is 1 line. I agree that in the next update these should be included by default,
My problem is a bit different as the typeof(MyClass).ToString() does not initialize the static fields.
Checking the c# language specification for 3.0 (point 10.5.5.1 Static field initialization), the static field is initialized before it’s access (what can be too late in our case if there is an sql query executed with this property usage), or before the static constructor. The static constructor is called when a static field is accessed, or an instance is created. So for this to work properly, we have to make sure, that all Entities having such properties have a static constructor (it can be empty), and that a static field is called or an instance is created.
So find a suitable place that gets called only once, and before any query gets executed (either Global.Application_Start(..) or a surely accessed class’s static constructor – in my case my UserControlBase class), and either call a customly defined static field on the entity, or create an instance of it.
It is quite an ugly workaround, and i would be very interested in any better ones!! Ideas?
As a naive solution to @Matyas Boros’s problem I’ve added the following code to my DataContext class:
This ensures that all of your classes are in the same assembly as your data context.
It works in small-scale tests on my dev machine, but I haven’t tested it for performance yet.
I really like this. Is there any way to do something similar with Include paths in an EF query?
The EF LINQ provider blows up when trying to do the Include.
I’m not sure but can give you some pointers on how to figure it out (if possible).
What you’ll want to do is write two queries – the one you have above and another that has .Include(e => e.ProtectedThings) and then examine the two trees.
It’s probably the WithTranslations not recognizing the particular pattern that Include uses and is therefore not substituting it correctly. Once you see the difference it shouldn’t be too hard to compensate for it.
[)amien
Thanks for the pointers Damien. I examined the trees and could see the differences, but I’m not sure that the translation can be done. The include paths are in a ConstantExpression which is of an internal type System.Data.Objects.Span, so i can’t inspect it and see what the paths actually are. Of course it is possible that I don’t know enough about this stuff to do it.
Anyway, what I’ll probably do is create my own Include extension method and do the translations there.
Hello, great lib!
Only one problem: I installed 1.0.1 via NuGet, but I don’t have the .Auto namespace at all. Any clues?
The Auto class was removed – it was very troublesome and had some issues.
[)amien
Hello,
Does Microsoft.Linq.Translations exist for 3.5 SP1 ?
My project is a vs2008 project ( no nuget ) :(
bmo
You should just be able to grab the source and use it – there are no runtime dependencies on .NET 4.
[)amien
Very useful article, but I wonder: there is a way to use something like this?
I need a way to build the OrderBy sql string at runtime, also on the custom properties.
Many thanks!
To create LINQ expressions with strings instead of lambda’s check out the DynamicLinq samples in your VS2010 folder (it’s in the C# Samples folder inside c:\Program Files\Microsoft Visual Studio).
[)amien
Thanks! Now it works fine!
But where are the source?
Good job.
I easily managed to make it work in a standard way.
But I hurt myself banging my head on a brick wall. I wish to have two levels of translation and don’t seem to be capable of.
My “mission” is to declare the client side property in an interface and having the implementation of the property made in the extension class of the interface so that many entities can expose the new client property.
One of the trick was to transform the code so that to now accept a method instead of a property as client side (interface extension cannot extend properties if I’m right). That I managed ok, me thinks.
The second trick that gets me crazy is now I need to implement a second level of client side property so that in the expression of the DefaultTranslationOf, I want to use a second level of client side property so that classes that implement the interface can specify the server side fields to use in the expression. (Hope I am not talking german now).
So to illustrate
interface:
InterfaceExtension:
Now in the derived class:
… and finally some query method …
My thinking is that my problem is that it does not manage to translate a IDataStatus.StatusId because the declaration in the map is a Connection.StatusId…
What do you think?
Sorry for the long post but I wished to illustrate my saying.
Thanks.
Great lib, thanks!
Unfortunately, I am unable to build it for .NET 3.5, because I am missing ExpressionVisitor class.
Any suggestions for this one?
Thanks.
Damien,
I have attempted to use your library so solve a problem I originally asked in StackOverflow. I have a class ClientID to wrap an Int64 ID:
class ClientID {
public Int64 Value {
get { return cidExpression.Evaluate(this); }
private set { m_Value = value; }
}
public static bool operator ==(ClientID lhs, Int64 rhs) { return lhs.Value == rhs; }
public static bool operator ==(Int64 lhs, ClientID rhs) { return rhs == lhs; }
private Int64 m_Value;
private static readonly CompiledExpression cidExpression = DefaultTranslationOf.Property(e => e.Value).Is(e => e.m_Value);
}
I use it as:
var q = dc.Notes.Where(f => f.CreatorParty.ClientID == clientID).WithTranslations().ToList();
but I still receive the “Could not format node ‘Value’ for execute as SQL.” error. I know the class is being initialized, because I am passing a ClientID as a parameter to the search method containing the LINQ code.
You can grab a copy of the ExpressionVisitor class from the IQToolkit at http://iqtoolkit.codeplex.com/
[)amien
Great work! Any suggestions on how this might be used with interfaces?
Say you have the interface:
and various classes that implement the interface such as:
Here’s what I’m attempting to do at the moment, which doesn’t work because the interface is being used:
Any ideas? I think the post above was attempting something similar. I found another post at the following address, but couldn’t quite figure out what the solution was:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3b401086-2c58-4bd0-845b-e0209f9cdcf5
Thanks!