Duplicate key in ecfVersionAssets when saving product assets


I'm saving assets through a scheduled job that looks at a folder and maps image names to products. At some point, the implementation was not correct: I believe that an exception before saving the product with its newly assigned assets left the assets in a partially invalid state. So assets were saved to the VPP, but not saved to the product's.

Now, when saving assets to some products using the scheduled job I get the following error:

2017-06-21 12:14:37,419 [84] ERROR BBSCatalyst.Services.ScheduledJobs.AssetIngestionScheduledJob: Attempting to import files for product with code BD489_48 but caught exception
System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.ecfVersionAsset' with unique index 'IDX_ecfVersionAsset_ContentID'. The duplicate key value is (916951, , b216679a-dc90-47ee-927b-b638e66aa6b2).
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
   at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters)
   at EPiServer.Commerce.Catalog.DataAccess.CatalogContentVersionDB.UpdateContentVersion(List`1 mappingData, DataTable workIdMapping, Boolean publishAction)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentVersionStore.CreateContentVersion(CatalogContentBase content, String currentUser, Boolean publishAction, Boolean skipSetCommonDraft)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.SaveVersionInternal(CatalogContentBase content, SaveAction action, String currentUser)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.Save(CatalogContentBase content, SaveAction action, String currentUser)
   at EPiServer.Commerce.Catalog.Provider.CatalogContentProvider.Save(IContent content, SaveAction action)
   at EPiServer.Core.Internal.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
   at Castle.Proxies.Invocations.IContentRepository_Save.InvokeMethodOnTarget()
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at BBSCatalyst.Integration.Interception.MiniProfilerInterceptor.Intercept(IInvocation invocation) in c:\adir\BBSCatalyst.Integration\Interception\MiniProfilerInterceptor.cs:line 13
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.Proxies.IContentRepositoryProxy.Save(IContent content, SaveAction action, AccessLevel access)
   at BBSCatalyst.Services.ScheduledJobs.AssetIngestionScheduledJob.AddAssetsToProduct(BaseEolProductContent product, AssetInfo[] assetInfos) in c:\adir\BBSCatalyst.Services\ScheduledJobs\AssetIngestionScheduledJob.cs:line 682
   at BBSCatalyst.Services.ScheduledJobs.AssetIngestionScheduledJob.CheckFolder(String importFolderName, Boolean insideUnzippedFolder, List`1& messages) in c:\adir\BBSCatalyst.Services\ScheduledJobs\AssetIngestionScheduledJob.cs:line 182
Error Number:2601,State:1,Class:14

Here's my code:

private void AddAssetsToProduct(BaseEolProductContent product, AssetInfo[] assetInfos)
  // Save a new version of the product and get it from the repo
  var contentRepository = ServiceLocator.Current.GetInstance();            
  var writeableProduct = product.CreateWritableClone();
  var productReference = contentRepository.Save(writeableProduct, SaveAction.ForceNewVersion, AccessLevel.NoAccess);
  writeableProduct = contentRepository.Get(productReference).CreateWritableClone();

  // Make a map of assets in the db to assets in the product's media collection, 
  // skipping any assets that don't exist in the db
  GenericFile ignore;
  var currentMedia = writeableProduct.CommerceMediaCollection
      cmcItem => contentRepository.TryGet(cmcItem.AssetLink, out ignore))
      cmcItem => contentRepository.Get(cmcItem.AssetLink),
      cmcItem => cmcItem);

  // Adding assets to the product's CommerceMediaCollection happens here:
  // Loop through the assets to add. Remove existing media collection items that have
  // the same name. Add the new asset to the product.
  foreach (var assetInfo in assetInfos)
    var currentMediaWithSameName = currentMedia
      .Where(cmItem => cmItem.Key.Name.Equals(assetInfo.Name))
      .Select(cmItem => cmItem.Value);
    foreach (var commerceMedia in currentMediaWithSameName)
      .Add(new CommerceMedia(assetInfo.Link, string.Empty, assetInfo.Group, 0));

  // Create a dictionary of asset names to asset content references
  var assetDictionary = new Dictionary();
  foreach (var commerceMedia in writeableProduct.CommerceMediaCollection)
    MediaData mediaData;
    var retrievedCollectionItem = contentRepository.TryGet(commerceMedia.AssetLink, out mediaData);
    if (!retrievedCollectionItem || assetDictionary.ContainsKey(mediaData.Name))

    assetDictionary.Add(mediaData.Name, commerceMedia.AssetLink);

  // Sort the dictionary by name
  var index = 0;
  var sortedAssetDictionary = assetDictionary.OrderBy(d => d.Key)
    // then create a new dictionary with media and their sort order
    .ToDictionary(kvp => kvp.Value, kvp => index++);

  // Set the sort order for each asset, or place it at the end.
  foreach (var commerceMedia in writeableProduct.CommerceMediaCollection)
    var sortedAssetItem = sortedAssetDictionary.FirstOrDefault(d => d.Key.Equals(commerceMedia.AssetLink));
    commerceMedia.SortOrder = sortedAssetDictionary.ContainsKey(commerceMedia.AssetLink)
      ? sortedAssetItem.Value
      : index;

  // Exception happens here
  contentRepository.Save(writeableProduct, SaveAction.Publish | SaveAction.ForceNewVersion, AccessLevel.NoAccess);         

