Beware of GetContentResult()

If you are using Episerver Search & Navigation (Formerly Find), you are likely using GetContentResult() . While I’m fairly skeptical about it (if you do not need the content explicitly, it’s probably a good idea to use GetResult instead), there are legitimate uses of the APIs. However, it can come with a surprise, and a not very pleasant one.

Your code probably looks like this

SearchClient.Instance.Search<IContent>()
  .For("banana")
  .StaticallyCacheFor(15)
  .GetContentResult();

Looks good, right? You search for content with keyword “banana” and cache the result for 15 minutes.

But is it? Does your search result really get cached for 15 minutes?

As it might surprise you, it doesn’t. GetContentResult() caches the result by default (unlike GetResult() which does not), but it only caches for 1 minutes. Even thought you asked it to cache for 15 minutes.

The right way to do it is to use other overload which takes an int as parameter. That is the cache time in seconds, like this

SearchClient.Instance.Search<IContent>()
  .For("banana")
  .GetContentResult(900);

In upcoming version of Find, the surprise will be fixed, but it’s probably a good idea to specify the cache time out explicitly.

Name or Display name in Catalog UI: you can choose

Since the beginning of Catalog UI, it had always shown Name, in both Catalog Tree and the Catalog content list.

That, however, was changed to DisplayName since 13.14 due to a popular feature request here https://world.episerver.com/forum/developer-forum/Feature-requests/Thread-Container/2019/12/use-localized-catalog-in-commerce-catalog-ui/#214650

All is good and the change was positively received. However not every is happy with it – some want it the old way, i.e. `Name` to be displayed. From a framework perspective, it might be complex to let partners configure which field to display. But if you are willing to do some extra work, then it’s all easy.

Catalog content is transformed using CatalogContentModelTransform, this is where DisplayName is added to the data returned to the client. If you override that, you can set DisplayName to whatever you want, for example, Name.

Here is what the implementation would look like

using EPiServer.Cms.Shell.UI.Rest.Models.Transforms;
using EPiServer.Commerce;
using EPiServer.Commerce.Catalog;
using EPiServer.Commerce.Catalog.ContentTypes;
using EPiServer.Commerce.Catalog.Linking;
using EPiServer.Commerce.Shell.Rest;
using EPiServer.Framework.Localization;
using EPiServer.ServiceLocation;
using Mediachase.Commerce.Catalog;
using Mediachase.Commerce.Customers;
using Mediachase.Commerce.InventoryService;
using Mediachase.Commerce.Markets;
using Mediachase.Commerce.Pricing;

namespace EPiServer.Reference.Commerce.Site.Infrastructure
{
    [ServiceConfiguration(typeof(IModelTransform))]
    public class BlahBlahBlah : CatalogContentModelTransform
    {
        public BlahBlahBlah(ExpressionHelper expressionHelper, IPriceService priceService, IMarketService marketService, IInventoryService inventoryService, LocalizationService localizationService, ICatalogSystem catalogContext, IRelationRepository relationRepository, ThumbnailUrlResolver thumbnailUrlResolver, CustomerContext customerContext) : base(expressionHelper, priceService, marketService, inventoryService, localizationService, catalogContext, relationRepository, thumbnailUrlResolver, customerContext)
        {
        }

        public override TransformOrder Order
        {
            ///Yes, this is very important to make it work
            get { return base.Order + 1; }
        }

        protected override void TransformInstance(IModelTransformContext context)
        {
            var catalogContent = context.Source as CatalogContentBase;
            var properties = context.Target.Properties;

            if (catalogContent is NodeContent nodeContent)
            {
                properties["DisplayName"] = nodeContent.Name;
            }
            if (catalogContent is EntryContentBase entryContent)
            {
                properties["DisplayName"] = entryContent.Name;
            }
        }
    }
}

And here is how it looks




A few notes:

  • CatalogContentModelTransform, and other APIs in Commerce.Shell, are not considered public APIs, so they might change without notice. There is a risk for adding this, however, it’s quite low.
  • This (or the bug fix) does not affect breadcrumb, it has been, and still is, showing Name.

Export catalog, with linked assets

If you are already using a PIM system, you can stop reading!

If you have been using Commerce for a while, you probably have seen this screen – yes, in Commerce Manager

This allow you to export a catalog, but without a caveat: the exported catalog, most likely, does not contains any linked assets. The reason for that was the asset content types need to be present at the context of the site. In Commerce Manager, the general advice is to not deploy the content types there for simpler management.

