Saturday, March 24, 2012

How to Implement Language Translation in WCF Data Service (OData) Part-1


Language translation in WCF Data Service  can be handled in two ways. First by using Entity Level Join in QueryView and second is by using ObjectMaterialized event provided by DataService class.

This article covers first part (QueryView ) and works well for one table (It can works for multiple translation table if translation table has all the required translation entries) 
But I would suggest to use this approach only when if there is only one translation table. For multiple translation table please refer to Part2 of this article.

Alternative Title 
Implementing Language Sensitive Response in WCF Data Service .
Multilingual Support in WCF Data Service
Culture Negotiation  in WCF Data service
Problem 
You are developing a data service for international customer and need to provide language specific translation for some fields (properties/columns)

Use Case
you have a master product table that contains a ProductName column. Also there is a Translation table that provides product names in different languages. Now we need to build a web service that provides Product Name and It`s translated name side by side as a single entity based on language code supplied by user or system.

Discussion  
There are two fundamentals approach for displaying multilingual data in a web service.
Super Imposition : In this approach either standard (say en-us) or  translated (say IT-IT) content is delivered.This technique create smaller pay load than next one discussed. Also in this approach schema of the  Entity that represent database table is unchanged.
Parallel Imposition or Side By Side approach: In this approach we add on extra property for each property that require translation. The extra property provides translated value. 

<m:properties>
  <d:ProductID m:type="Edm.Int32">1</d:ProductID>
  <d:ProductName>Chai</d:ProductName>
  <d:ProductTranslatedName>IT_Chai</d:ProductTranslatedName>
   <d:SupplierID m:type="Edm.Int32">1</d:SupplierID>
....
</m:properties>

Solution 
High level solution is listed below
  1. Expose your Data Table(Ex Products)  and Translation Table (Ex NameTranslation) as entity in your EDMX
  2. Add two scalar property (Translated Name and Language) in your Entity (Product) generated by EDMX
  3. Create a Left outer join of  Data Entity (Product) and Translation Entity (ThanslatedName) based on primary key of Product Table using Query View of Mapping section in EDMX
Handling Language Parameter 
Note :- This Article do not cover "Language Parameter Handling " and code sample uses hard coded language parameter to simplify demonstration
There could me multiple ways to receive language parameter in the WCF data processing pipeline.
Attached Solution 
  • Uses Northwind database and exposed tow tabled Products and Name Translation as entity in edmx.
  • Two additional properties are added in entity conceptual model as below
<Property Type="String" Name="ProductTranslatedName" Nullable="false" FixedLength="false" MaxLength="100" />
<Property Type="String" Name="Language" Nullable="false" />
  • I had created a table called NameTranslation  that simulate translations just two products and two languages  EN and IT and exposed as entity.
  • Mapping of Product entity is modified to implement Left outer join with Translation table. This technique discard existing scalar mapping and uses query based mapping abs below.
  •   <EntitySetMapping Name="Products">
                <QueryView>
                  select value
                  NORTHWNDModel.Product
                  (p.ProductID,
                  p.ProductName,
                 
                  case when t.ProductNativeName is null then 'Translation Not Found'
                  else t.ProductNativeName
                  end,
                
                  p.SupplierID,
                  p.CategoryID,
                  p.QuantityPerUnit,
                  p.UnitPrice,
                  p.UnitsInStock,
                  p.UnitsOnOrder,
                  p.ReorderLevel,
                  p.Discontinued,

                  case when t.Language is null then 'N/A'
                  else t.Language
                  end )
                  from NORTHWNDModelStoreContainer.Products   as p
                  left join NORTHWNDModelStoreContainer.NameTranslation as t
                  on p.ProductID=t.ProductID
                </QueryView>
    <!--OLD Mapping is discarded here -->
                <!--<EntityTypeMapping TypeName="NORTHWNDModel.Product">
                  <MappingFragment StoreEntitySet="Products">
                    <ScalarProperty Name="ProductID" ColumnName="ProductID" />
                    <ScalarProperty Name="ProductName" ColumnName="ProductName" />
                    <ScalarProperty Name="SupplierID" ColumnName="SupplierID" />
                    <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
                    <ScalarProperty Name="QuantityPerUnit" ColumnName="QuantityPerUnit" />
                    <ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
                    <ScalarProperty Name="UnitsInStock" ColumnName="UnitsInStock" />
                    <ScalarProperty Name="UnitsOnOrder" ColumnName="UnitsOnOrder" />
                    <ScalarProperty Name="ReorderLevel" ColumnName="ReorderLevel" />
                    <ScalarProperty Name="Discontinued" ColumnName="Discontinued" />
                  </MappingFragment>
                </EntityTypeMapping>-->
              </EntitySetMapping>

     
  •   This completes edmx manipulation and now we need to write a Query Interceptor in Data Service class that can intercept the request and filter the response based on Language requested.
    [QueryInterceptor("Products")]
            public Expression<Func<Product, bool>> OnQueryOrders()
            {
                //This code uses hardecoded language parameter but if you want to pass it
                // dynamicly  use query paramteters
                string language = "IT-IT";
                string  P = this.CurrentDataSource.Products.ToTraceString();
                return o => (o.Language == language || o.Language == "N/A");
            }
  • This completes the code base and service is ready to test. Try navigating the entity set (http://localhost:11002/WcfDataService1.svc/Products) belwo is the output.
  •  <m:properties>
      <d:ProductID m:type="Edm.Int32">2</d:ProductID>
      <d:ProductName>Chang</d:ProductName>
      <d:ProductTranslatedName xml:space="preserve">IT_Chang</d:ProductTranslatedName>
      <d:SupplierID m:type="Edm.Int32">1</d:SupplierID>
      <d:CategoryID m:type="Edm.Int32">1</d:CategoryID>
      <d:QuantityPerUnit>24 - 12 oz bottles</d:QuantityPerUnit>
      <d:UnitPrice m:type="Edm.Decimal">19.0000</d:UnitPrice>
      <d:UnitsInStock m:type="Edm.Int16">17</d:UnitsInStock>
      <d:UnitsOnOrder m:type="Edm.Int16">40</d:UnitsOnOrder>
      <d:ReorderLevel m:type="Edm.Int16">25</d:ReorderLevel>
      <d:Discontinued m:type="Edm.Boolean">false</d:Discontinued>
      <d:Language>IT-IT</d:Language>
    </m:properties>

     
Further References 

1 comment: