Find orphan variants



Im in situation where I need to find all the varaiants in the catalog that has no relation to a product and then delete them.

I was thinking to make a search in the catalog for all variants and then iterate trough them.

Then using the linkrepository to see if there is a relation to a product and if not delete it.

This was a good idea in my dev enviorment where i have 300-400 products but in the prod env. there is about 25k, this can be costly.

Is there a more efficient way to do this?


Jan 26, 2015 10:34


You can use a simple SQL to get the CatalogEntryId to delete, for example:

select catalogentryId FROM [dbo].[CatalogEntry]
where ClassTypeId = 'Variation' and
catalogentryid not in (Select ChildEntryId from [dbo].CatalogEntryRelation where RelationTypeId = 'ProductVariation')

Then delete the content by the catalog entry ids you got.

It's always recommended to back up your database first.



Jan 26, 2015 10:49

Thanks for your reply, I wanted to use the api but this is more simple, I will have a try and mark your answer if sucessful.

Jan 26, 2015 11:30

Here is the final solution, that is a shedualTask.

var listOfOrphans = VariantCleanupQueries.GetOrphans().ToList();
if (listOfOrphans.Any())
return string.Format("Deleted {0} orphans.", listOfOrphans.Count);

GetOrphans uses your SQL.


Jan 26, 2015 14:21
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* 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.