FIX: Catalog caching is not used when you use the CategoryConfiguration class together with the CategoryConfiguration.ChildProducts.SqlWhereClause property in Commerce Server 2009

Applies to: Commerce Server 2009 EnterpriseCommerce Server 2009 Standard


Consider the following scenario:
  • You enable caching in the catalog system in the Web.config file for a website that is running Microsoft Commerce Server 2009.
  • You use the CategoryConfiguration class to specify the preloaded configuration of a Category object.
  • You set the LoadChildProducts property to True.
  • You specify a filtering clause in the CategoryConfiguration.ChildProducts.SqlWhereClause property.
  • You use the GetCategory method or the GetProduct method to retrieve child categories or child products of the CategoryConfiguration object.

In this scenario, catalog caching may not be used to retrieve the child categories and child products. Therefore, the website may experience decreased performance when there is high load.

For example, you have a website that uses the AdventureWorks sample catalog. You run the following code example to retrieve the display name of a Category object. In this example, the code queries data from the Catalog database every time, although catalog caching is enabled.
CategoryConfiguration categoryConfiguration = new CategoryConfiguration();

categoryConfiguration.LoadChildProducts = true;

categoryConfiguration.ChildProducts.SqlWhereClause = "[IsVisible] = 1";

var catalogContext = CommerceContext.Current.CatalogSystem.CatalogContext;

Category category = catalogContext.GetCategory("AdventureWorks Catalog", "SleepingBags");

Note This same problem occurs when you use the GetProduct method in the code example.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.


To resolve this problem, apply this hotfix to the server that is running Commerce Server 2009.

After you apply this hotfix, caching child products and child categories in the catalog cache is allowed when you use a "whereClause" filter. By default, any query that contains a where clause bypasses the catalog cache. In order to enable caching, you must add a "cacheFilter" section and configure it correctly in the Web.config file for the Commerce Server website.

The "cacheFilter" section provides the following attributes:
  • The "whereClause" attribute.
  • The "exactMatch" attribute.

You can use one of the following options to manage which where clause to cache:
  • Specify the entire where clause in the "whereClause" attribute, and then set the "exactMatch" attribute to True.
  • Specify a where clause in the "whereClause" attribute, and then set the "exactMatch" attribute to False. This enables Commerce Server to cache any where clause that has a partial match to what was specified in the configuration file.

Note The "exactMatch" attribute is optional. By default, if you omit the "exactMatch" attribute, it is set to True.

The following example shows how to modify the configuration section. This example also shows the usage of the "exactMatch" attribute.

<catalogSets .../>

<cache enable="true">


<add whereClause="WHERE [ListPrice] between 0 AND 1000'" />

<add whereClause="isActive" exactMatch="False" />




Rules in defining the "whereClause" attribute value in the cacheFilter section

Note The value of the "whereClause" attribute in the "cacheFilter" section is string-matched. For example, the following rules must be followed:
  • Only one white space is allowed among the "whereClause" attribute value keywords. For example:
    • whereClause="ProductID like 'AW2%'" is not matched
    • whereClause="ProductID like 'AW2%'" is matched
  • No white space(s) is allowed at the beginning or end of the "whereClause" attribute value if there is a multiple keywords value. For example:
    • whereClause=" ProductID like 'AW2%'" is not matched
    • whereClause="ProductID like 'AW2%' " is not matched
  • The singled-quoted sub-value within the "whereClause" attribute must be fully string-matched. For example, assume that the search query is "ProductID like 'AW2%'"; in this case,
    • whereClause="ProductID like 'AW2' is not a match
    • whereClause="ProductID like 'AW2%' is a match

More information about this hotfix

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft website: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.


To apply this hotfix, you must have Commerce Server 2009 or Commerce Server 2009 Template Pack for SharePoint 2010 installed.

Restart information

You do not have to restart the computer after you apply this hotfix. However, you must restart Internet Information Services (IIS) after you apply this hotfix. To restart IIS, open a Command Prompt window, type the following command, and then press ENTER:
iisreset /restart
Note This option stops all IIS services that are running and then restarts them.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

For all supported versions of Commerce Server 2009
File nameFile versionFile sizeDateTimePlatform
For all supported versions of Commerce Server 2009 Template Pack for SharePoint 2010
File nameFile versionFile sizeDateTimePlatform


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

Please be aware of the following two points during and after the uninstallation of the hotfix:
  1. A dialog window may be displayed. The dialog window warns you that some applications need to be closed before you can continue with the uninstallion process. In this situation, accept the default selection and click the OK button to continue.
  2. After you uninstall the hotfix for Commerce Server 2009, both Commerce Server 2009 and Commerce Server 2007 may appear in the Start menu. In this situation, you can ignore or delete the Commerce Server 2007 menu item.
For more information, visit the following Microsoft Developer Network (MSDN) websites:
To download Commerce Server 2009 Template Pack for SharePoint 2010, visit the following Microsoft website: