EntityFramework query manipulation, db provider wrapping, db expression trees

Question

I'm trying to implement data localization logic for Entity Framework. So that if for example a query selects Title property, behind the scenes it should reference the column Title_enGB or Title_deCH depending on the current user culture .

To achieve this, I'd like to rewrite the DbExpression CommandTrees from Entity Framework. I thought these trees are a new common .NET way for building cross database insert/update/select queries.. But now all relevant constructors/factories in the namespaces System.Data.Metadata and System.Data.Common.CommandTrees in System.Data.Entity.dll are internal!! (In msdn documentated as public, like: DbExpressionBuilder).

Does anyone have an idea to achieve this query manipulation with or without query tree rewrite?

my desired code: (public class DbProviderServicesWrapper : DbProviderServices)

/// <summary>
/// Creates a command definition object for the specified provider manifest and command tree.
/// </summary>
/// <param name="providerManifest">Provider manifest previously retrieved from the store provider.</param>
/// <param name="commandTree">Command tree for the statement.</param>
/// <returns>
/// An exectable command definition object.
/// </returns>
protected override DbCommandDefinition CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
{
    var originalCommandTree = commandTree as DbQueryCommandTree;
    if (originalCommandTree != null)
    {
        var expression = new MyCustomQueryRewriter(originalTree.MetadataWorkspace).Visit(originalCommandTree.Query);
        commandTree = DbQueryCommandTree.FromValidExpression(originalCommandTree.MetadataWorkspace, originalCommandTree.DataSpace, expression);
    }

    // TODO: UpdateCommand/InsertCommand

    var inner = this.Inner.CreateCommandDefinition(providerManifest, commandTree);
    var def = new DbCommandDefinitionWrapper(inner, (c, cd) => new DbCommandWrapper(c));

    return def;
}

Update

Having two title columns on one table isn't cool but its easier to implement in a first step. Later I'll join an other table with the localized fields, so the main table will only contain invariant data.

Answer 1

In .net you have resx files for handling localization. See: What are the benefits of resource(.resx) files?

There are a couple of problems with your approach:

• Adding an extra language requires a database change
• There is more data traffic from the database than is required

I know that this is not a direct answer to your question but I think you should look at resx files.

If you must store it in the database you could redesign the database:

• Table 1: id, Text
• Table 2: id, Table1_id, language_code, text

This way a new language does not require a database change, and the EF code becomes much simpler.

Answer 2

I agree with the answer of Shiraz that this shouldn't be what you want if you are still capable of changing the design, but I'll be assuming that this is an existing application that you are converting to Entity Framework.

If so, it matters if the Title_enGB/etc columns are mapped in the EDMX file / POCOs. If they are, I suppose this is possible. What you could do here, is use an Expression visitor that visits MemberExpressions, checks if they access a property named "Title" (you could create a whitelist of properties that needed to be treated like this) and then return a new MemberExpression that insteads accesses Title_enGB if the logged in user has that language set.

A quick example:

public class MemberVisitor : ExpressionVisitor
{
  protected override Expression VisitMember(MemberExpression node)
  {
    if(node.Member.Name == "Title")
    {
        return Expression.Property(node.Expression, "Title_" + User.LanguageCode)
    }

    return base.VisitMember(node);
  }
}

And then before you execute the query:

var visitor = new MemberVisitor();
visitor.Visit(query);

Again, this is only a good idea if you don't have any control over the database any more.

This solution may or may not be practical to you, depending on your exact situation, but rewriting queries using Expressions is definitely possible.

It's a much higher level solution than modifying how Entity Framework generates the actual SQL queries. That's indeed hidden from you, probably with good reason. Instead, you just modify the expression tree that describes the query and let Entity Framework worry about converting it to SQL.

Answer 3

Instead I will propose one more design...

Products
   ProductID 
   ProductName
   Price
   Description
   ParentID (Nullable, FK on ProductID)
   LangCode

Now in this case you have,

1, Milk, $1 , EnglishDesc  , NULL, en-us 
2. M*^*, ^*&, OtherLangDesc, 1   , @$#$$

Your record 2 is actually another language description of entire product in different language identified by LanguageCode.

This way you can only manage one Table and writing some Generics based or Reflection based Querying solution will be lot easier.

// Get Active Products
q = context.Products.Where( x=> x.ParentID == null);

// Get Product's Language Code Description
IQueryable<Product> GetProductDesc(int productID, string langCode){
    return context.Products.Where( x=>x.ParentID == productID &&
              x.LangCode == langCode);
}

You can create an interface as follow,

interface IMultiLangObject{
    int? ParentID {get;set;}
    string LangCode {get;set;}
}

And you can write a generic solution based on this.