EF and Culture

Imagine that you have a localizable DB like this:

Products

  • ProductID (PK)
  • Name

ProductDescriptions

  • ProductID (PK, FK to Products)
  • Culture (PK)
  • Text

Then we want only one public entity type:

Product

  • ProductID (Key)
  • Name
  • Description

How to do this?

First solution:

Set ProductDescription class internal

Set ProductDescription EntitySet private

Set Product EntitySet private

Set ProductDescriptions navigation property (of Product) private

Extend (with partial) the Product class:

partial class Product
{
    public string Description
    {
        get
        {
            var productDescription = GetProductDescription();
            return productDescription == null ? null : productDescription.Text;
        }
        set
        {
            var productDescription = GetProductDescription();
            if (productDescription == null)
                Descriptions.Add(new ProductDescription { Culture = Thread.CurrentThread.CurrentUICulture.Name, Text = value });
            else
                productDescription.Text = value;
        }
    }
 
    private ProductDescription GetProductDescription()
    {
        if (!Descriptions.IsLoaded)
            Descriptions.Load();
        return Descriptions.FirstOrDefault(pd => pd.Culture == Thread.CurrentThread.CurrentUICulture.Name);
    }
}

Great, it works but

·         For each product, we load all the descriptions instead of getting only the one for the current culture.

·         We have two sql queries to get one complete instance of Product.

 

To solve this last point, we can:

Extend (with partial) the ObjectContext class and add a Products property:

partial class THBEntities
{
    public ObjectQuery<Product> Products
    {
        get { return ProductSet.Include("Descriptions"); }
    }
} 

 Then, the product description property can now remove the Load:

private ProductDescription GetProductDescription()
{
    return Descriptions.FirstOrDefault(pd => pd.Culture == Thread.CurrentThread.CurrentUICulture.Name);
}

We have always the current culture problem. In fact, in this case, it’s a shame not to have the LINQ To SQL AssociateWith method.

To load only the current culture, we can imagine a second solution:

My idea here is to do EntitySplitting. But we can do Entity Splitting only if the tables have the same PK.

So the first step is to change the SSDL:

·         Remove Culture from the entity type key

·         Set Culture as Nullable

·         Change the multiplicity of the association between Products and ProductDescriptions

Then, we make the entity type with four properties:

·         ProductID (Key)

·         Name

·         Description

 

Here, we have a problem. We have to add condition on ProductDescriptions table mapping while the program is running to determine the current culture.

But how to do this?

My idea is to change the CSDL and the MSL only when the program is running. To do this, we change the model metadata artifact processing from “Embed in Output Assembly” to “Copy to Output Directory”.

Then, we extend (partial) the ObjectContext class and we change the msl file in the static constructor:

partial class THBEntities
{
    private const string MSL_NS = "urn:schemas-microsoft-com:windows:storage:mapping:CS";
 
    static THBEntities()
    {
        var culture = Thread.CurrentThread.CurrentUICulture.Name;
        var connectionString = ConfigurationManager.ConnectionStrings["THBEntities"].ToString();
        var edmConnectionString = connectionString.Remove(connectionString.IndexOf(";")).Substring(connectionString.IndexOf("=") + 1);
        var edmSplitConnectionString = edmConnectionString.Split(‘|’);
        var mslPath = edmSplitConnectionString.First(elt => elt.EndsWith("THB.msl"));
        var msl = XElement.Load(mslPath);
        var productMSL = msl.Descendants(XName.Get("MappingFragment", MSL_NS)).First(mf => mf.Attribute("StoreEntitySet").Value == "ProductDescriptions");
        if (!productMSL.Elements(XName.Get("Condition", MSL_NS)).Any(sp => sp.Attribute("ColumnName") != null && sp.Attribute("ColumnName").Value == "Culture"))
        {
            productMSL.Add(new XElement(XName.Get("Condition", MSL_NS),
                               new XAttribute("ColumnName", "Culture"),
                               new XAttribute("Value", culture)));
            msl.Save(mslPath);
        }
    }
}

With this solution, we have two problems:

·         We changed the SSDL and we simulate a 1 to 0..1 relationship between Products and ProductDescriptions and so, when  we update the Description, the UPDATE query won’t integrate the WHERE Culture = ‘xx-xx’ and so, we will update the description for all cultures on the SaveChanges.

·         Moreover, using Entity Splitting means that we can’t get an existing product if we haven’t got the description for the current culture

We also have a very interesting thing: if we delete one product, we will also delete all its descriptions in DB (the DELETE SQL query WHERE is only on ProductID (because we change the SSDL)).

 

We can fix these two problems using

·         SSDL View instead of ProductDescriptions table directly to get the product with no description in the current culture

·         SSDL Functions to fix the Update problem

To fix the UPDATE problem, we can also have another approach with Entity Splitting:

We can remove the mapping condition and use directly the property Culture. So, we have four properties in Product Entity Type:

