Hi

I have a commerce solution where HierarchicalCatalogPartialRouter is used to change the url for products. I have found out that when using the original HierarchicalCatalogPartialRouter the sites makes alot of SQL requests for properties if the products are not cached. The reason for it is that there exists alot of products in the catalog folders. Most of the products are also expired, but is requested anyway.

The problem is in the FindNextContentInSegmentPair(CatalogContentBase catalogContent, SegmentPair segmentPair, SegmentContext segmentContext, CultureInfo cultureInfo) method, when loading children for a catalog node:

return this._contentLoader.GetChildren(catalogContent.ContentLink, cultureInfo).FirstOrDefault((CatalogContentBase x) => segmentPair.Next.Equals(x.RouteSegment, StringComparison.OrdinalIgnoreCase));

When requesting the RouteSegment, a call to the SQL server is made to get the properties of the product.

I want to override the HierarchicalCatalogPartialRouter and make better FindNextContentInSegmentPair that gets the correct child in 1 request. Is there a way I can filter on RouteSegment directly, or filter out expired products?

EPiServer v9.12.3
EpiServer Commerce v9.24.1

Because of the project's complexity and dependencies of 3rd party libraries I am unable to upgrade to EPiServer Commerce 10 yet.

Original method:

protected virtual CatalogContentBase FindNextContentInSegmentPair(CatalogContentBase catalogContent, SegmentPair segmentPair, SegmentContext segmentContext, CultureInfo cultureInfo)
{
	HierarchicalCatalogPartialRouter._logger.DebugBeginMethod("FindNextContentInSegmentPair", new object[]
	{
		catalogContent,
		segmentPair,
		segmentContext,
		cultureInfo
	});
	Validator.ThrowIfNull("catalogContent", catalogContent);
	if (catalogContent.ContentType == CatalogContentType.Root || catalogContent.ContentType == CatalogContentType.Catalog)
	{
		return this._contentLoader.GetChildren(catalogContent.ContentLink, cultureInfo).FirstOrDefault((CatalogContentBase x) => segmentPair.Next.Equals(x.RouteSegment, StringComparison.OrdinalIgnoreCase));
	}
	return this._contentLoader.GetBySegment(catalogContent.ContentLink, segmentPair.Next, cultureInfo) as CatalogContentBase;
}