LINQ: How to dynamically map properties

A few month ago i struggled with calculated properties in Entity Framework model objects.

A property like

public string FullName
{
    get { return this.FirstName + " " + this.LastName; }
}

can’t be used in a LINQ query to the database, because the LINQ provider transforming the expression tree to SQL can’t deal with it. This is kind of obvious, because there is no field named “fullName” or similar in the database, but on the other hand it shouldn’t be to difficult to achieve support for these kind of properties as it is quite simple to translate them into SQL.

The Idea

So 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 properties in LINQ expressions.

Although their solution is really great, it has some caveats:

1. You have to include a call to WithTranslations() on every query.
2. It is not possible to build queries on an common interface for multiple classes (more about this in another blog post).
3. I wanted to be able to provide different property implementations based on the current thread ui culture (I know, sounds scary…).

So I took a stab at it and tried to add the “missing” functionality. The result is a fork of Microsoft.Linq.Translations named “PropertyTranslator”.

The mayor differences between Microsoft.Linq.Translations and PropertyTranslator are the three points described above. At that time Microsoft.Linq.Translations wasn’t at GitHub, yet. Otherwise I probably would have forked it in the github-sense and tried to contribute back.

PropertyTranslator

So, how does it work? I think this is best described with a few examples:

Basic example

A POCO entity class from EntityFramework.

Although in the database only a FirstName and a LastName field exists, the property Name can be used in queries, because right before execution it is translated to FirstName + ' ' + LastName.

public class Person
{
    private static readonly CompiledExpressionMap<Person, string> fullNameExpression = 
        DefaultTranslationOf<Person>.Property(p => p.FullName).Is(p => p.FirstName + " " + p.LastName);

    public string FullName
    {
        get { return fullNameExpression.Evaluate(this); }
    }

    public string FirstName { get; set; }

    public string LastName { get; set; }        
}

UI culture dependent translations

The context: a database table, mapped with entity framework to POCO entity classes with two fields: EnglishName and GermanName. With the following snippet, you can use the Name property in LINQ queries which resolves to the name (either EnglishName or GermanName) depending on the current ui culture.

public class Country
{
    private static readonly CompiledExpressionMap<Country, string> nameExpression = 
        DefaultTranslationOf<Country>.Property(c => c.Name).Is(c => c.EnglishName);

    static Country()
    {
        DefaultTranslationOf<Country>.Property(c => c.Name).Is(c => c.EnglishName, "en");
        DefaultTranslationOf<Country>.Property(c => c.Name).Is(c => c.GermanName, "de");
    }       

    public string Name
    {
        get { return nameExpression.Evaluate(this); }
    }

    public string EnglishName { get; set; }

    public string GermanName { get; set; }      
}

How to enable PropertyTranslator

PropertyTranslator is on Nuget. So you can just add it to your project using the Package Manager Console.

You can enable PropertyTranslator by adding the PropertyVisitor to your EntityFramework ObjectSets (of course it works not only with EntityFramework but with any LINQ provider).

Best way to do this is by using David Fowler’s QueryInterceptor (also on Nuget):

using QueryInterceptor;
using PropertyTranslator;

public class MyDataContext
{
    ObjectContext context = new MyEfContext();

    public IQueryable<Person> PersonTable
    {
        get
        {
            var objectSet = context.CreateObjectSet<Person>("Persons");

            return objectSet.InterceptWith(new PropertyVisitor());
        }
    }
}

Conclusion

With PropertyTranslator it is really simple to translate calculated properties to their implementation right before execution so that it is possible to use them in LINQ queries with any LINQ provider.

Great thanks to Damien Guard and David Fowler for the general idea and their implementation. I mainly just added an additional layer of abstraction (CompiledExpressionMap) and did some restructuring/fixes to enable a convenient usage with QueryInterceptor and writing queries towards interfaces.