Code 47 UNKNOWN_IDENTIFIER: Identifier 's.id' cannot be resolved from subquery with name s ... Maybe you meant: ['s.id1']

[tomh4]

When querying with LINQ and adding nested navigation props with Include or ThenInclude e.g.:

 var parent = await ctx.Parents
            .Where(p => p.Id == 1L)
            .Include(p => p.Children)
                .ThenInclude(c => c.GrandChildren)
            .SingleOrDefaultAsync();

an error occurs:

ClickHouse.Driver.ClickHouseServerException (0x0000002F): Code: 47. DB::Exception: Identifier 's.id' cannot be resolved from subquery with name s. In scope SELECT c0.id, ..., s.id AS id0, .... Maybe you meant: ['s.id1']. (UNKNOWN_IDENTIFIER) (version 25.8.29.51 (official build))

[tomh4]

to be honest this is exceeding my knowledge on how the queries are generated from LINQ.

AI suggested to look into ClickHouseQuerySqlGenerator.cs
and add

    private int _selectDepth;

    protected override Expression VisitSelect(SelectExpression selectExpression)
    {
        _selectDepth++;
        try
        {
            return base.VisitSelect(selectExpression);
        }
        finally
        {
            _selectDepth--;
        }
    }

    protected override Expression VisitProjection(ProjectionExpression projectionExpression)
    {
        if (_selectDepth <= 1)
        {
            return base.VisitProjection(projectionExpression);
        }

        Visit(projectionExpression.Expression);

        if (projectionExpression.Alias != string.Empty)
        {
            Sql.Append(AliasSeparator)
               .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(projectionExpression.Alias));
        }

        return projectionExpression;
    }

but I don't have enough knowledge to verify if this is a good, or the best way so I would leave this here and hope for some input :)