Translating Where() to sql

Question

Hi.

I saw DamienG's article (http://damieng.com/blog/2009/06/24/client-side-properties-and-any-remote-linq-provider) in how to map client properties to sql. i ran throgh this article, and i saw great potential in it. Definitely mapping client properties to SQL is an awesome idea.

But i wanted to use this for something a bit more complicated then just concatenating strings. Atm we are trying to introduce multilinguality to our Business objects, and i hoped we could leave all the existing linq2sql queries intact, and just change the code of the multilingual properties, so they would actually return the given property in the CurrentUICulture.

The first idea was to change these fields to XMLs, and then try the Object.Property.Elements().Where(...), but it got stuck on the Elements(), as it couldnt translate it to sql. I read somewhere that XML fields are actually regarded as strings, and only on the app server they become XElements, so this way the filtering would be on the app server anyways, not the DB. Fair point, it wont work like this. Lets try something else... SO the second idea was to create a PolyGlots table (name taken from http://weblogic.sys-con.com/node/102698?page=0,1), a PolyGlotTranslations table and a Culture table, where the PolyGlots would be referenced from each internationalized property. This way i wanted to say for example:

private static readonly CompiledExpression<Announcement, string> nameExpression
    = DefaultTranslationOf<Announcement>
        .Property(e => e.Name)
        .Is(e=> e.NamePolyGlot.PolyGlotTranslations
          .Where(t=> t.Culture.Code == Thread.CurrentThread.CurrentUICulture.Name)
          .Single().Value
        );

now unfortunately here i get an error that the Where() function cannot be translated to sql, what is a bit disappointing, as i was sure it will go through. I guess it is failing, cause the IEntitySet is basically an IEnumerable, not IQueryable, am i right?

Is there another way to use the compiledExpressions class to achieve this goal? Any help appreciated.

Answer 1

First, I would not try to wrestle the XML extensions into SQL. They were designed for different beasts for a reason.

With the right database strategy, you should be able to get away with a single set of linq queries. (You may need to adjust them to fit the new model.) For instance if you have a description field that is different depending on the language, than create a view that will return the correct description based on the language of choice. Now point your linq query to the view instead of the tables.

Additional Information: You only have one view per item that will contain multilingual fields. Each multilingual field has its own table using a language table key and the original object's key as a compound key. The view combines all the tables back into a single place to query. Now say we have a product view. If I query that view by ProductId = 1 I will actually see as many copies of the product as there are languages.

Now your Linq queries will always contain a parameter to limit by language. Just be sure to use an outer join in your query so that if the description in a language is missing you will still get it back. (Or not use it to intentionally limit the items to those setup for the language of choice.

You can create stored procs to open up the ability to update the view making it act like a table. (Or you can do this with most ARM frameworks by wiring up the stored procs there.)