Client-side properties and any remote LINQ provider

📅 24/06/2009
📝 1,083 words
🕙 5 minutes
📦 .NET
🏷️ Entity Framework, LINQ, C#
💬 43 responses

David Fowler on the ASP.NET team and I have been bouncing ideas about 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. They have been compiled into Intermediate Language (IL) and not expression trees. LINQ requires expression trees to translate them using IQueryable providers. 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 perform. Then a ToList or AsEnumerable call to force that to happen and bring the intermediate results down to the client. Then the second part with operations that must be evaluated locally. This process can hurt performance if you want to reduce or transform the result set significantly.

What David, Colin Meek, and I came up with is a provider-independent way of declaring properties just once so they are usable in both scenarios. Computed properties for LINQ to SQL, LINQ to Entities and anything else LINQ enabled with little effort, and it works on .NET 3.5 SP1 :)

Before example

Here we have extended the Employee class to add Age and FullName. We only wanted people with “da” in their name but are forced to pull everything to the client to perform 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

Binaries at NuGet or Source on GitHub

Usage considerations

The caveats to the technique shown above are:

You need to ensure your class is initialized before you write queries with it (see alternatives below).
The expression you register for a property must be translatable to the remote store. You 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. This 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());} }
}

When the performance of these client-side properties is critical, you can 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 we also want to evaluate them on the client, we need to compile them at run time, so CompiledExpression exists. It 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 to WithTranslations if you want to programmatically create them at run-time or have different ones for different scenarios, but generally, you will want to use DefaultTranslationOf.

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 look-up what is already registered so you can evaluate that although there is a small reflection performance penalty:

public int Age { get { return DefaultTranslationOf<Employees>.Evaluate<int>(this, MethodInfo.GetCurrentMethod()); } }

Have fun!

[)amien

42 responses to Client-side properties and any remote LINQ provider