Import/Export are also missing features in Catalog UI compared to Commerce Manager. I wish I could have added it, but given my Dojo skill, it’s better to write something UI-less, and here you go: a controller to let you download a catalog with everything attached. Well, here is the entire code that you can drop into your project and build:

using EPiServer.Data;
using EPiServer.Framework.Blobs;
using EPiServer.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web.Http;
using EPiServer.Commerce.Catalog.ContentTypes;
using Mediachase.Commerce.Catalog;
using Mediachase.Commerce.Catalog.ImportExport;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Xml;

namespace EPiServer.Personalization.Commerce.CatalogFeed.Internal
{
    /// <summary>
    /// Download a catalog.
    /// </summary>
    public class CatalogExportController : ApiController
    {
        private readonly CatalogImportExport _importExport;
        private readonly IBlobFactory _blobFactory;
        private readonly IContentLoader _contentLoader;
        private readonly ReferenceConverter _referenceConverter;
        internal const string DownloadRoute = "episerverapi/catalogs/";
        private static readonly Guid _blobContainerIdentifier = Guid.Parse("119AD01E-ECD1-4781-898B-6DEC356FC8D8");

        private static readonly ILogger _logger = LogManager.GetLogger(typeof(CatalogExportController));

        /// <summary>
        /// Initializes a new instance of the <see cref="CatalogExportController"/> class.
        /// </summary>
        /// <param name="importExport">Catalog import export</param>
        /// <param name="blobFactory">The blob factory.</param>
        /// <param name="contentLoader">The content loader.</param>
        /// <param name="referenceConverter"></param>
        public CatalogExportController(CatalogImportExport importExport,
            IBlobFactory blobFactory,
            IContentLoader contentLoader,
            ReferenceConverter referenceConverter)
        {
            _importExport = importExport;
            _blobFactory = blobFactory;
            _contentLoader = contentLoader;
            _referenceConverter = referenceConverter;

            _importExport.IsModelsAvailable = true;
        }

        /// <summary>
        /// Direct download catalog export for admins.
        /// </summary>
        /// <param name="catalogName">Name of catalog to be exported.</param>
        /// <returns>
        /// Catalog.zip if successful else HttpResponseMessage containing error.
        /// </returns>
        [HttpGet]
        [Authorize(Roles = "CommerceAdmins")]
        [Route(DownloadRoute)]
        public HttpResponseMessage Index(string catalogName)
        {
            var catalogs = _contentLoader.GetChildren<CatalogContent>(_referenceConverter.GetRootLink());
            var catalog = catalogs.First(x => x.Name.Equals(catalogName, StringComparison.OrdinalIgnoreCase));
            if (catalog != null)
            {
                return GetFile(catalog.Name);
            }

            return new HttpResponseMessage
            { Content = new StringContent($"There is no catalog with name {catalogName}.") };
        }

        private HttpResponseMessage GetFile(string catalogName)
        {
            var container = Blob.GetContainerIdentifier(_blobContainerIdentifier);
            var blob = _blobFactory.CreateBlob(container, ".zip");
            using (var stream = blob.OpenWrite())
            {
                using (var zipArchive = new ZipArchive(stream, ZipArchiveMode.Create, false))
                {
                    var entry = zipArchive.CreateEntry("catalog.xml");

                    using (var entryStream = entry.Open())
                    {
                        _importExport.Export(catalogName, entryStream, Path.GetTempPath());
                    }
                }
            }

            var response = new HttpResponseMessage
            {
                Content = new PushStreamContent(async (outputStream, content, context) =>
                {
                    var fileStream = blob.OpenRead();

                    await fileStream.CopyToAsync(outputStream)
                        .ContinueWith(task =>
                        {
                            fileStream.Close();
                            outputStream.Close();

                            if (task.IsFaulted)
                            {
                                _logger.Error($"Catalog download failed", task.Exception);
                                return;
                            }

                            _logger.Information($"Feed download completed.");

                        });
                }, new MediaTypeHeaderValue("application/zip"))
            };
            return response;
        }
    }
}

And now you can access to this path http://yoursite.com/episerverapi/catalogs?catalogName=fashion to download the catalog named “Fashion”.

