Awful faceting with NPoco and SQL Server 2012

NPocoFaceting.cs

using NPoco;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;

namespace NPocoFaceting
{
    public class FacetBuilder
    {
        private IList<Facet> _facets = new List<Facet>();

        public void Add(Facet facet)
        {
            _facets.Add(facet);
        }

        public Sql CreateFilter(params string[] ignoredFacets)
        {
            var sql = new Sql();
            var hit = false;
            foreach (var facet in _facets)
            {
                if (ignoredFacets.Contains(facet.Name)) continue;
                hit = true;
                sql = sql.Where(facet.Filter.SQL, facet.Filter.Arguments);
            }
            if (!hit) return new Sql("WHERE 1=1");
            return sql;
        }

        public Sql ToSql(Sql baseQuery)
        {
            var selectSql = new Sql();
            var sql = new Sql("; WITH _FACETSOURCE AS (");
            sql.Append(baseQuery.SQL, baseQuery.Arguments);
            sql.Append(")");

            for (var i = 0; i < _facets.Count; ++i)
            {
                var where = CreateFilter(_facets[i].Name);
                var value = _facets[i];

                sql.Append(", FACET_" + i + "_INNER AS (SELECT (");
                sql.Append(value.Expression.SQL, value.Expression.Arguments);
                sql.Append(") AS [key], (CASE WHEN (");
                sql.Append(value.SelectionFilter.SQL, value.SelectionFilter.Arguments);
                sql.Append(") THEN 1 ELSE 0 END) AS [selected] FROM _FACETSOURCE");
                sql.Append("GROUP BY (");
                sql.Append(value.Expression.SQL, value.Expression.Arguments);
                sql.Append("))");

                sql.Append(", FACET_" + i + " AS (SELECT *");
                sql.Append(", (SELECT COUNT(1) FROM _FACETSOURCE");
                sql.Append(where.SQL, where.Arguments);
                sql.Append(" AND src.[key] = (");
                sql.Append(value.Expression.SQL, value.Expression.Arguments);
                sql.Append(")) as [count] FROM FACET_" + i + "_INNER AS src)");

            }


            sql.Append("SELECT ");
            for (var i = 0; i < _facets.Count; ++i)
            {
                sql.Append(i > 0 ? "," : "");
                sql.Append(" (SELECT * FROM FACET_" + i);
                if (!_facets[i].IncludeZeroes)
                {
                    sql.Append(" WHERE [count] > 0 OR [selected] = 1");
                }
                sql.Append(" FOR XML PATH('facet'), TYPE) as [FACET_" + i + "]");
            }
            sql.Append("FOR XML PATH('facets'), TYPE;");
            return sql;
        }

        public IList<FacetResult> ParseFacets(string facetString)
        {
            return XDocument.Parse(facetString)
                .Root
                .Elements()
                .Select(el => new FacetResult
                {
                    Name = _facets[Convert.ToInt32(el.Name.LocalName.Substring("FACET_".Length))].Name,
                    Values = el.Elements().Select(val => new FacetResultValue
                    {
                        Key = val.Element("key").Value,
                        Count = Convert.ToInt32(val.Element("count").Value),
                        IsSelected = Convert.ToInt32(val.Element("selected").Value) != 0
                    }).ToList()
                })
                .ToList();
        }
    }

    public class Facet
    {
        public Facet(string name, object[] selections)
            : this(name, new Sql(name), selections)
        {
        }

        public Facet(string name, Sql expression, object[] selections)
        {
            Name = name;
            HasSelections = selections.Length > 0;
            Filter = CreateFilter(expression, selections);
            Expression = expression;
        }

        public Facet(string name, Sql filter, Sql expression, bool hasSelections)
        {
            Name = name;
            HasSelections = hasSelections;
            Filter = filter;
            Expression = expression;
        }


        private Sql CreateFilter(Sql expression, object[] selectedValues)
        {
            var sql = new Sql("(1=1)");
            if (selectedValues.Length > 0)
            {
                sql = new Sql(expression.SQL, expression.Arguments);
                sql.Append(" IN (");
                for (var i = 0; i < selectedValues.Length; ++i)
                {
                    if (i > 0) sql.Append(",");
                    sql.Append("@0", selectedValues[i]);
                }
                sql.Append(")");
            }
            return sql;
        }


        public string Name { get; set; }
        public Sql Expression { get; set; }
        public Sql Filter { get; set; }
        public bool HasSelections { get; set; }
        public bool IncludeZeroes { get; set; }

        public Sql SelectionFilter
        {
            get { return HasSelections ? Filter : new Sql("(1=0)"); }
        }

    }

    public class FacetResult
    {
        public string Name { get; set; }
        public IList<FacetResultValue> Values { get; set; }
    }

    public class FacetResultValue
    {
        public string Key { get; set; }
        public int Count { get; set; }
        public bool IsSelected { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Database("server=.;data source=exampledb;integrated security=sspi;", DatabaseType.SqlServer2012))
            {
                string sqlCount, sqlPage;

                // create a base query, used as the data source for your facets.
                var query = new Sql("select * from users");
                query.Where("country = 'US'");

                // create a facet query and add the fields to collect facets for.
                var facetBuilder = new FacetBuilder();
                facetBuilder.Add(new Facet("Disabled", new object[] { }));
                facetBuilder.Add(new Facet("Role", new object[] { 1 }));

                // build an sql query to load the facets.
                var facetSql = facetBuilder.ToSql(query);

                // you can quickly apply all selected facets to your base query using 'CreateFilter'.
                query.Append(facetBuilder.CreateFilter());
                
                // for the heck of it, add some paging too
                query.OrderBy("Name");
                var queryArgs = query.Arguments;
                db.BuildPageQueries<dynamic>(0, 5, query.SQL, ref queryArgs, out sqlCount, out sqlPage);

                // now we'll create our main query, which combines for the facets query with your regular query (optional ofc.)
                var sql = new Sql();
                sql.Append(facetSql);
                sql.Append(sqlCount + ";" + sqlPage, queryArgs);

                // load the recordsets, recordset 1 is an xml of the facets, recordset 2+3 is our paging query.
                var data = db.FetchMultiple<string, int, dynamic>(sql);

                // parse the facet xml into a more useable form.
                var facets = facetBuilder.ParseFacets(data.Item1[0]);
                Console.WriteLine(facets);
            }

            Console.ReadKey(true);
        }
    }
}