Information is only used to show your comment. See my Privacy Policy.
Max09/08/2018
There is any way to include the property without including in where ?
Something like that:
var result = context.Employee.Include(p=> p.FullName)
Otherwise for getting the FullName value I must include it on where clause
Nadège10/03/2016
Hello, The error i’m getting is this one : “Cannot compare elements of type ‘System.Collections.Generic.ICollection`1 Only primitive types, enumeration types and entity types are supported”. So it correspond to what you said earlier. Does it exist a way to solve this kind of issue?
Damien Guard09/03/2016
For the server side to filter on it the expression must be translatable to the remote source. String.Join is definitely not something that can be translated as it has no SQL equivalent. Is that the error you are getting?
Nadège09/03/2016
Hello, I’m facing the same problem but i cannot find a way to solve this.
I’m working with EF4. My class “User” contains a list of object “Actors”. I’ve created a custom propery which evaluate a compiledExpression named “ActorsName”. The mapper between entity and model is only like : model.ActorsName = entity = ActorsName. The “WithTranslations()” method is called over the query. The datagrid is bound on the property ActorsName correctly. The datas are displayed.
However, i cannot filter or sort this computed column. Does someone have a suggestion for me? Thanks
Informations:
Custom partial class of the entity :
private static readonly CompiledExpression LibActorsNameExpression = DefaultTranslationOf.Property(e => e.ActorsName).Is(u=> u.Actors != null ? string.Join(", ", u.Actors.OrderBy(o => o.Name).Select(item => item.Name).ToArray()) : string.Empty); public string ActorsName{ get { return LibActorsNameExpression.Evaluate(this); } } }
Model :
public string ActorsName {get;set;
Mapper :
model.ActorsName = entity.ActorsName;
The datasource (i’m using kendo ui Grid)
DataSourceResult result = usersrv.GetAll().ApplyKendoRequest( request, // contains filter, sort, paging, ... c => c.Nom, // default property to be sorted c => c.ToModel(this.User.Identity.Name, 1), // calling the mapper );
ApplyKendoRequest : contains the query where we applied the method WithTranslations() on each call
bojingo10/06/2013
We use strong name our assemblies and, because of that, can only reference other strong named assemblies. You guys forgot to strong name Microsoft.Linq.Translations!
I am getting: Error 1 Assembly generation failed -- Referenced assembly ‘Microsoft.Linq.Translations’ does not have a strong name
Ugh.
Aaron14/03/2013
I was able to solve my dilemma by overriding to the Query function and injecting a call to WithTranslations supplying the original expression.
I based my code to override the query function off code found at the following forum post
https://social.msdn.microsoft.com/Forums/en-US/silverlightwcf/thread/62d03153-d0dc-4797–8dc0–9fb207c528a9/
Aaron14/03/2013
This looks awesome however I’m having a problem with the constraints I have. I’m using this in a silverlight app where sorting/filtering and such can be done client side. Client-side the linq is generated to query my entities from RIA. However, with the query coming through in this manner there’s no call to “WithTranslations()".
Any thoughts on a way to inject the call?
Lucas14/02/2013
Righto! I narrowed down the source of problem. My apologies for the litany of posts.
I’m writing LINQ queries using System.Data.Objects.EntityFunctions and System.Data.Objects.SqlClient.SqlFunctions, both of which use the EdmFunctionAttribute to map their canonical function to their equivallent store functions. When i use these functions in my LINQ I get the classic “LINQ to Entities does not recognize the method…” error.
I then realised there is another SqlFunctions class at System.Data.Entity.Core.Objects.SqlClient.SqlFunctions which uses DbFunctionAttribute instead of EdmFunctionAttribute. The canonical functions in this class seem to work fine with Linq to Entities, in EntityFramework v5 and v6-alpha2.
I guess my next question would be, what’s the purpose of EdmFunctionAttribute; but that’s for another time. :)
Thanks again for this great blog post Damien.
Lucas13/02/2013
After further testing it seems to be version 6.0.0-alpha2 of Entity Framework that is the cause of my problem. Whenever I try to use SqlFunctions or EntityFunctions from within a CompiledExpression the Canonical function isn’t recognised by the LINQ to Entities provider. When I use version 5.0 of Entity Framework, it works fine. Weird…
Lucas13/02/2013
Great solution for this problem guys. it would be great to see this sort of thing baked into the .net Framework.
Having said that, I seem to be having trouble using canonical functions from the SqlFunctions and EntityFunctions classes. Would you expect functions like SqlFunctions.DateDiff to work within the expression of a CompiledExpression?
Damien Guard01/12/2012
What are you binding LinqDataSource too?
I suspect WithTranslations() is not in the method chain. You need to make sure this method is part of the evaluation so it can rewrite the expression tree.
Zarepheth01/12/2012
I’ve been trying this to use a custom property in a LinqDataSource’s orderby attribute. However, I’m still getting “The member ‘xxxx’ has no supported translation to SQL.” when performing a databind on a GridView which uses that LinqDataSource.
It’s as though it’s not even attempting to resolve the property’s compiled expression.
The custom property simply returns a boolean true/false based upon whether or not a datetime column in the underlying table has a value (e.DateFinished.HasValue). With straight SQL, I’d use a CASE expression in the ORDER BY clause. In LINQ to SQL code, I’d just specify the e.DateFinished.HasValue. But an asp:LinqDataSource object doesn’t like either approach.
Damien Guard11/04/2012
They should work just fine but bear in mind that even on the server an order by based on a calculation (which is what this effectively is) will not be as fast as one based on a stored value.
Dave11/04/2012
Great work Damien! Are there any tricks to setting up these client side properties so that they can be used in OrderBy without needing to jump out to Ling to Objects?
Damien Guard20/03/2012
You can indeed — TranslationMap is a dictionary of PropertyInfo to CompiledExpression.
You’ll need to add a getter to CompiledExpression to get out the Expression though.
Stefan20/03/2012
Simple question: Can I get an expression for a property out of the TranslationMap?
Patrick09/12/2011
Great work! Any suggestions on how this might be used with interfaces?
Say you have the interface:
public interface IElement { int UniqueId { get; } }
and various classes that implement the interface such as:
public class User : IElement { private static readonly CompiledExpression _idExpression = DefaultTranslationOf.Property(x => x.UniqueId).Is(x => x.UserId); public int UniqueId { get { return _idExpression.Evaluate(this); } } public int UserId { get; set; } }
Here’s what I’m attempting to do at the moment, which doesn’t work because the interface is being used:
IQueryable source; // (could be assigned with an IQueryable on instantiation) IList results = source.Where(x => x.UniqueId == 1).WithTranslations().ToList(); // this line causes the problem, as long as the interface is 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: https://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3b401086–2c58–4bd0–845b-e0209f9cdcf5
Thanks!
Damien Guard10/11/2011
You can grab a copy of the ExpressionVisitor class from the IQToolkit
Don Wilcox07/11/2011
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.
Radek07/11/2011
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.
Fred the frog23/09/2011
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:
public interface IDataStatus { Guid StatusId { get; }
InterfaceExtension:
public static class DataStatusExtensions { private static readonly CompiledExpression closeStatusExpression = DefaultTranslationOf.Property(e => e.CloseStatus()).Is(e => (e.StatusId == EnumStatusIDs.Closed) || (e.StatusId == EnumStatusIDs.Detached)); public static Boolean CloseStatus(this IDataStatus dataStatus) { return closeStatusExpression.Evaluate(dataStatus); } }
Now in the derived class:
partial class Connection : IDataStatus { private static readonly CompiledExpression statusIdExpression = DefaultTranslationOf.Property(e => e.StatusId).Is(e => (e.EnumConnectionStatusID == null) ? Guid.Empty : e.EnumConnectionStatusID.Value); public Guid StatusId { get { return statusIdExpression.Evaluate(this); }
and finally some query method
query = query.Where(it => it.CloseStatus()).WithTranslations();
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.
the_bmo07/09/2011
But where are the source?
Eros23/08/2011
Thanks! Now it works fine!
Damien Guard23/08/2011
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).
Eros23/08/2011
Very useful article, but I wonder: there is a way to use something like this?
db.Employees.Where(e => e.FullName.Contains("da")).OrderBy("it.FullName ASC").WithTranslations();
I need a way to build the OrderBy sql string at runtime, also on the custom properties.
Many thanks!
Damien Guard14/08/2011
You should just be able to grab the source and use it — there are no runtime dependencies on .NET 4.
the_bmo11/08/2011
Hello,
Does Microsoft.Linq.Translations exist for 3.5 SP1 ? My project is a vs2008 project ( no nuget ) :(
bmo
Damien Guard15/04/2011
The Auto class was removed — it was very troublesome and had some issues.
Stefan15/04/2011
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?
Sean22/03/2011
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.
Damien Guard21/03/2011
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.
Sean21/03/2011
I really like this. Is there any way to do something similar with Include paths in an EF query?
public class Employee { private static readonly CompiledExpression<Employee,IEnumerable> thingsExpression = DefaultTranslationOf.Property(e => e.Things).Is(e => e.ProtectedThings); public int Id { get; set; } protected ICollection ProtectedThings { get; set; } public IEnumerable Things { get { return thingsExpressions.Evaluate(this); } } } var employees = db.Employees .Include(e => e.Things) .Where(e => e.Things.Any(t => true)) .WithTranslations() .ToArray();
The EF LINQ provider blows up when trying to do the Include.
AaronSieb12/01/2011
As a naive solution to @Matyas Boros’s problem I’ve added the following code to my DataContext class:
static WikiTomeDataContext() { Assembly currentAssembly = Assembly.GetExecutingAssembly(); foreach(var type in currentAssembly.GetExportedTypes()) System.Runtime.CompilerServices.RuntimeHelpers.RunClassConstructor(type.TypeHandle); }
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.
Matyas Boros30/08/2010
@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?
Shaul B07/04/2010
@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 Fowler24/03/2010
@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.
Shaul B23/03/2010
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?
Damien Guard11/08/2009
@BrianP, something simple like typeof(MyClass).ToString(); should cause it to be initialized.
BrianP28/06/2009
“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?
BrianP28/06/2009
This is great! I can’t wait to try it out.
I agree with shawn, this should be standard part of linq.
shawn25/06/2009
Nice, now that’s what I’m talking about! Any chance we can get something like this baked into the framework for 4.0? ;)
KristoferA24/06/2009
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…