Localizing Linq to SQL Entities

Back from the holidays! Not getting too much sun certainly encourages to write code rather than chilling out. Writing on this subject was on my list as Linq to SQL got more mature, need for it in multi-cultural applications has arisen respectively. Also an old post of Ayende beat me to think about how a similar problem could be solved in Linq to SQL.

I’ll use the same model that he provided, and it is the following:

tmpD45

Figure 1. Table structure for multi-lingual products

 

As in the original post, the challenge is just to load the Product Names for the current culture(or specific culture), not all of them related to one product. So in nhibernate, there are filters to solve this problem in an elegant way. It is elegant because it is externally configurable and includes no intrusiveness in your design.

When internationalising-localizing comes into play, there are 2 main approaches from a Domain Perspective and it lies behind the answer of the question :

“Is localization a concern of my domain?”

In fairness, the answer changes for every domain (to my experience in most cases it is either no, or part of a different domain, such as administration). A simple way of determining if this is an issue is, to check that if domain needs to know about different cultures or domain elements with different languages need to talk to each other or not (Can Reuters publish news in Portugese ?). If the answer is yes, then even eager loading all language translations can be an option. But otherwise, we’ll need to abstract away so that domain won’t know about this infrastructurel concern.

In the original post, Ayende uses filters in NHibernate. In Linq to SQL we don’t have filters but as mentioned before, we have Load Options to give a criteria and reduce the amount of data we retrieve.

As a matter of fact, we expect following test to pass. Note that this is a state based test to test the data retrieved is not more than one.

   1: /// <summary>
   2: ///A test for GetProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void GetProductTest()
   6: {
   7:   ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:   int prodId = 1; // TODO: Initialize to an appropriate value
   9:   int lcId = 3; // TODO: Initialize to an appropriate value
  10:   Product actual = target.GetProduct(prodId, lcId);
  11:   Assert.AreEqual(“Prod13″, actual.Name);
  12:   Assert.IsTrue(actual.ProductNames.Count == 1);
  13: }

Where the records in the table are as follows:

image

Figure 2. Records in Product Names Table. As seen, there are 2 records for product id ‘1′

The entity structure that we have to use with Linq to SQL (generated by the courtesy of the designer) is as follows:

image

Figure 3. Object Model of Product and Product Name

Looks innocent doesn’t it ? The secret thing is that Product will always have a list of ProductNames, which in my case will always have 1 element. If I want to keep my domain ignorant of this, this certainly is a bad thing but this is what L2S gives me by default. There are ways to overcome this issue of course, but those are not the point of the post.

In addition to the model, I’ll add another field called “Name” to the model that’s not mapped to any column in db, to reach the same example. This is achieved by a partial class:

   1: partial class Product
   2: {
   3:     public string Name
   4:     {
   5:         get;
   6:         set;
   7:     }
   8: }

Now we are ready to write the code that passes the test. Note that we are utilizing AssociateWith Generic Method to make the necessary filtering.

   1: /// <summary>
   2: /// Gets the product for the current culture.
   3: /// </summary>
   4: /// <param name=”prodId”>The prod id.</param>
   5: /// <param name=”lcId”>The lc id to do localization filter.</param>
   6: /// <returns></returns>
   7: public Product GetProduct(int prodId, int? lcId)
   8: {
   9:     using (ProductsDataContext context = new ProductsDataContext())
  10:     {
  11:         // set load options if localizable filter needed
  12:         if (lcId.HasValue)
  13:         {
  14:             DataLoadOptions options = new DataLoadOptions();
  15:             options.AssociateWith<Product>(p => p.ProductNames.Where<ProductName>(pn => pn.CultureId == lcId));
  16:             context.LoadOptions = options;
  17:         }
  18:
  19:         Product pFromDb = context.Products.Single<Product>(p => p.ProductId == prodId);
  20:
  21:         return new Product()
  22:         {
  23:                  Amount = pFromDb.Amount,
  24:                  ProductId = pFromDb.ProductId,
  25:                  Size = pFromDb.Size,
  26:                  Name = pFromDb.ProductNames.First<ProductName>().Name,
  27:                  ProductNames = pFromDb.ProductNames
  28:         };
  29:      }
  30: }

Now since we are done with the original post, let’s go beyond the bar and implement inserts & updates too. With Inserts, there are 2 things that I am going to handle: 1 - It is a brand new insert 2 - It is just an insert of a new product name in another language.

For first one here is the test :

   1: /// <summary>
   2: ///A test for InsertProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void Should_Insert_for_Completely_New_Prod()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = new Product()
   9:     {
  10:          Amount = 31,
  11:          Name = “English Name”,
  12:              ProductId = 0,
  13:              Size = 36,
  14:     };
  15:     int lcId = 7;
  16:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  17:     {
  18:         target.InsertProduct(p, lcId);
  19:         Assert.IsTrue(p.ProductId > 0);
  20:         Assert.IsTrue(p.ProductNames.Count > 0);
  21:      }
  22: }