A few notes:

  • This requires Admin access, for obvious reasons. You will need to log in to your website first before accessing the path above
  • It can take some time for big catalogs, so be patient if that’s the case ;). Yes another approach is to have this as a scheduled job when you can export the catalog in background, but that make the selection of catalog to export much more complicated. If you have only one catalog, then go ahead!

Include/IncludeOn/Exclude/ExcludeOn: a simple explanation

When I come across this question https://world.episerver.com/forum/developer-forum/-Episerver-75-CMS/Thread-Container/2020/3/trouble-with-availablecontenttypesattribute-excludeonincludeon/ I was rather confused by the properties of AvailableContentTypesAttribute (admittedly I don’t use them that often!). Looking at the code that defined them, or the XML documentation does not really help. I only come to an understanding when I look into how they are used, and I guess many other developers, especially beginners, might have same confusion, so here’s a simple explanation.

Include : defines content types that can be created as children of a content of this type (being decorated by the attribute)

IncludeOn: defines content types that can be parent of a content of this type

Exclude: defines content types that can not be created as children of a content of this type

ExcludeOn: defines content types that can not be parent of a content of this type.

If there is a conflict between those properties, for example content type A has Include with content type B, and content type B has ExcludeOn with content type A, then Exclude and ExcludeOn take priority (i.e. they will override Include and IncludeOn. In the example above then content type B will not be able to be children of content type A)

While AvailableContentTypesAttribute is extremely helpful, the property naming is not the best – they are short and symmetric, but they are not easy to understand and remember. An “improved” example might be

CanBeParentOf

CanBeChildrenOf

CannotBeParentOf

CannotBeChildrenOf

Yes they are more verbose, but they are unambiguous and you will not have to check the document (or this blog post) when you use them.

This is not the first time we have something that rather confusing in our API. One notable example is the old (now removed) ILinksRepository with the Source and Target properties in Relation . For quite some time I always had to check the code to know what to use, and then had the documentation updated, and eventually, changed to Parent and Child. No API is created perfect, but we can improve over time.

It might be a good idea to renew your card every year

Your cards, credit or debit, might be valid for 3-5 years. Most of you will keep them until they near expiry. But it might be a good idea to renew them every year. Why?

To remove the subscriptions you don’t really need, and to block shady businesses that do not let you cancel your subscriptions!

Most banks allow ordering cards for free. Here in Sweden it’s as easy as log in to your bank website and order a new card. After a couple of days, a new card will be sent to your with same PIN code as before. Convenient, right?

You will of course need to update your payment information on every website that you need the membership, but with Chrome/Firefox, that can be done easily.

And now, you can just forget about your unwanted subscriptions, or the ones with shady business that make it very easy to subscribe, but extremely difficult to unsubscribe.

It’s more common than you think, from a less known shop like JustFab https://vimvq1987.com/beware-of-unwanted-subscriptions/ , or a well known entity like, America’s Test Kitchen https://www.americastestkitchen.com/support#change-membership-status

Your only option is to call!

Yeah, right. When you subscribe, it’s as easy as just your name and your – of course – card number. When you want to cancel your membership, you need to call a number, and not only that, they actively make it as confusing as possible https://www.reddit.com/r/Cooking/comments/ae8qfw/trying_to_cancel_my_americas_test_kitchen_online/

I call the number and there is a 10+ minute wait according to the automated message. Fine, I’ll wait.

A few minutes in and the call just hangs up on me. I’ve called back and it happens again and again at random points (drops 22 seconds into the call, 53 seconds into the call, etc.)

It is not only shady, but illegal practice in many place. I, however, do not have the time or resource to deal with them. The solution? Cancel the card I used with them and then switch to my new card. Problem solved!

Disabling Catalog Dto cache: maybe, don’t?

Recently (as recent as this morning) I was asked to look into a case when the Find indexing performance was subpar. Upon investigation – looking at a properly captured trace from dotTrace – it was clear that at least 30% percent of time was spending in loading the CatalogDto

This is something that should not happen, as the CatalogDto should have been cached. Also, a normal site should have very few catalogs, so the cache should be very effective. However, data does not lie – it has been hitting database a lot, and a quick check on the site settings revealed that the entire DTO cache has been indeed, disabled

 <Cache enabled="false" collectionTimeout="0:0:0" entryTimeout="0:0:0" nodeTimeout="0:0:0" schemaTimeout="0:0:0" /> 

