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 :)