And for the second one:

   1: /// <summary>
   2: ///A test for InsertProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void Should_Insert_Name_for_Existing_Prod()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = target.GetProduct(1);
   9:     int firstCount = p.ProductNames.Count;
  10:     p.Name = “Kurdish Name”;
  11:     int lcId = 9;
  12:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  13:     {
  14:         target.InsertProduct(p, lcId);
  15:         Product prAfterInsert = target.GetProduct(p.ProductId);
  16:         Assert.AreEqual(firstCount + 1, prAfterInsert.ProductNames.Count);
  17:     }
  18: }

So, passing test is obvious. I need to do an extra insert to the product tables if it is a new one, and that’s it:

   1: /// <summary>
   2: /// Inserts the product.
   3: /// </summary>
   4: /// <param name=”p”>The p.</param>
   5: /// <param name=”lcId”>The lc id.</param>
   6: public void InsertProduct(Product p, int lcId)
   7: {
   8:     using (ProductsDataContext context = new ProductsDataContext())
   9:     {
  10:         if (p.ProductId == 0)
  11:         {
  12:             // insert only if it is new
  13:             context.Products.InsertOnSubmit(p);
  14:         }
  15:
  16:         InsertProductNameForProduct(context, p, lcId);
  17:         context.SubmitChanges();
  18:     }
  19: }
  20:
  21: /// <summary>
  22: /// Inserts the product name for product.
  23: /// </summary>
  24: /// <param name=”context”>The context.</param>
  25: /// <param name=”p”>The p.</param>
  26: /// <param name=”lcId”>The lc id.</param>
  27: private void InsertProductNameForProduct(ProductsDataContext context, Product p, int lcId)
  28: {
  29:     context.ProductNames.InsertOnSubmit(new ProductName()
  30:     {
  31:         CultureId = lcId,
  32:         Name = p.Name,
  33:         ProductId = p.ProductId,
  34:         Product = p,
  35:      });
  36: }

And last, for update; apart from the obvious part there is one situation we need to handle : if the name of the product is changed, than we need to update it as well. For the other fields, go on with the regular update. Here is the test that codifies the statement:

   1: /// <summary>
   2: ///A test for UpdateProduct
   3: ///</summary>
   4: [TestMethod()]
   5: public void should_update_product_and_its_current_name()
   6: {
   7:     ProductsRepository target = new ProductsRepository(); // TODO: Initialize to an appropriate value
   8:     Product p = target.GetProduct(1, 2);
   9:     p.Name = “French Name”;
  10:     p.Amount = 40;
  11:     p.Size = 55;
  12:     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
  13:     {
  14:         target.UpdateProduct(p);
  15:         Assert.AreEqual(“French Name”, p.Name);
  16:         Assert.AreEqual(40, p.Amount);
  17:         Assert.AreEqual(55, p.Size);
  18:      }
  19: }

After writing the test, the implementation below becomes obvious:

   1: public void UpdateProduct(Product p)
   2: {
   3:    // since we don’t load more than one product name, we can assume that the one is updated
   4:    using (ProductsDataContext context = new ProductsDataContext())
   5:    {
   6:        context.Products.Attach(p, true);
   7:        ProductName currentName = p.ProductNames.Single<ProductName>();
   8:        if (p.Name != currentName.Name)
   9:        {
  10:            // it is updated, update it
  11:            currentName.Name = p.Name;
  12:         }
  13:         context.SubmitChanges();
  14:     }
  15: }

I showed a possible strategy to localize Linq to SQL entities in this post. Of course, more complex scenarios such as child entities and lazy loading issues could be thought thoroughly, but I hope this gave some initiative to attack the whole idea.

Comments and critics well appreciated as always.

kick it on DotNetKicks.com

Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone

3 Responses so far »

  1. 1

    asp.net said,

    August 18, 2008 @ 8:39 pm

    good review,thx

  2. 2

    motoponk said,

    October 1, 2008 @ 11:55 pm

    Hocam guncel linkler var, yer yerinde oynuyor hic bahsedilmemis. Bakin ben bilem nasil takip ediyorum sinsi sinsi :P

    http://www.computerworld.com.au/index.php/id;1149786074;pp;1

    http://msdn.microsoft.com/en-us/vs2008/products/cc948977.aspx

    http://msdn.microsoft.com/en-us/vsts2008/products/bb725993.aspx

    http://channel9.msdn.com/VisualStudio/

  3. 3

    Sidar Ok said,

    October 2, 2008 @ 11:04 am

    haha :) man I am not an MS evangelist, I am a customer and I only care about how the solution is good in the real world ! So after first CTP, we’ll see the craic :)

Comment RSS · TrackBack URI

Say your words

You must be logged in to post a comment.