By setting these timeout settings to 0, the cache is immediately invalidated, rendering them useless. The CatalogDto, therefore, is loaded everytime from database, causing the bottleneck.

The reason for setting those timeout to 0 was probably – I guess – to reduce the memory footprint of the site. However, Catalog DTOs are fairly small in size, and since Commerce 11, it has been smart enough to skip caching the DTOs if there is cache on a higher (content ) level, thanks to my colleague Magnus Rahl. So DTOs should not be of any concerns, if you are not actively using them (and in most of the case, you should not). By re-enabling the cache, the indexing time can be cut, at least 30%, according to the aforementioned trace.

As you might wonder, Catalog content provider still uses the DTOs internally, therefore it would load those for data.

Moral of the story:

  • The cache settings are there, but because you can, does not mean you should. I personally think cache settings should be as hidden as possible from accidental changes. Disabling cache, and in a lesser extend, changing default cache timeout, can have unforeseeable consequences. Only do so if you have strong reasons to do so. Or better, let us know why you need to do that, and we can figure out a compromise.

Looking for static class fields in Windbg

I am looking into the ever growing problem with LambdaExpression cache in Find, as reported here https://world.episerver.com/forum/developer-forum/Problems-and-bugs/Thread-Container/2019/9/episerver-find-lambdaexpressionextensions-_cache-keeps-growing-indefinately/ . One important part of analyzing cache is to understand how many items are in cache, and how is the cache hit ratio. I have received the memory dumps from our partners, time to fire up some Windbg. Luckily for us that is stored in the class as fields. Unluckily for us, the class in question is a static one, it is when you find out !dumpheap -type is not working for you.

The right way would be to use !name2ee

0:000> !name2ee episerver.find.dll EPiServer.Find.LambdaExpressionExtensions
Module:      00007ffd770bef80
Assembly:    EPiServer.Find.dll
Token:       000000000200000d
MethodTable: 00007ffd79c998e8
EEClass:     00007ffd79c8d268
Name:        EPiServer.Find.LambdaExpressionExtensions

!name2ee takes two parameters, the first one is the module name (basically the name of the assembly), and the second one is the name of the class. It is important to note that the class name is case sensitive, so you have to give it name with correct casing.

Now you have the EEClass and you just need to dump it using !Dumpclass

0:000> !DumpClass /d 00007ffd79c8d268
Class Name:      EPiServer.Find.LambdaExpressionExtensions
mdToken:         000000000200000d
File:            C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root5a589a\ddb1376c\assembly\dl3c58139
0:000> !DumpClass /d 00007ffd79c8d268
Class Name:      EPiServer.Find.LambdaExpressionExtensions
mdToken:         000000000200000d
File:            C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\775a589a\ddb1376c\assembly\dl3\26c58139\00ecff94_a3aed501\EPiServer.Find.dll
Parent Class:    00007ffd76045498
Module:          00007ffd770bef80
Method Table:    00007ffd79c998e8
Vtable Slots:    4
Total Method Slots:  7
Class Attributes:    100181  Abstract, 
Transparency:        Critical
NumInstanceFields:   0
NumStaticFields:     3
              MT    Field   Offset                 Type VT     Attr            Value Name
00007ffd7d0c4e28  4000009        8 ...egate, mscorlib]]  0   static 000001e6008e6768 _cache
00007ffd760d0d90  400000a      398         System.Int64  1   static 87633 _compiles
00007ffd760d0d90  400000b      3a0         System.Int64  1   static 34738206 _calls
ecff94_a3aed501\EPiServer.Find.dll Parent Class: 00007ffd76045498 Module: 00007ffd770bef80 Method Table: 00007ffd79c998e8 Vtable Slots: 4 Total Method Slots: 7 Class Attributes: 100181 Abstract, Transparency: Critical NumInstanceFields: 0 NumStaticFields: 3 MT Field Offset Type VT Attr Value Name 00007ffd7d0c4e28 4000009 8 ...egate, mscorlib]] 0 static 000001e6008e6768 _cache 00007ffd760d0d90 400000a 398 System.Int64 1 static 87633 _compiles 00007ffd760d0d90 400000b 3a0 System.Int64 1 static 34738206 _calls

And voilà!

Dynamic data store is slow, (but) you can do better.