My theory in using SaveAction.ForceNewVersion for the product was that I would get a new version of assets, too, and that would prevent the duplicate key in the ecfAssetVersion table. However, I still get the duplicate key error. 

How can I overwrite or force a new version of the assets?

(I'm on EPiServer Commerce 10.6)

Edited, Jun 21, 2017 20:34

Just a quick question. Are you sure the error is happening at 

// Exception happens here
  contentRepository.Save(writeableProduct, SaveAction.Publish | SaveAction.ForceNewVersion, AccessLevel.NoAccess);        

And not at this?

// Save a new version of the product and get it from the repo
  var contentRepository = ServiceLocator.Current.GetInstance();            
  var writeableProduct = product.CreateWritableClone();
  var productReference = contentRepository.Save(writeableProduct, SaveAction.ForceNewVersion, AccessLevel.NoAccess);

Because we have the same code and it works fine for us. Why you want to clone the product to force new version for assets? Just curious.

Jun 22, 2017 17:31

Yes I'm certain that's where the exception is thrown: the line number from the stack trace corresponds to the second call to save the content.

I was hoping that, by saving a new version of the content, that a new workid for the content and all its assets would be created. That seems to be the gist of the duplicate key exception: you can't have two asset mappings for the same product which have the same asset key and work ID.

EPi support has suggested I use the Asset Importer: http://world.episerver.com/documentation/developer-guides/commerce/catalogs/assets-and-media/Asset-Importer/, which uses CatalogSystem interface instead of the Content interface, so perhaps that will work.

But, I agree with you, that I shouldn't have to save a clone. I think we got our system into an invalid state somehow.

Jun 22, 2017 17:40

This is what we are doing.

var fileID = SaveMediaContent(contentRepository, cImage, groupName, ref errorMessage);

In SaveMediaContent we first save the image file and get its content reference in fileID.

_contentRepository.Save(file, SaveAction.Publish | SaveAction.ForceNewVersion, AccessLevel.NoAccess);

Then we do this. 

//If the previous file was added to a product content we just add it to the media collection.

if (previousProductContent != null)
if (SKU.Equals(previousProductContent.Code))
currentVariantLink = variantLink;
currentCommerceMedia = commerceMedia;
changeCurrentContent = true;
Logger.GetLogger.Debug("Starting contentRepository.Save previousProductContent.");
contentRepository.Save(previousProductContent, SaveAction.Publish | SaveAction.ForceNewVersion, AccessLevel.NoAccess);
SetCurrentContent(variantLink, contentLoader, commerceMedia);
Logger.GetLogger.Debug("Ending contentRepository.Save previousProductContent. ");

This might help. AssetImporter is a great tool. But if you still want to make your code work.

Jun 22, 2017 18:25
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.