Blog: Bypass the content API for better performance - 5.cs

/// <summary>
/// A complex example that returns the actual descendants that inherit from a specific template id.
/// </summary>
/// <param name="item">The item for which to determine the descendants.</param>
/// <param name="templateId">The ID of the template of which the result items have to inherit.</param>
/// <returns>The actual descendants that inherit from the template that was passed.</returns>
public IEnumerable<Item> GetDescendantsInheritingTemplate(Item item, Sitecore.Data.TemplateID templateId)
{
    Assert.IsNotNull(item, "A valid Sitecore item must be provided to this method");

    // query SQL server directly
    string query = @"with cteChildren as
                    (select ID, TemplateID
                    from [dbo].[Items]
                    where ID = CAST(@itemId AS UNIQUEIDENTIFIER)
                    union all
                    select e.ID, e.TemplateID
                    from cteChildren cte
                    inner join [dbo].[Items] e
                    on cte.ID = e.ParentID),
                cteSubItems as
                (
                select ItemId from SharedFields
                where FieldId = '12C33F3F-86C5-43A5-AEB4-5598CEC45116'
                    and Value like '%' + @templateId + '%'
                    union all
                select sf.ItemId from SharedFields sf inner join cteSubItems ic
                on sf.Value like '%' + CAST(ic.ItemId as varchar(36)) + '%'
                where sf.FieldId = '12C33F3F-86C5-43A5-AEB4-5598CEC45116'
                )
                select cteChild.ID
                from (select ItemId from cteSubItems union select @templateId) cteSit
                    inner join cteChildren cteChild on cteSit.ItemId = cteChild.TemplateID";

    Sitecore.Data.DataProviders.Sql.DataProviderReader reader = dataApi.CreateReader(query,
        "itemId", item.ID.ToString().Trim(new[] { '{', '}' }),
        "templateId", templateId.ID.ToString().Trim(new[] { '{', '}' }));

    // return to the content API to get the actual items (therefore also applies security)
    List<Item> result = new List<Item>();
    while (reader.Read())
    {
        Sitecore.Data.ID itemId = new Sitecore.Data.ID(reader.InnerReader.GetGuid(0));
        if (itemId != item.ID)
        {
            Item descItem = Sitecore.Context.Database.GetItem(itemId);
            if (descItem != null)
            {
                result.Add(descItem);
            }
        }
    }
    return result;
}