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

42 responses  

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

    KristoferAJune 24th, 2009
  2. pingback

    […] Client-side properties and any remote LINQ provider (Damien Guard) […]

    Dew Drop – June 25, 2009 | Alvin Ashcraft's Morning DewJune 25th, 2009
  3. pingback

    […] ORM – Client-side properties and any remote LINQ provider – Damien Guard (Suggested by David Fowler) […]

    I love .NET! » Blog Archive » The Technology Post for June 25th, 2009June 25th, 2009
  4. 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 – June 25th, 2009
  5. This is great! I can’t wait to try it out.

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

    BrianP – June 28th, 2009
  6. “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 – June 28th, 2009
  7. pingback

    […] 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 […]

    Summary 13.07.2009 « Bogdan Brinzarea’s blogJuly 13th, 2009
  8. @BrianP, something simple like typeof(MyClass).ToString(); should cause it to be initialized.

    [)amien

    Damien GuardAugust 11th, 2009
  9. 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 – March 23rd, 2010
  10. @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 FowlerMarch 24th, 2010
  11. @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 – April 7th, 2010
  12. @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 – August 30th, 2010
  13. 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 – January 12th, 2011
  14. 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 – March 21st, 2011
  15. 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

    Damien GuardMarch 21st, 2011
  16. 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 – March 22nd, 2011
  17. 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 – April 15th, 2011
  18. The Auto class was removed – it was very troublesome and had some issues.

    [)amien

    Damien GuardApril 15th, 2011
  19. Hello,

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

    bmo

    the_bmoAugust 11th, 2011
  20. You should just be able to grab the source and use it – there are no runtime dependencies on .NET 4.

    [)amien

    Damien GuardAugust 14th, 2011
  21. 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 – August 23rd, 2011
  22. 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

    Damien GuardAugust 23rd, 2011
  23. Thanks! Now it works fine!

    Eros – August 23rd, 2011
  24. But where are the source?

    the_bmoSeptember 7th, 2011
  25. 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 – September 23rd, 2011
  26. 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 – November 7th, 2011
  27. 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 WilcoxNovember 7th, 2011
  28. You can grab a copy of the ExpressionVisitor class from the IQToolkit at http://iqtoolkit.codeplex.com/

    [)amien

    Damien GuardNovember 10th, 2011
  29. 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:
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3b401086-2c58-4bd0-845b-e0209f9cdcf5

    Thanks!

    Patrick – December 9th, 2011
  30. pingback

    […] I did some research and discovered a blog post by Damien Guard and David Fowler: http://damieng.com/blog/2009/06/24/client-side-properties-and-any-remote-linq-provider. They described exactly what I was trying to do and also provided a ready solution for translating […]

    LINQ: How to dynamically map properties | peschusterMarch 6th, 2012
  31. Simple question: Can I get an expression for a property out of the TranslationMap?

    Stefan – March 20th, 2012
  32. 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.

    [)amien

    Damien GuardMarch 20th, 2012
  33. 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 – April 11th, 2012
  34. 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 GuardApril 11th, 2012
  35. 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 – December 1st, 2012
  36. 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.

    [)amien

    Damien GuardDecember 1st, 2012
  37. 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 – February 13th, 2013
  38. 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 – February 13th, 2013
  39. 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 – February 14th, 2013
  40. 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?

    AaronMarch 14th, 2013
  41. 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

    http://social.msdn.microsoft.com/Forums/en-US/silverlightwcf/thread/62d03153-d0dc-4797-8dc0-9fb207c528a9/

    AaronMarch 14th, 2013
  42. 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 – June 10th, 2013

Respond to this