protected virtual void CompileStatementParts(MIA_Management miaManagement, IFilter filter, IFilter subqueryFilter, Namespace ns, BindVarNamespace bvNamespace,
                                                     ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
                                                     IList <object> resultParts, IList <BindVar> resultBindVars)
        {
            if (filter == null)
            {
                return;
            }

            MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;

            if (mediaItemIdFilter != null)
            {
                ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
                if (mediaItemIds.Count == 0)
                {
                    resultParts.Add("1 = 2");
                }
                else
                {
                    if (mediaItemIds.Count == 1)
                    {
                        resultParts.Add(outerMIIDJoinVariable);
                        BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
                        resultParts.Add(" = @" + bindVar.Name);
                        resultBindVars.Add(bindVar);
                    }
                    else
                    {
                        bool first = true;
                        ICollection <string> clusterExpressions = new List <string>();
                        foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
                        {
                            IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
                            foreach (Guid mediaItemId in mediaItemIdsCluster)
                            {
                                BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
                                bindVarRefs.Add("@" + bindVar.Name);
                                resultBindVars.Add(bindVar);
                            }
                            if (!first)
                            {
                                resultParts.Add(" OR ");
                            }
                            first = false;
                            resultParts.Add(outerMIIDJoinVariable);
                            resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
                        }
                        resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
                    }
                }
                return;
            }

            BooleanCombinationFilter boolFilter     = filter as BooleanCombinationFilter;
            ICollection <IFilter>    filterOperands = boolFilter?.Operands; //Work on collection reference to avoid chaning original

            if (boolFilter != null && boolFilter.Operator == BooleanOperator.And && boolFilter.Operands.Count > 1 && boolFilter.Operands.ToList().All(x => x is IAttributeFilter))
            {
                ICollection <IFilter> remainingOperands = new List <IFilter>();

                // Special case to do multiple MIA boolean logic first
                IDictionary <Guid, ICollection <IAttributeFilter> > multiGroups = new Dictionary <Guid, ICollection <IAttributeFilter> >();
                foreach (IAttributeFilter operand in filterOperands)
                {
                    MultipleMediaItemAspectMetadata mmiam = operand.AttributeType.ParentMIAM as MultipleMediaItemAspectMetadata;
                    if (mmiam != null)
                    {
                        Guid key = operand.AttributeType.ParentMIAM.AspectId;
                        if (!multiGroups.ContainsKey(key))
                        {
                            multiGroups[key] = new List <IAttributeFilter>();
                        }
                        multiGroups[key].Add(operand);
                    }
                    else
                    {
                        remainingOperands.Add(operand);
                    }
                }

                if (multiGroups.Keys.Count > 0)
                {
                    bool firstGroup = true;
                    foreach (ICollection <IAttributeFilter> filterGroup in multiGroups.Values)
                    {
                        if (firstGroup)
                        {
                            firstGroup = false;
                        }
                        else
                        {
                            resultParts.Add(" AND ");
                        }

                        bool firstItem = true;
                        foreach (IAttributeFilter filterItem in filterGroup)
                        {
                            MediaItemAspectMetadata.AttributeSpecification attributeType = filterItem.AttributeType;
                            if (firstItem)
                            {
                                resultParts.Add(outerMIIDJoinVariable);
                                resultParts.Add(" IN(");
                                resultParts.Add("SELECT ");
                                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                                resultParts.Add(" FROM ");
                                resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
                                resultParts.Add(" WHERE ");

                                firstItem = false;
                            }
                            else
                            {
                                resultParts.Add(" AND ");
                            }
                            //Empty filter needs to be handled differently to other IAttribute filters
                            if (filterItem is EmptyFilter)
                            {
                                resultParts.Add(miaManagement.GetMIAAttributeColumnName(attributeType));
                                resultParts.Add(" IS NULL");
                            }
                            else
                            {
                                BuildAttributeFilterExpression(filterItem, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
                            }
                        }
                        resultParts.Add(")");
                    }

                    // Process remaining operands ?
                    if (remainingOperands.Count == 0)
                    {
                        return;
                    }

                    resultParts.Add(" AND ");
                    filterOperands = remainingOperands;
                }
            }
            if (boolFilter != null)
            {
                int         numOperands  = filterOperands.Count;
                IEnumerator enumOperands = filterOperands.GetEnumerator();
                if (!enumOperands.MoveNext())
                {
                    return;
                }
                if (numOperands > 1)
                {
                    resultParts.Add("(");
                }
                CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                while (enumOperands.MoveNext())
                {
                    switch (boolFilter.Operator)
                    {
                    case BooleanOperator.And:
                        resultParts.Add(" AND ");
                        break;

                    case BooleanOperator.Or:
                        resultParts.Add(" OR ");
                        break;

                    default:
                        throw new NotImplementedException(string.Format(
                                                              "Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
                    }
                    CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
                                          requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                }
                if (numOperands > 1)
                {
                    resultParts.Add(")");
                }
                return;
            }

            NotFilter notFilter = filter as NotFilter;

            if (notFilter != null)
            {
                resultParts.Add("NOT (");
                CompileStatementParts(miaManagement, notFilter.InnerFilter, subqueryFilter, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                resultParts.Add(")");
                return;
            }

            FalseFilter falseFilter = filter as FalseFilter;

            if (falseFilter != null)
            {
                resultParts.Add("1 = 2");
                return;
            }

            // Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
            // compiled in a different way
            EmptyFilter emptyFilter = filter as EmptyFilter;

            if (emptyFilter != null)
            {
                MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    resultParts.Add(new QueryAttribute(attributeType));
                    resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V WHERE V.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
                    resultParts.Add(" NM INNER JOIN ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V ON NM.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" = V.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" WHERE NM.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                return;
            }

            // Must be done before checking IAttributeFilter - EmptyUserDataFilter is also an IAttributeFilter but must be
            // compiled in a different way
            EmptyUserDataFilter emptyUserDataFilter = filter as EmptyUserDataFilter;

            if (emptyUserDataFilter != null)
            {
                BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), emptyUserDataFilter.UserProfileId, typeof(Guid));

                resultParts.Add("NOT EXISTS(");
                resultParts.Add("SELECT 1");
                resultParts.Add(" FROM ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                resultParts.Add(" WHERE ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                resultParts.Add(" = @" + userIdVar.Name);
                resultBindVars.Add(userIdVar);
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                resultParts.Add(" = '");
                resultParts.Add(emptyUserDataFilter.UserDataKey);
                resultParts.Add("' AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                resultParts.Add(" IS NOT NULL ");
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                resultParts.Add(" <> '' ");
                resultParts.Add(" AND ");
                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                resultParts.Add("=");
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(")");

                return;
            }

            SharePathFilter sharePathFilter = filter as SharePathFilter;

            if (sharePathFilter != null)
            {
                IMediaLibrary library  = ServiceRegistration.Get <IMediaLibrary>(false);
                bool          anyAdded = false;

                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(" IN(");
                resultParts.Add("SELECT ");
                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                resultParts.Add(" FROM ");
                resultParts.Add(miaManagement.GetMIATableName(ProviderResourceAspect.Metadata));
                resultParts.Add(" WHERE ");

                if (library != null && sharePathFilter.ShareIds.Any())
                {
                    var allShares = library.GetCachedShares(null);
                    foreach (var share in allShares)
                    {
                        if (!allShares.Any() || !sharePathFilter.ShareIds.Contains(share.Key))
                        {
                            continue;
                        }

                        BindVar bindVar;
                        if (!anyAdded)
                        {
                            //Allow virtual objects like parents etc.
                            bindVar = new BindVar(bvNamespace.CreateNewBindVarName("S"), "{00000000-0000-0000-0000-000000000000}:///%", typeof(string));
                            resultParts.Add(miaManagement.GetMIAAttributeColumnName(ProviderResourceAspect.ATTR_RESOURCE_ACCESSOR_PATH));
                            resultParts.Add($" LIKE @{bindVar.Name}");
                            resultBindVars.Add(bindVar);
                            anyAdded = true;
                        }

                        bindVar = new BindVar(bvNamespace.CreateNewBindVarName("S"), share.Value.BaseResourcePath + "%", typeof(string));
                        resultParts.Add(" OR ");
                        resultParts.Add(miaManagement.GetMIAAttributeColumnName(ProviderResourceAspect.ATTR_RESOURCE_ACCESSOR_PATH));
                        resultParts.Add($" LIKE @{bindVar.Name}");
                        resultBindVars.Add(bindVar);
                    }
                }

                if (!anyAdded)
                {
                    //No shares are allowed
                    resultParts.Add(miaManagement.GetMIAAttributeColumnName(ProviderResourceAspect.ATTR_RESOURCE_ACCESSOR_PATH));
                    resultParts.Add(" = ''");
                }
                resultParts.Add(")");

                return;
            }

            AbstractRelationshipFilter relationshipFilter = filter as AbstractRelationshipFilter;

            if (relationshipFilter != null)
            {
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(" IN(");
                BuildRelationshipSubquery(relationshipFilter, subqueryFilter, miaManagement, bvNamespace, resultParts, resultBindVars);
                resultParts.Add(")");
                return;
            }

            RelationalUserDataFilter relationalUserDataFilter = filter as RelationalUserDataFilter;

            if (relationalUserDataFilter != null)
            {
                BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.UserProfileId, typeof(Guid));
                BindVar bindVar   = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.FilterValue, typeof(string));

                resultParts.Add("(EXISTS(");
                resultParts.Add("SELECT 1");
                resultParts.Add(" FROM ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                resultParts.Add(" WHERE ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                resultParts.Add(" = @" + userIdVar.Name);
                resultBindVars.Add(userIdVar);
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                resultParts.Add(" = '");
                resultParts.Add(relationalUserDataFilter.UserDataKey);
                resultParts.Add("' AND (");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                switch (relationalUserDataFilter.Operator)
                {
                case RelationalOperator.EQ:
                    resultParts.Add(" = ");
                    break;

                case RelationalOperator.NEQ:
                    resultParts.Add(" <> ");
                    break;

                case RelationalOperator.LT:
                    resultParts.Add(" < ");
                    break;

                case RelationalOperator.LE:
                    resultParts.Add(" <= ");
                    break;

                case RelationalOperator.GT:
                    resultParts.Add(" > ");
                    break;

                case RelationalOperator.GE:
                    resultParts.Add(" >= ");
                    break;

                default:
                    throw new NotImplementedException(string.Format(
                                                          "Relational user data filter operator '{0}' isn't supported by the media library", relationalUserDataFilter.Operator));
                }
                resultParts.Add("@" + bindVar.Name);
                resultBindVars.Add(bindVar);
                if (relationalUserDataFilter.IncludeEmpty)
                {
                    resultParts.Add(" OR ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                    resultParts.Add(" IS NULL ");
                }
                resultParts.Add(") AND ");
                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                resultParts.Add("=");
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(")");
                if (relationalUserDataFilter.IncludeEmpty)
                {
                    resultParts.Add(" OR NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                    resultParts.Add(" WHERE ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                    resultParts.Add(" = @" + userIdVar.Name);
                    resultParts.Add(" AND ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                    resultParts.Add(" = '");
                    resultParts.Add(relationalUserDataFilter.UserDataKey);
                    resultParts.Add("'");
                    resultParts.Add(" AND ");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                resultParts.Add(")");
                return;
            }

            IAttributeFilter attributeFilter = filter as IAttributeFilter;

            if (attributeFilter != null)
            {
                MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
                if (attributeType.ParentMIAM is MultipleMediaItemAspectMetadata)
                {
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(" IN(");
                    resultParts.Add("SELECT ");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
                    resultParts.Add(" WHERE ");
                    BuildAttributeFilterExpression(attributeFilter, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
                    resultParts.Add(")");

                    return;
                }

                // For attribute filters, we have to create different kinds of expressions, depending on the
                // cardinality of the attribute to be filtered.
                // For Inline and MTO attributes, we simply create
                //
                // QA [Operator] [Comparison-Value]
                //
                // for OTM attributes, we create
                //
                // INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // WHERE [...] and V.VALUE [Operator] [Comparison-Value])
                //
                // for MTM attributes, we create
                //
                // INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
                // WHERE [...] AND V.VALUE [Operator] [Comparison-Value])

                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
                                                   resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
                    {
                        TableQueryData tqd = new TableQueryData(joinTable);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                                     new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
                        attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
                        _innerJoinedTables.Add(joinTable, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
                    {
                        TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));

                        TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                                     new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
                        attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
                        _innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                return;
            }
            throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
        }