·         ProductID (Key)

·         Name

·         Culture (private)

·         Description

Culture property is:

[EdmScalarPropertyAttribute(IsNullable = false)]
private string Culture
{
    get { return Thread.CurrentThread.CurrentUICulture.Name; }
    set { }
}

But we don’t want to change the edmx .Designer file. So one possibility is to do without, with our own code (with Presciptive classes or IPOCO or POCO with EF v2). Yes but the problem with this approach is the fact that if we want to change our model, we also have to change our code ourselves. My fun idea is to exclude the Culture property from the code generator.

Then, we will add it to csdl and msl files by using the same idea that previously:

partial class THBEntities
{
    private const string CSDL_NS = "http://schemas.microsoft.com/ado/2006/04/edm";
    private const string MSL_NS = "urn:schemas-microsoft-com:windows:storage:mapping:CS";
 
    static THBEntities()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["THBEntities"].ToString();
        var edmConnectionString = connectionString.Remove(connectionString.IndexOf(";")).Substring(connectionString.IndexOf("=") + 1);
        var edmSplitConnectionString = edmConnectionString.Split(‘|’);
        var csdlPath = edmSplitConnectionString.First(elt => elt.EndsWith("THB.csdl"));
        var csdl = XElement.Load(csdlPath);
        var productCSDL = csdl.Descendants(XName.Get("EntityType", CSDL_NS)).First(et => et.Attribute("Name").Value == "Product");
        if (!productCSDL.Elements(XName.Get("Property", CSDL_NS)).Any(p => p.Attribute("Name").Value == "Culture"))
        {
            productCSDL.Add(new XElement(XName.Get("Property", CSDL_NS),
                                new XAttribute("Name", "Culture"),
                                new XAttribute("Type", "String"),
                                new XAttribute("Nullable", false)));
            csdl.Save(csdlPath);
        }
        var mslPath = edmSplitConnectionString.First(elt => elt.EndsWith("THB.msl"));
        var msl = XElement.Load(mslPath);
        var productMSL = msl.Descendants(XName.Get("MappingFragment", MSL_NS)).First(mf => mf.Attribute("StoreEntitySet").Value == "ProductDescriptions");
        if (!productMSL.Elements(XName.Get("ScalarProperty", MSL_NS)).Any(sp => sp.Attribute("Name").Value == "Culture"))
        {
            productMSL.Add(new XElement(XName.Get("ScalarProperty", MSL_NS),
                               new XAttribute("Name", "Culture"),
                               new XAttribute("ColumnName", "Culture")));
            msl.Save(mslPath);
        }
    }

}

Now, we have to use a SSDL function to get the products (to set a WHERE on the Culture).

So in the SSDL part of our edmx, we will add this:

<Function Name="GetProducts" IsComposable="false">
  <CommandText>
   SELECT P.ProductID, P.ProductName, PD.Culture, PD.[Text] AS [Description]
    FROM Products AS P
    LEFT OUTER JOIN ProductDescriptions AS PD ON P.ProductID = PD.ProductID AND PD.Culture = ‘en-US’  </CommandText>
  <Parameter Name="Culture" Type="nvarchar" Mode="In" />
</Function> 

Then we make a “function import” to import it in the CSDL.

Then set this CSDL function to private and the Product EntitySet also.

Then in our ObjectContext file, we add a public Products property:

public ObjectResult<Product> Products
{
    get
    {
        return GetProducts(Thread.CurrentThread.CurrentUICulture.Name);
    }
}

Now how to solve our Update problem?

What we want here is to include the where condition on the update. Yes but the ConcurrencyMode.Fixed does exactly this. So we can add the ConcurrencyMode attribute in our ObjectContext static constructor.

Ok great! Now we can do CUD operation but there are three problems with this approach:

·         For one product P1, if the culture doesn’t exist in ProductDescriptions table, the P1 update will throw an exception

·         We have a problem with the DELETE: if we delete one product, we have to delete all the associated ProductDescription and not only the current culture one.

·         Products property is an IEnumerable<T>, not an IQueryable<T>. That means that if we want to add some condition on the products, we will load all the products for the current culture and we will filter our objects (with LINQ To Objects for example).

To solve the first two points, we can use a SSDL Functions for the Update (which will test if the ProductDescriptions’ row exists in the DB for the current culture to determine if you have to do an UPDATE or an INSERT) and the DELETE. But with EF V1, that means that we also have to use SSDL Functions for INSERT. For the DELETE, we can also use a CASCADE Delete in DB.

Ok, it’s interesting but we still have our IEnumerable problem.

Another idea can be to use reflection to get the product ObjectContext to get only the ProductDescription:

If we come back to the first solution, one of the problems was the fact that you load all the description instead of loading only the current culture one.

We can imagine doing a query on ProductDescription EntitySet but the problem is that we need the current ObjectContext from an entity (Product in our case).

