EPiServer – Get all Blocks filtered by Class Type

While researching about how to get all blocks of an specific type we found that the only available solution was to use the ContentModelUsage API . However, if some of the blocks are not referenced in a page this API will not be useful. Moreover, if a block is referenced 10.000 times in the CMS it will return the block 10.000 and we will have to apply distinct filters to the list causing several performance issues We could have used EPiServer Find, but in the scenario we tested it took longer than the content usage API, so we had to think another possible solution.

At the end, we decided to use a direct query to the database to get the media files borrowing some code from the 404 Handler for EPiServer. This post, will explain how we did it and how to use it. Just as a reminder, we do not recommend to use queries directly to the database due to performance (EPiServer APIs have their own cache implementation) and maintenance (Database structure may change) issues.

So, lets begin !!!!

First, we will create the author block types that we are going to query.

namespace Data.Models.Block
{
    [ContentType(GUID = "c1a7bff1-5951-abc2-ba6b-b55aca1f4a7b")]
    public class AuthorBlock : BlockData
    {
        [CultureSpecific]
        [Required]
        [Display(GroupName = SystemTabNames.Content, Order = 200)]
        public virtual string BioName { get; set; }
        
        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 400)]
        public virtual string BioPosition { get; set; }

        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 500)]
        public virtual XhtmlString BioDescription { get; set; }
    }
}

Then, we will add the code that query the database directly and returns a IEnumerable of the block type we pass as parameter. Part of this code was borrowed from the 404 handler as mentioned before.

namespace Business.Util
{
    public class CustomDataAccessBaseEx : EPiServer.DataAccess.DataAccessBase
    {
        private Injected<IContentRepository> ContentRepository;

        public CustomDataAccessBaseEx(IDatabaseExecutor handler)
            : base(handler)
        {
            Executor = handler;
        }

        public static CustomDataAccessBaseEx GetWorker()
        {
            return EPiServer.ServiceLocation.ServiceLocator.Current.GetInstance();
        }

        public DataSet ExecuteSql(string sqlCommand, List parameters)
        {
            return Executor.Execute(delegate
            {
                using (var ds = new DataSet())
                {
                    try
                    {
                        var command = CreateCommand(sqlCommand);
                        if (parameters != null)
                        {
                            foreach (var dbDataParameter in parameters)
                            {
                                var parameter = (SqlParameter)dbDataParameter;
                                command.Parameters.Add(parameter);
                            }
                        }
                        command.CommandType = CommandType.Text;
                        CreateDataAdapter(command).Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("An error occured in the ExecuteSQL method. Exception:{ex}", ex.Message);
                    }

                    return ds;
                }
            });

        }
        
        public IEnumerable<T> GetAllBlocks<T>(string typeName) where T : BlockData
        {
            var sqlCommand = "SELECT DISTINCT(tbw.fkContentID) FROM[dbo].[tblWorkContent] tbw " +
                             "INNER JOIN[dbo].[tblContent] tc ON tc.pkID = tbw.fkContentID " +
                             "INNER JOIN[dbo].[tblContentType] tct ON tct.pkID = tc.fkContentTypeID " +
                             $"WHERE tct.Name = '{typeName}'";

            var dataSet = ExecuteSql(sqlCommand, null);

            foreach (DataTable table in dataSet.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    foreach (int item in row.ItemArray)
                    {
                        yield return ContentRepository.Service.Get<T>(new ContentReference(item));
                    }
                }
            }
        }
    }
}

The important part of this code is the GetAllBlocks function, which gets the list of blocks based on the block type. It uses inner joins to improve a little bit the performance cost of making the call to the database. Now, with this code, we can execute a query using the example below.

private readonly Injected<IPublishedStateAssessor> PublishedStateAssessor;

public List<T> GetBlocks<T>() where T : BlockData
{
    var dataAccess = CustomDataAccessBaseEx.GetWorker();
    var blocks = dataAccess.GetAllBlocks<T>(typeof(T).Name);
    var filteredBlocks = blocks.Where(z => IsContentPublished((IContent)z)).ToList();

    return filteredBlocks;
}

public bool IsContentPublished(IContent content)
{
    return PublishedStateAssessor.Service.IsPublished(content, PagePublishedStatus.Published);
}

And that is all. Now, if you want to find a all block types in the CMS by type (class name), you can use this method. It is not the most efficient way to do it, but helped to solve our needs, performance wise it is not too expensive and does not return duplicate items as the content type usage repository API. I hope it will help someone and as always keep learning !!!