If you have been developing with Episerver CMS for a while, you probably know about its embedded “ORM”, called Dynamic Data Store, or DDS for short. It allows you to define strongly typed types which are mapped to database directly to you. You don’t have to create the table(s), don’t have to write stored procedures to insert/query/delete data. Sounds very convenient, right? The fact is, DDS is quite frequently used, and more often than you might think, mis-used.

As Joel Spolsky once said Every abstraction is leaky, an ORM will likely make you forget about the nature of the RDBMS under neath, and that can cause performance problems, sometime severe problems.

Let me make it clear to you

DDS is slow, and it is not suitable for big sets of data.

If you want to store a few settings for your website, DDS should be fine. However, if you are thinking about hundreds of items, it is probably worth looking else. Thousands and more items, then it would be a NO.

I did spend some time trying to bench mark the DDS to see how bad it is. A simple test is to add 10.000 items to a store, then query by each item, then deleted by each item, to see how long does it take

The item is defined like this, this is just another boring POCO:

internal class ShippingArea : IDynamicData
{
    public Identity Id { get; set; }

    public string PostCode { get; set; }

    public string Area { get; set; }

    public DateTime Expires { get; set; }
}

The store is defined like this

    public class ShippingAreaStore
    {
        private const string TokenStoreName = "ShippingArea";

        internal virtual ShippingArea CreateNew(string postCode, string area)
        {
            var token = new ShippingArea
            {
                Id = Identity.NewIdentity(),
                PostCode = postCode,
                Area = area,
                Expires = DateTime.UtcNow.AddDays(1)
            };
            GetStore().Save(token);
            return token;
        }

        internal virtual IEnumerable<ShippingArea> LoadAll()
        {
            return GetStore().LoadAll<ShippingArea>();
        }

        internal virtual IEnumerable<ShippingArea> Find(IDictionary<string, object> parameters)
        {
            return GetStore().Find<ShippingArea>(parameters);
        }

        internal virtual void Delete(ShippingArea shippingArea)
        {
            GetStore().Delete(shippingArea);
        }

        internal virtual ShippingArea Get(Identity tokenId)
        {
            return GetStore().Load<ShippingArea>(tokenId);
        }

        private static DynamicDataStore GetStore()
        {
            return DynamicDataStoreFactory.Instance.CreateStore(TokenStoreName, typeof(ShippingArea));
        }
    }

Then I have some quick and dirty code in QuickSilver ProductController.Index to measure the time (You will have to forgive some bad coding practices here ;). As usual StopWatch should be used on demonstration only, it should not be used in production. If you want a good break down of your code execution, use tools like dotTrace. If you want to measure production performance, use some monitoring system like NewRelic or Azure Application Insights ):

        var shippingAreaStore = ServiceLocator.Current.GetInstance<ShippingAreaStore>();
        var dictionary = new Dictionary<string, string>();
        for (int i = 0; i < 10000; i++)
        {
            dictionary[RandomString(6)] = RandomString(10);
        }
        var identities = new List<ShippingArea>();
        var sw = new Stopwatch();
        sw.Start();
        foreach (var pair in dictionary)
        {
            shippingAreaStore.CreateNew(pair.Key, pair.Value);
        }
        sw.Stop();
        _logger.Error($"Creating 10000 items took {sw.ElapsedMilliseconds}");
        sw.Restart();
        foreach (var pair in dictionary)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("PostCode", pair.Key);
            parameters.Add("Area", pair.Value);
            identities.AddRange(shippingAreaStore.Find(parameters));
        }

        sw.Stop();
        _logger.Error($"Querying 10000 items took {sw.ElapsedMilliseconds}");
        sw.Restart();

        foreach (var id in identities)
        {
            shippingAreaStore.Delete(id);
        }
        sw.Stop();
        _logger.Error($"Deleting 10000 items took {sw.ElapsedMilliseconds}");

Everything is ready. So a few tries gave us a fairly stable result:

2019-12-02 13:33:01,574 Creating 10000 items took 11938

2019-12-02 13:34:59,594 Querying 10000 items took 118009

2019-12-02 13:35:24,728 Deleting 10000 items took 25131

And this is strictly single-threaded, the site will certainly perform worse when it comes to real site with a lot of traffic, and then insert-query-delete at the same time, it will certainly be worse.

Can we do better?