First, we will change ProductDescription EntitySet from private to internal.

Then, we need the ObjectContext but to do this, we have to use reflection:

private ProductDescription GetProductDescription()
{
    var culture = Thread.CurrentThread.CurrentUICulture.Name;
    var value = Descriptions.FirstOrDefault(pd => pd.Culture == Thread.CurrentThread.CurrentUICulture.Name);
    if (value == null)
    {
        var context = (THBEntities)Descriptions.GetType().GetProperty("ObjectContext", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(Descriptions, null);
        value = context.ProductDescriptionSet.FirstOrDefault(pd => pd.ProductID == ProductID && pd.Culture == Thread.CurrentThread.CurrentUICulture.Name);
    }
    return value;
}              

In this case, we solve the problem of loading all the descriptions by product but not the fact to have two sql queries to get a complete product.

Another idea is this:

We will come back to the second solution (with Include) but instead of doing the Include, we do:

public IEnumerable<Product> Products
{
    get { return ProductSet.Select(p => new { Product = p, ProductDescription = p.Descriptions.FirstOrDefault(pd => pd.Culture == Thread.CurrentThread.CurrentUICulture.Name) }).AsEnumerable().Select(p => p.Product); }
}

With this solution we have the same problem as one of a previous solution: if we want to add some condition on the products, these conditions won’t be included in the sql query because Products property returns an IEnumerable<Product> instead of an IQueryable<Product>.

So, all the previous versions have some good and bad points.

I think the best is to use SSDLFunctions to do UPDATE and DELETE and so, with v1, INSERT.

A last funny solution with SSDLView:

We come back to the second solution (with Include).

But instead of mapping ProductDescription entity type on ProductDescriptions table, we map it on a SSDL View which duplicates the Culture column:

SELECT ProductID, Culture, Text, Culture AS ConditionCulture FROM ProductDescriptions 

Why do we do this? Like this, we will be able to keep our two properties key and also to add column condition and as the keys properties should not change, we keep the insurance of the coherence of our ProductDescription entities.

Then, we will add during the runtime the column condition:

partial class THBEntities
{
    private const string MSL_NS = "urn:schemas-microsoft-com:windows:storage:mapping:CS";
 
    static THBEntities()
    {
        var culture = Thread.CurrentThread.CurrentUICulture.Name;
        var connectionString = ConfigurationManager.ConnectionStrings["THBEntities"].ToString();
        var edmConnectionString = connectionString.Remove(connectionString.IndexOf(";")).Substring(connectionString.IndexOf("=") + 1);
        var edmSplitConnectionString = edmConnectionString.Split(‘|’);
        var mslPath = edmSplitConnectionString.First(elt => elt.EndsWith("THB.msl"));
        var msl = XElement.Load(mslPath);
        var productMSL = msl.Descendants(XName.Get("MappingFragment", MSL_NS)).First(mf => mf.Attribute("StoreEntitySet").Value == "ProductDescriptions");
        if (!productMSL.Elements(XName.Get("Condition", MSL_NS)).Any(sp => sp.Attribute("ColumnName") != null && sp.Attribute("ColumnName").Value == "ConditionCulture"))
        {
            productMSL.Add(new XElement(XName.Get("Condition", MSL_NS),
                               new XAttribute("ColumnName", "ConditionCulture"),
                               new XAttribute("Value", culture)));
            msl.Save(mslPath);
        }
    }
 
    public ObjectQuery<Product> Products
    {
        get { return ProductSet.Include("Descriptions"); }
    }
} 

Now, as we use a SSDL Function, we have to define SSDL Functions to do CUD operations.

Ok great, but we also have a problem: if we delete a Product, we also have to delete all its descriptions. To do this, we can, of course use SSDL Functions, but we can also imagine having a cascade delete in our DB and in our EDM.

In the EDM, we can activate delete cascade mode like this:

<Association Name="FK_ProductDescriptions_Products">
  <End Role="Products" Type="THBModel.Product" Multiplicity="1">
    <OnDelete Action="Cascade" />
  </End>
  <End Role="ProductDescriptions" Type="THBModel.ProductDescription" Multiplicity="*" />
  <ReferentialConstraint>
    <Principal Role="Products">
      <PropertyRef Name="ProductID" />
    </Principal>
    <Dependent Role="ProductDescriptions">
      <PropertyRef Name="ProductID" />
    </Dependent>
  </ReferentialConstraint>
</Association>

Note that these solutions are just some demos. For example, if we have two different cultures, my MSL is only made for the first one. However, I found interesting to show you that is possible to change the CSDL/MSL on runtime.

This entry was posted in 7674, 7675. Bookmark the permalink.

2 Responses to EF and Culture

  1. Faisal says:

    Seems good.. but is there any alternative solution….. ????

  2. Matthieu MEZIL says:

    I think the only one alternative is to keep the one to many association. (which is the default way after making the EDM from the DB)

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>