Sitecore - get all orphan items C#

Question

I need to get a list of all of the orphaned items in Sitecore. I'm not sure how to go about doing this. My initial idea was to start at the root and get all descendants, then check every item to see if it item.Parent is null, but if an item is an orphan, I'm not sure if it will show up in the list of descendants? Does anyone know how to go about finding orphans in Sitecore?

Answer 1

You cannot get the orphaned items using Sitecore Api.

You need to use sql query on the database directly.

Sitecore has already a method called CleanupOrphans which is called from the Cleanup Database task. It uses following query to remove orphaned items from the database:

string text = " SELECT ID FROM Items i1 WHERE i1.ParentID <> null AND NOT EXISTS(SELECT ID FROM Items i2 WHERE i2.ID = i1.ParentID)";
string sql = " DELETE FROM Items WHERE ID IN (" + text + ")";