Episerver Commerce MetaDictionary internals

This is an excerpt from my book – Pro Episerver Commerce – which is now already 2/3 complete.

Dictionary types.

Previously we discussed on how properties work with catalog content. However – if you have dictionary types in your MetaClasses, they will work differently. In this section we will examine these special data types – this applies to Order system metaclasses as well.

As we all know – there are three types of dictionary in Episerver Commerce:

• Single value dictionary: editor can select a value from defined ones.

Single value dictionary type is supported in the strongly typed content types – you’ll need to define a property of type string, with backing type of typeof(PropertyDictionarySingle)

 [BackingType(typeof(PropertyDictionarySingle))]
 public virtual string Color { get; set; }

• Multi value Dictionary: editor can select multiple values from defined ones. The only different from Single value dictionary is it has the “Multiline” option enabled.

You can define a property for Multi value dictionary in content type by IEnumeable<string> andtypeof(PropertyDictionaryMultiple) backing type

[BackingType(typeof(PropertyDictionaryMultiple))]
public virtual IEnumerable<string> Colors { get; set; }

Both single and multi value dictionary types are fully supported in Catalog UI, including editing:

and administration:

• String dictionary: this is the true “dictionary type”: you can define pairs of key and value (the previous types are actually “list”).

String dictionary is not supported by the strongly typed content types, nor Catalog UI. To manage this metafield, we’ll have to use Commerce Manager, or use MetaDataPlus API:s

In later chapters, we will see how to add the support for it in Catalog UI.

How dictionary works

We’ve learned in previous chapters how properties are stored and loaded. Technically, dictionaries are “just another properties”. However they are stored differently.

If you look at CatalogContentProperty table (or ecfVersionProperty, for draft versions), you’ll see the dictionary properties are stored as numbers. What do those numbers mean?

• For single value dictionary type, that number is the MetaDictionaryId of the selected value in MetaDictionary table.
• For multi value dictionary type, things are a bit more complicated. That number is the MetaKey value in MetaKey table. This MetaKey, is, however, connected to MetaMultiValueDictionary, which itself points back to MetaDictionary. An “usual” design for 1-n relation in database, right?
• For string dictionary type, it’s more or less the same as multi value dictionary. However, the MetaKey will point to pairs of key and value in MetaStringDictionaryValue table.

Those information might not be really interesting to you – but they can be useful in some specific scenario. Let’s consider some of those cases:

• You want to know which entries use a specific dictionary value. In previous example, we have a property named Color, we want to find all entries with Color is ‘Blue’. For front-end site, it would be easy to find such entries by search feature (will be discussed later), but what if you want to create a report for that? Episerver Commerce does not provide such functionality out of the box, so we’ll have to craft it ourselves. Time for some SQL then!
  

  DECLARE @MetaFieldId INT
  DECLARE @MetaDictionaryId INT
  
  SET @MetaFieldId = (SELECT MetaFieldId FROM MetaField WHERE Name = 'Color' AND
  Namespace = 'Mediachase.Commerce.Catalog')
  SET @MetaDictionaryId = (SELECT MetaDictionaryId FROM MetaDictionary WHERE Value = 'Blue' AND MetaFieldId = @MetaFieldId)
   
  SELECT ObjectId FROM CatalogContentProperty WHERE MetaFieldId = @MetaFieldId AND Number = @MetaDictionaryId AND ObjectTypeId = 0
  

This script is quite simple – we need to get the Id of Color MetaField first, then the MetaDictionaryId of the ‘Blue’ color. When we have two values, we can simple query from table CatalogContentProperty to find which entries have that value. You can go even further to join with CatalogEntry table to get more information such as name or code – I’ll leave that to you.

• Let’s consider another case – we need to report which entries belong to a specific Market. To determine which entries belong to which markets, Episerver Commerce uses a special metafield, named_ExcludedCatalogEntryMarkets which is a multi-value dictionary. As its name might suggest, it contains list of the MarketId which the entry does not belong to, for example, if_ExcludedCatalogEntryMarkets contains ‘US’ then the entry is not available in ‘US’ market. So if we are to find entries which belong to ‘US’ market, we have to find entries which do not have ‘US’ value for _ExcludedCatalogEntryMarkets.
  

  DECLARE @MetaFieldId INT
  DECLARE @MetaDictionaryId INT
  
  SET @MetaFieldId = (SELECT MetaFieldId FROM MetaField 
  WHERE Name = '_ExcludedCatalogEntryMarkets' AND Namespace = 'Mediachase.Commer 6 ce.Catalog')
  SET @MetaDictionaryId = (SELECT MetaDictionaryId FROM MetaDictionary WHERE Value = 'US' AND MetaFieldId = @MetaFieldId)
   
  SELECT ObjectId FROM CatalogContentProperty
  WHERE 
  MetaFieldId = @MetaFieldId AND
  Number NOT IN
  (
  SELECT mk.MetaKey from MetaMultiValueDictionary mmv
  INNER JOIN MetaKey mk on mmv.MetaKey = mk.MetaKey
  WHERE mk.MetaFieldId = @MetaFieldId
  AND MetaDictionaryId = @MetaDictionaryId
  )
  

Same as previous script, we need to find the Id of _ExcludedCatalogEntryMarkets MetaField, then theMetaDictionaryId of ‘US’ market. The next statement is tricky – we need to find MetaKey which value matching value for ‘US’, then except them. Again, you can join with other tables for more data.