There is a little better attribute that many people don’t know about DDS: you can mark a field as indexed, by adding [EPiServerDataIndex] attribute to the properties. The new class would look like this.

    [EPiServerDataStore]
    internal class ShippingArea : IDynamicData
    {
        public Identity Id { get; set; }

        [EPiServerDataIndex]
        public string PostCode { get; set; }

        [EPiServerDataIndex]
        public string Area { get; set; }

        public DateTime Expires { get; set; }
    }

If you peek into the database during the test, you can see that the data is now being written to Indexed_String01 and Indexed_String02 columns, instead of String01 and String02 as without the attributes. Such changes give us quite drastic improvement:

2019-12-02 15:38:16,376 Creating 10000 items took 7741

2019-12-02 15:38:19,245 Querying 10000 items took 2867

2019-12-02 15:38:44,266 Deleting 10000 items took 25019

The querying benefits greatly from the new index, as it no longer has to do a Clustered Index Scan, it can now do a non clustered index seek + Key look up. Deleting is still equally slow, because the delete is done by a Clustered Index delete on the Id column, which we already have, and the index on an Uniqueidentifier column is not the most effective one.

Before you are happy which such improvement, keep in mind that there are two indexes added for Indexed_String01 and Indexed_String02 separately. Naturally, we would want a combination, clustered even, on those columns, but we just can’t.

What if we want to go bare metal and create a table ourselves, write the query ourselves? Our repository would look like this

public class ShippingAreaStore2
    {
        private readonly IDatabaseExecutor _databaseExecutor;

        public ShippingAreaStore2(IDatabaseExecutor databaseExecutor)
        {
            _databaseExecutor = databaseExecutor;
        }

        /// <summary>
        /// Creates and stores a new token.
        /// </summary>
        /// <param name="blobId">The id of the blob for which the token is valid.</param>
        /// <returns>The id of the new token.</returns>
        internal virtual ShippingArea CreateNew(string postCode, string area)
        {
            var token = new ShippingArea
            {
                Id = Identity.NewIdentity(),
                PostCode = postCode,
                Area = area,
                Expires = DateTime.UtcNow.AddDays(1)
            };
            _databaseExecutor.Execute(() =>
            {
                var cmd = _databaseExecutor.CreateCommand();
                cmd.CommandText = "ShippingArea_Add";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("Id", token.Id.ExternalId));
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("PostCode", token.PostCode));
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("Area", token.Area));
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("Expires", token.Expires));
                cmd.ExecuteNonQuery();
            });

            return token;
        }

        internal virtual IEnumerable<ShippingArea> Find(IDictionary<string, object> parameters)
        {
            return _databaseExecutor.Execute<IEnumerable<ShippingArea>>(() =>
            {
                var areas = new List<ShippingArea>();
                var cmd = _databaseExecutor.CreateCommand();
                cmd.CommandText = "ShippingArea_Find";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("PostCode", parameters.Values.First()));
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("Area", parameters.Values.Last()));
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    areas.Add(new ShippingArea
                    {
                        Id = (Guid)reader["Id"],
                        PostCode = (string)reader["PostCode"],
                        Area = (string)reader["Area"],
                        Expires = (DateTime)reader["Expires"]
                    });
                }
                return areas;
            });
        }

        /// <summary>
        /// Deletes a token from the store.
        /// </summary>
        /// <param name="token">The token to be deleted.</param>
        internal virtual void Delete(ShippingArea area)
        {
            _databaseExecutor.Execute(() =>
            {
                var cmd = _databaseExecutor.CreateCommand();
                cmd.CommandText = "ShippingArea_Delete";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("PostCode", area.PostCode));
                cmd.Parameters.Add(_databaseExecutor.CreateParameter("Area", area.Area));
                cmd.ExecuteNonQuery();
            });
        }
    }

And those would give us the results:

2019-12-02 16:44:14,785 Creating 10000 items took 2977

2019-12-02 16:44:17,114 Querying 10000 items took 2315

2019-12-02 16:44:20,307 Deleting 10000 items took 3190

Moral of the story? DDS is slow and you should be avoid using it if you are working with fairly big set of data. If you have to use DDS for whatever reason, make sure to at least try to index the columns that you query the most.

And in the end of the days, hand-crafted custom table + query beats everything. Remember that you can use some tools like Dapper to do most of the works for you.

Hide certain tabs in Catalog UI

It has been a while since I write something in my blog – have been “fairly” busy making Commerce even faster for a while. But I should take a break from time to time and share things that will benefit community as a whole – and this is one of that break.

