litedb-org/LiteDB · error · NotSupportedException

GROUP BY expression do not support INCLUDE

Error message

GROUP BY expression do not support INCLUDE

What it means

Thrown by QueryOptimization.DefineGroupBy when a query combines GROUP BY with one or more INCLUDE clauses. Grouping aggregates rows into buckets, which is semantically incompatible with INCLUDE (which expands related documents per source row). The optimizer rejects this combination early during query planning with a NotSupportedException rather than producing ambiguous results.

Source

Thrown at LiteDB/Engine/Query/QueryOptimization.cs:563

                if (orderBy.Segments.Count == 1)
                {
                    orderBy = null;
                }
            }

            // otherwise, query.OrderBy will be set according user defined
            _queryPlan.OrderBy = orderBy;
        }

        /// <summary>
        /// Define GroupBy optimization (try re-use index)
        /// </summary>
        private void DefineGroupBy()
        {
            if (_query.GroupBy == null) return;

            if (_query.Includes.Count > 0) throw new NotSupportedException("GROUP BY expression do not support INCLUDE");

            var expression = _query.GroupBy;
            var select = _queryPlan.Select.Expression;
            var having = _query.Having;
            var groupOrderBy = (OrderBy)null;

            // if groupBy use same expression in index, no additional ordering is required before grouping
            if (expression.Source == _queryPlan.IndexExpression)
            {
                // index already provides grouped ordering
            }
            else
            {
                // create orderBy expression
                groupOrderBy = new OrderBy(new[] { new OrderByItem(expression, Query.Ascending) });
            }

            _queryPlan.GroupBy = new GroupBy(expression, select, having, groupOrderBy);

View on GitHub (pinned to f906a5f850)

Solutions

1. Remove the Include(s) from any GROUP BY query; fetch related documents in a separate query after grouping.
2. Perform the join/include in application code after obtaining the grouped results.
3. If you need per-row expansion, drop GROUP BY and aggregate in code.
4. Split into two queries: one grouped, one include-expanded over the grouped keys.

Example fix

// before — GROUP BY + INCLUDE
var results = col.Query()
    .Include("$.author")
    .GroupBy("$.category")
    .Select("{ cat: $.category, n: COUNT(*) }")
    .ToList(); // throws

// after — separate the concerns
var grouped = col.Query()
    .GroupBy("$.category")
    .Select("{ cat: $.category, n: COUNT(*) }")
    .ToList();
// fetch includes per-row in a different (non-grouped) query if needed

public List<BsonDocument> GroupedQuery(ILiteCollection<BsonDocument> col, string groupBy, string select)
{
    // Reject Include + GroupBy combinations before they reach the optimizer.
    // (No public flag to check includes count; enforce at the builder layer.)
    var query = col.Query();
    // Do NOT call .Include(...) here if you will .GroupBy(...).
    return query.GroupBy(groupBy).Select(select).ToList();
}

static bool IsCompatibleWithGroupBy(ILiteCollection<BsonDocument> col) =>
    true; // Enforced by NOT chaining Include before GroupBy.

try
{
    var r = col.Query().Include("$.rel").GroupBy("$.key").Select(s).ToList();
}
catch (NotSupportedException ex) when (ex.Message.Contains("GROUP BY expression do not support INCLUDE"))
{
    // Remove the Include and fetch related data in a separate query.
    throw new ArgumentException("GROUP BY cannot be combined with INCLUDE. Split into separate queries.", ex);
}

When it happens

Trigger: Calling ILiteCollection.Query().Include("$.related").GroupBy("$.key") or SQL like 'SELECT $.key, COUNT(*) FROM col GROUP BY $.key INCLUDE $.related'. Any query where _query.GroupBy != null and _query.Includes.Count > 0.

Common situations: Trying to eagerly load related documents in an aggregation query; building a generic query builder that always applies Include and also supports GroupBy; migrating an include-heavy query to grouped form.

Related errors

• 0
• 0
• GROUP BY already defined in this query
• HAVING already defined in this query
• Extend expression must return a document

AI-assisted analysis of litedb-org/LiteDB@f906a5f850 (2026-08-13). Data as JSON: /api/errors/06a38a6d369d7c27. Report an issue: GitHub.