This is an excerpt from my book – Pro Episerver Commerce – which is now already 2/3 complete.
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
- 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
typeof(PropertyDictionaryMultiple) backing type
Both single and multi value dictionary types are fully supported in Catalog UI, including editing:
- 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!
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
_ExcludedCatalogEntryMarketswhich 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
_ExcludedCatalogEntryMarketscontains ‘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
Same as previous script, we need to find the Id of
_ExcludedCatalogEntryMarkets MetaField, then the
MetaDictionaryId 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.