Today I come across this question on World https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2019/10/remove-item-from-tab-in-content-editor/ . Basically, how to hide a specific tab in the Catalog UI when you open All Properties view of a catalog content.

The original poster has found a solution from https://world.episerver.com/forum/legacy-forums/Episerver-7-CMS/Thread-Container/2013/10/Is-there-any-way-to-hide-the-settings-tab/ . While it works, I think it is not the easiest or simple way to do it. Is there a simpler way? Yes.

The Related Entries tab is generated for content with implements IAssociating interface. Bad news is EntryContentBase implements that interface, so each and every entry type you have, has that tab. But good news is we can override the implementation – by just override the property defined by IAssociating.

How?

Simple as this

        /// <inheritdoc />
        [IgnoreMetaDataPlusSynchronization]
        [Display(AutoGenerateField = false)]
        public override Associations Associations { get; set; }

We are overriding the Associations property, and the change the Display attribute to have AutoGenerateField = false. Just try to build it and see

No Related Views! But is it the end of the story. Not yet, Related Views can still be accessed by the menu

A complete solution is to also disable that view. How? By using the same technique here https://world.episerver.com/blogs/Quan-Mai/Dates/2019/8/enable-sticky-mode-for-catalog-content/ i.e. using `UIDescriptor`. You can disable certain views by adding this to your constructor

AddDisabledView(CommerceViewConfiguration.RelatedEntriesEditViewName);

A few notes:

  • This only affects the type you add the property, so for example you can hide the tab for Products, but still show it for Variants.
  • Related Entries is not the only tab you can hide. By applying the same technique you can have a lot of control over what you can hide, and what you show. I will leave that to you for exploration!

Pension fund in Sweden, an overview

In Sweden, like many other western countries, each working person has an individual pension fund. This is vastly different from some other countries like Vietnam, where the pension fund is shared, not only between every working person, but also between other purposes (maternity leaves, sick leaves…) . Like many other things in Sweden, the pension fund is transparent to you, and you can manage part of it, to some level. If you do things “correctly”, then it might make a sizable impact on your pension you you retire

A three parts pension scheme

Pension fund in Sweden consists of 3 parts. If you have been in Sweden for more than 1 year, you can always check https://www.minpension.se/ to see how much money you have in your pension fund (your information is only “added” in your first November here)

General pension (Swedish: Allmän pension)

Every working person in Sweden will receive this pension, as contributed by their employer. Every year, 18.5% of your pensionable income, up to a limit, is contributed to this part of your pension. The limit is set as 7.5 PBA – Price Base Amount. This is adjusted by Swedish government every year, and in 2019, 1 PBA = 64.400 SEK, meaning your upper limit this year is 483.000SEK. If you are making more than that in your pensionable income (good for you!), then your public pension contribution is still capped at 89.355 SEK.

The public pension actually has 2 parts of itself:

The income pension (Inkomstpension)

which is 16% of your pensionable income. You can see this, (i.e. how much money do you have), but you can’t manage it. The state will invest the money the way it sees fit, however you can guess that the money is invested in some low risk, low return bonds.

The premium pension (premiepension)

which is 2.5% of your pensionable income. You can actually manage this at https://www.pensionsmyndigheten.se/

By default, your premium pension is put into AP7 Såfa, which is actually a very good fund. It has very low fee (only 0.06 – 0.1%/year), and good return rate. It is also an adaptive fund, which means it will invest more in bonds (which is “safer”, but returns less than stocks) as you age. When you are less than 55 years old, 100% is your money is put into shares, and that will reduce as you age, at 65 (your expected retirement age), it’s 67% stocks and 33% bonds, at 75, it’s 33% stocks and 67% bonds

Pensionsmyndigheten is great, because not only you can manage your fund here, but also it has a wide range of funds for you to choose, at a superbly discounted fee. The discount is small on index funds (which already have cheap management fees by themselves), usually at 0.16%/year compared to 0.2%/year originally, but it is very significant for actively managed funds.

Most of them are discounted for more than 1%. For example: Skandia Time Global has a management fee of 1.4% year, and on top of that, you have to pay transaction fee (Transaktionskostnad) of 0.25%, in total your fee is 1.65%/year. At Pensionsmyndigheten you only pay 0.39%. Which means you get 1.26% gain per year for free! As the biggest fund manager in Sweden, they have the leverage to negotiate with other fund managers to cut down the fees, and that is really good for you.

