Client-side properties and any remote LINQ provider

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

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

43 responses

  1. Avatar for KristoferA

    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...

    KristoferA 24 June 2009
  2. Avatar for shawn

    Nice, now that's what I'm talking about! Any chance we can get something like this baked into the framework for 4.0? ;)

    shawn 25 June 2009
  3. Avatar for BrianP

    This is great! I can't wait to try it out.

    I agree with shawn, this should be standard part of linq.

    BrianP 28 June 2009
  4. Avatar for BrianP

    "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?

    BrianP 28 June 2009
  5. Avatar for Damien Guard

    @BrianP, something simple like typeof(MyClass).ToString(); should cause it to be initialized.

    Damien Guard 11 August 2009
  6. Avatar for Shaul B

    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 23 March 2010
  7. Avatar for David Fowler

    @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 24 March 2010
  8. Avatar for Shaul B

    @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... :(

    Shaul B 7 April 2010
  9. Avatar for Matyas Boros

    @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?

    Matyas Boros 30 August 2010
  10. Avatar for AaronSieb

    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.

    AaronSieb 12 January 2011
  11. Avatar for Sean

    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.

    Sean 21 March 2011
  12. Avatar for Damien Guard

    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.

    Damien Guard 21 March 2011
  13. Avatar for Sean

    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.

    Sean 22 March 2011
  14. Avatar for Stefan

    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?

    Stefan 15 April 2011
  15. Avatar for Damien Guard

    The Auto class was removed - it was very troublesome and had some issues.

    Damien Guard 15 April 2011
  16. Avatar for the_bmo

    Hello,

    Does Microsoft.Linq.Translations exist for 3.5 SP1 ? My project is a vs2008 project ( no nuget ) :(

    bmo

    the_bmo 11 August 2011
  17. Avatar for Damien Guard

    You should just be able to grab the source and use it - there are no runtime dependencies on .NET 4.

    Damien Guard 14 August 2011
  18. Avatar for Eros

    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!

    Eros 23 August 2011
  19. Avatar for Damien Guard

    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).

    Damien Guard 23 August 2011
  20. Avatar for Eros

    Thanks! Now it works fine!

    Eros 23 August 2011
  21. Avatar for the_bmo

    But where are the source?

    the_bmo 7 September 2011
  22. Avatar for Fred the frog

    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.

    Fred the frog 23 September 2011
  23. Avatar for Radek

    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.

    Radek 7 November 2011
  24. Avatar for Don Wilcox

    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.

    Don Wilcox 7 November 2011
  25. Avatar for Damien Guard

    You can grab a copy of the ExpressionVisitor class from the IQToolkit

    Damien Guard 10 November 2011
  26. Avatar for Patrick

    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!

    Patrick 9 December 2011
  27. Avatar for Stefan

    Simple question: Can I get an expression for a property out of the TranslationMap?

    Stefan 20 March 2012
  28. Avatar for Damien Guard

    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.

    Damien Guard 20 March 2012
  29. Avatar for Dave

    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?

    Dave 11 April 2012
  30. Avatar for Damien Guard

    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.

    Damien Guard 11 April 2012
  31. Avatar for Zarepheth

    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.

    Zarepheth 1 December 2012
  32. Avatar for Damien Guard

    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.

    Damien Guard 1 December 2012
  33. Avatar for Lucas

    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?

    Lucas 13 February 2013
  34. Avatar for Lucas

    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...

    Lucas 13 February 2013
  35. Avatar for Lucas

    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.

    Lucas 14 February 2013
  36. Avatar for Aaron

    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?

    Aaron 14 March 2013
  37. Avatar for Aaron

    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/

    Aaron 14 March 2013
  38. Avatar for bojingo

    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.

    bojingo 10 June 2013
  39. Avatar for Nadège

    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

    Nadège 9 March 2016
  40. Avatar for Damien Guard

    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?

    Damien Guard 9 March 2016
  41. Avatar for Nadège

    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?

    Nadège 10 March 2016
  42. Avatar for Bekir Arslan

    Hi, we have been using this almost six years, it worked very well with EF 5. But when we upgraded EF to version 6 it is not working as expected. May be ProxyCreationEnabled setting is wrecking it.

    Is it compatible with EF6?

    Bekir Arslan 21 May 2018
  43. Avatar for Max

    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

    Max 9 August 2018