You can leave your money as-is, I think the default choice is very decent. But you are free to make your bet to potentially make more money.

The only bad thing about Pensionsmyndigheten is that you can’t put more money to their fund, even if you want to. That low fee is just so great.

Occupational pension (Tjänstepension)

Most, but not all, employers in Sweden give their employees the occupational pension. In case your employer doesn’t – like mine – then you will likely have to pay it yourself.

Most of the companies follow ITP scheme, which is in short:

  • ITP1, for people who were born in 1979 and after, which is
    • 4.5% of your salary, up to 7.5 PBA/year, so basically 40.250SEK/month in 2019.
    • 30% of your salary part that is higher than 7.5 PBA/year.
  • ITP 2, for people who were born in 1978 and before
    • 10% of your pensionable income, up to 7.5 PBA
    • 65% of your pensionable income, from 7.5 to 20 PBA
    • 32.5% of your pensionable income, from 20 to 30 PBA

The numbers are updated here https://www.collectum.se/sv/Privat/ITP/ITP-1-och-ITP-2/

So if your salary is higher than 40.250SEK, your occupational insurance increases very fast.

If you get less than, or equal to, 40.250SEK/month, then your total pension contribution is 23% of your pensionable income.

If you are get, for example, 50.000SEK/month, then your total pension contribution is as follow:

9250 SEK from general pension

1811.25 + 2925 SEK from occupational pension

Total: 13986.25 SEK = 27.9725%

So if you are a high income person, this part can be very significant in your pension fund, and by default you don’t have a good choice as with premium pension, so you should pay close attention to it.

Just like premium pension, you can of course manage this pension, even to a bigger extend, as you can choose the provider yourself. In many cases, however, the choices of provider can be limited, depending on your employer contract. Notable providers in the market are:

  • Most banks, including big fours (SEB, Swedbank, Nordea & Handelsbanken) and smaller ones (Skandia, Länsförsäkringar)…
  • Avanza
  • Nordnet
  • SPP
  • Söderberg & Partners

As always, it’s best to talk with your company HR and/or your fund manager to see which options do you have. I’d always recommend to choose the one which lowest fees (less than 0.3%/year) and the biggest fund portfolio. Note that the provider might have a more limited fund portfolio for pension, so make sure to check that.

With occupational pension you have too choices, or actually three:

  • Funds (Fondförsäkring)
  • Traditional insurance (Traditionell försäkring)
  • A mix between those two.

When I started paying my occupational pension, I went with Traditional insurance because I am clueless about other options. In the end, I moved my pension into funds. Why? Lower cost with higher return.

Skandia Traditionell försäkring: 0.55% management fee, with an average 8.4% return/year in the last 4 years (2014-2018), down to 5% from May 1st 2019.

An US index fund: 0.2% management fee, with an average 18%/year in the last 5 years.

It’s not even close! Assuming I put 5000 SEK/month into my occupational pension (which is not the actually number), and the traditional insurance return is 5.3%/year, while the index fund return is 9.5%/year – both after fees, after 30 years:

  • Traditional Insurance gives me 4.419.370 SEK
  • Us index fund gives me 10.245.650 SEK

The choice was easy!

What if you don’t know what funds to invest? Nordnet for example, provides about 1400 funds in their portfolio. Which to choose? A general recommendation is to invest your money into an index fund – a type of fund that follow an index, like Standard & Poor 500, passively. The common characteristics of index funds are they are very low cost (commonly 0.2%/year fee), they yield market return, which is, in most of the cases, very good. The average return for S&P 500 index since its inception has been about 9.7%/year.

A well known advice is to have a three parts portfolio: an US index fund (follow S&P 500 index), a global market index fund, and a total market bond fund. It’s up to you, your age, your risk tolerance … to decide how many percent you want to invest in each. If you ask me, then I’d would say invest into an US index fund (most banks have this fund), and forget about it. If you are older, then you might want to put part of your money into bonds, and if you are younger, you might want to bet by putting more money into actively managed funds – which might have higher return than index funds, but with higher fee. In long term, index funds should still be a majority of your investment portfolio. Avoid funds with high fees. Avoid paying for people to adjust funds for you – the less they take, the more you make.

Private pension

This is your own pension contribution. However since 1/1/2016 this is no longer a “tax deductible” contribution, so there are very few people are still contributing to this part. I personally don’t, so I will skip writing about it.